CODEWITHSUNDEEP
pythonmysqlsqlpython-2.7
munchschair
munchschair

Reputation: 1683

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

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)

Upvotes: 1

Views: 682

Answers (1)

alecxe
alecxe

Reputation: 474221

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.

Upvotes: 1

Related Questions