Why won't this insert into my database, using python?

Question

I am trying to insert some strings into my database.

import mysql.connector
import web

from mysql.connector import Error
import cgi, cgitb

conn = mysql.connector.connect(host='localhost', database='adatabase', user='root', password='')
cursor = conn.cursor()

if conn.is_connected():
    print('Connected to MySQL database')



fullname = "fullname"
email ="email"
comments = "comments"
sql = """INSERT INTO `comments` (`id`, `name`, `email`, `comments`, `time`) VALUES (NULL, %s, %s, %s, )""" % (fullname, email, comments)

cursor.execute(sql)
conn.commit()  
cursor.close()
conn.close()

It gives me this error. Note, I have mysql-connector-python_1.2.3 installed when it gave me this error.

Connected to MySQL database
Traceback (most recent call last):
  File "commentsscript.cgi", line 108, in <module>
    cursor.execute(sql)
  File "/usr/lib/python2.7/dist-packages/mysql/connector/cursor.py", line 494, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "/usr/lib/python2.7/dist-packages/mysql/connector/connection.py", line 683, in cmd_query
    statement))
  File "/usr/lib/python2.7/dist-packages/mysql/connector/connection.py", line 601, in _handle_result
    raise errors.get_exception(packet)
mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1

and I've also gotten this error, when I didn't have mysql-connector-python installed and instead, mysqlworkbench

Traceback (most recent call last):
  File "commentsscript.cgi", line 108, in <module>
    cursor.execute(sql)
  File "/usr/lib/python2.7/dist-packages/mysql/connector/cursor.py", line 494, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "/usr/lib/python2.7/dist-packages/mysql/connector/connection.py", line 683, in cmd_query
    statement))
  File "/usr/lib/python2.7/dist-packages/mysql/connector/connection.py", line 601, in _handle_result
    raise errors.get_exception(packet)
mysql.connector.errors.ProgrammingError: 1054 (42S22): Unknown column 'fullname' in 'field list'

Thing's I've tried

Passing variables into a create table statement. This works.

anooog1 = "thing"
sql = """CREATE TABLE thiga (%s INT, COL2 INT )""" % (anooog1)

Also passing the strings directly. This is redundant kind of, considering that I want this to eventually pass variables from HTML. I've been testing the syntax.

sql = """INSERT INTO `comments` (`id`, `name`, `email`, `comments`, `time`) VALUES (NULL, "fullname", "email", "comments", CURRENT_TIMESTAMP)"""

(I am also having trouble getting cgi scripts to run in apache, but that's a different problem,this testing is being done on the terminal)

alecxe · Accepted Answer · 2015-12-18 14:58:48Z

1

Don't use string formatting to insert your variables into SQL queries - this is, for the least, makes your code vulnerable to SQL injections.

You are also missing the value for the time column in your query and I'm pretty sure you had to put your placeholders into quotes (%s -> "%s").

Anyway, parameterize your query:

sql = """
    INSERT INTO 
        comments 
        (id, name, email, comments, time) 
    VALUES 
        (NULL, %s, %s, %s, CURRENT_TIMESTAMP)
"""
cursor.execute(sql, (fullname, email, comments))

Note that quotes around placeholders are not needed here - the database driver in this case handles the type conversions automatically.

edited Dec 18, 2015 at 14:58

answered Dec 18, 2015 at 14:51

alecxe

476k127 gold badges1.1k silver badges1.2k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

munchschair Over a year ago

I had current timestamp in there like a few minutes ago and was still getting the same errors. It works tho with this new syntax.

Collectives™ on Stack Overflow

Why won't this insert into my database, using python?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related