views:

31

answers:

2

Hey all, I'm getting an error

OperationalError: (1054, "Unknown column 'XX' in 'where clause'")

Where XX is the value of CLASS in the following code

conn = MySQLdb.connect(host = "localhost",user = "user", passwd = "pass",db = "dbase")
cursor = conn.cursor()
cursor.execute("""SELECT * FROM %s WHERE course =%s AND sec = %s""" % (str(DEPT),str(CLASS),str(SEC),))

The thing is, I only get this error with certain values, namely, when CLASS contains a letter. I have the table set up as varchar if that helps

Thanks!

+1  A: 

Don't use "string injection" into your SQL except for cases where it's truly indispensable, such as the str(DEPT) here to choose what table you're selecting from. For every other case, use the parameter passing feature of the Python DB API instead -- it will quote things properly for you and automatically defend you against "SQL injection" attacks, among other things. (It can also be faster, sometimes).

Since MySQLdb uses the unfortunate notation %s for parameters, here's what you should do (also fixing the style to be PEP8-compliant, not required but can't hurt;-):

conn = MySQLdb.connect(host="localhost", user="user", passwd="pass", db="dbase")
cursor = conn.cursor()
q = 'SELECT * FROM %s WHERE course=%%s AND sec = %%s""" % (DEPT,)
cursor.execute(q, (CLASS, SEC))

The %%s in the string formatting which produces q become a single % each upon formatting, so q is left with two occurrences of %s -- which the execute fills in neatly with correctly formatted versions of CLASS and SEC. All the str calls are redundant, etc.

As an aside, if you're on Python 2.6 or later, for string formatting you should use the new format method instead of the old % operator -- that saves you from the need for those "doubled up % signs", among other advantages. I haven't applied that change in the above snippet just in case you're stuck with 2.5 or earlier (so the code above works in any version of Python, instead of just in reasonably recent ones).

Alex Martelli
Sorry, but I'm really new to SQL and Python. Could you explain what parameter passing is if it not too big a deal?Thanks!
Jill S
Thanks Alex! Appreciate it
Jill S
@Jill, sure, see the edit I just did to my answer -- I simply rewrote your code to make it correct (and PEP8-compliant as to where the spaces go or don't for elegant style, and more speedy and readable by avoiding a bunch of redundant `str` calls, but those are only side issues here;-).
Alex Martelli
@Jill, you're welcome!
Alex Martelli
A: 

Instead of:

course=%s

I think you need:

course='%s'
Shane
Alex's answer is the better approach.
Shane
Nope, I get an error about correct syntax when I do that :/
Jill S