Python MySQL Statement returning Error

Question

hey, I'm very new to all this so please excuse stupidity :)

import os
import MySQLdb
import time
db = MySQLdb.connect(host="localhost", user="root", passwd="********", db="workspace")
cursor = db.cursor()
tailoutputfile = os.popen('tail -f syslog.log')
while 1:
 x = tailoutputfile.readline() 
 if len(x)==0:
 break
 y = x.split()
 if y[2] == 'BAD':
 timestring = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(time.time()))
 cursor.execute("INSERT INTO releases (date, cat, name) values (timestring, y[4], y[7]")
 if y[2] == 'GOOD':
 print y[4] + '\t' + y[7]

so i run the program and this is the error message I am getting

user@machine:~/$ python reader.py
Traceback (most recent call last):
 File "reader.py", line 17, in ?
 cursor.execute("INSERT INTO releases (date, cat, name) values (timestring, y[4], y[7]")
 File "/usr/lib/python2.4/site-packages/MySQLdb/cursors.py", line 163, in execute
 self.errorhandler(self, exc, value)
 File "/usr/lib/python2.4/site-packages/MySQLdb/connections.py", line 35, in defaulterrorhandler
 raise errorclass, errorvalue
_mysql_exceptions.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[4], y[7]' at line 1")
user@machine:~/$

So i'm assuming that the error is obviously coming from the SQL Statement

cursor.execute("INSERT INTO releases (date, cat, name) values (timestring, y[4], y[7]")

Here is an example of what y[4] and y[7] will look like.

YES Mail.Sent.To.User:[email protected]:23.17

Is this error happening because I should be escaping those values before I try and Insert them into the Database? Or am I completely missing the point??

Any help would be appreciated! thanks in advance.

Answer 1

As pointed out, you're failing to copy the Python variable values into the query, only their names, which mean nothing to MySQL.

However the direct string concatenation option:

cursor.execute("INSERT INTO releases (date, cat, name) VALUES ('%s', '%s', '%s')" % (timestring, y[4], y[7]))

is dangerous and should never be used. If those strings have out-of-bounds characters like ' or \ in, you've got an SQL injection leading to possible security compromise. Maybe in your particular app that can never happen, but it's still a very bad practice, which beginners' SQL tutorials really need to stop using.

The solution using MySQLdb is to let the DBAPI layer take care of inserting and escaping parameter values into SQL for you, instead of trying to % it yourself:

cursor.execute('INSERT INTO releases (date, cat, name) VALUES (%s, %s, %s)', (timestring, y[4], y[7]))

Answer 2

 cursor.execute("INSERT INTO releases (date, cat, name) values (timestring, y[4], y[7]")

should be

 cursor.execute("INSERT INTO releases (date, cat, name) values (timestring, '%s', '%s')" % (y[4], y[7]))

Your best bet to debug things like this is to put the query into a variable and use that:

query = "INSERT INTO releases (date, cat, name) values (timestring, '%s', '%s')" % (y[4], y[7])
print query
cursor.execute(query)

That print statement would make it very obvious what the problem is.

If you're going to be using list variables a lot like this it can get very confusing, consider using the list just once and putting the variables into a dictionary. It's a bit longer to type, but is much, much easier to keep track of what's going on.

• Simon: you might also need to put quotes around the strings in [y4] and [y7]

Answer 3

never use "direct string concatenation" with SQL, because it's not secure, more correct variant:

cursor.execute('INSERT INTO releases (date, cat, name) VALUES (%s, %s, %s)', (timestring, y[4], y[7]))

it automatically escaping forbidden symbols in values (such as ", ' etc)