Using a variable as field name in MySQLdb, Python 2.7

Question

This works when I replace the column variable with an actual column name. I do however need a variable. When I use a variable I get a MySQL syntax error. Can a field be a variable? If so, where is the error?

conn = self.create_connection()
        cur = conn[0]
        db = conn[1]

        cur.execute('''
                        UPDATE coefficients

                        SET %s = %s 
                        WHERE coef_id = %s

                    ''' , (sql_col_name, fgi, ici))  
        db.commit()

Ok here's the traceback:

           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 ''base_rpm' = 3500 WHERE coef_id = 460' at line 1")

I haven't used MySQLdb, but I have used psycopg2 with a Redshift database and to use variables in the sql statement there you have to define a sql string and pass the sql string to the execute command. Ex: `sql = "Update coefficients SET %s = %s WHERE coef_id = %s"` `cur.execute(sql)` If it wasn't defined this way the literal `%s` was passed to the query. — Bob, Aug 30 '16 at 17:55
I won't post as an answer as I feel uncomfortable doing it myself in SQLite (I feel this is hacky and perhaps I'll gain too if someone tells me I'm wrong...) but you can form the query separately e.g. `query = "UPDATE something SET {} = %s".format('hello')` and then `print query % 'goodbye'`. In which case, the use of `format` doesn't affect `%s` in your pre-formed string, so you create it in two parts. — roganjosh, Aug 30 '16 at 17:55
I can't edit my previous comment, but of course to get the variables in the string you will want something like this in your sql statement. `sql = "Update coefficients SET {1} = {2} WHERE coef_id = {3}".format(sql_col_name, fgi, ici)` Or whatever formatting practices you prefer, just as long as you add them into the string. — Bob, Aug 30 '16 at 18:06
@BobDunakey but is this standard practice? I always form the string before execution in SQLite but this was only due to severe time pressure and it shut up the error that you can't assign things left of `=` on execution. I've never been settled with it, surely there is a standard feature for this? — roganjosh, Aug 30 '16 at 18:08
@BobDunakey and actually your answer is probably going further into deeper water since you're using `format` to assign values (on RHS of `=`) and bypassing the placeholders that reduce the chance of SQL injection? — roganjosh, Aug 30 '16 at 18:13
Can you please add the traceback of the error and highlight the position where it occurs? — Robin Krahl, Aug 30 '16 at 18:20
Here is the traceback: 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 ''base_rpm' = 3500 \n WHERE coef_id = 460' at line 2") — Mike C., Aug 30 '16 at 19:39
`coef_id = 460'` would be invalid because you have a stray apostrophe after `460` but not before. EDIT: and before `base_rpm`. You should edit the question to include the error verbatim. — roganjosh, Aug 30 '16 at 19:41
I happen to be looking at this question so I see your response; you should use '@' before a name to notify them that you replied or you might get nothing. The error is still saying `base_rpm'` with the apostrophe _after_ so that would be invalid? How is it getting there? — roganjosh, Aug 30 '16 at 19:46
@roganjosh I couldn't say for sure, I'm not a web developer so sql injection wasn't a concern for me during development. However it appears in looking into this you may want to seperate the arguments from the sql in web dev. http://stackoverflow.com/questions/2097475/how-to-safely-generate-a-sql-like-statement-using-python-db-api @MikeC.It looks like your sql statement contains a newline character you can achieve this by adding `.replace('\n', '')` at the end of your string. — Bob, Aug 30 '16 at 19:48
@roganjosh The traceback just didn't copy and paste well. The double quotation mark you see before base_rpm is actually two single quotation marks. base_rpm is enclosed in singles and the entire line ending with 460 is also enclosed in singles. It's clear in the python interpreter but didn't copy well. — Mike C., Aug 30 '16 at 19:52
@BobDunakey Thanks for the link but it doesn't address my concern. I'm thrust into the world of the web :) I cannot see how my approach could introduce a serious error but it makes me feel uncomfortable; yours however I think removes the 'escapes' that would prevent SQL injection. Nothing on the RHS of '=' should allow you to `format` in a string. — roganjosh, Aug 30 '16 at 19:53

score 5 · Accepted Answer · answered Aug 30 '16 at 20:13

The issue there is that Parameter substitution in the execute method is intended to be used for data only - as you found out. That is not quite explicit in the documentation, but it is how most database drivers implement it.

It is important to note the intent of the parameter substitution in the execute and executemany methods is to format Python objects as strings to the database, and apply escaping and quotes, so that SQL injection becomes difficult (if not impossible) without one having to worry about several places where to put the escaping.

What is needed when you need to use variable column names (or vary other parts of the SQL statement) is to format the string using Python's string formatting methods - and leave the resulting string in a suitable way to be used as a suitable parameter for the data substitution, in a second substitution (so that type casting and quoting still is performed by the driver).

To avoid having to escape the %s entries for parameters itself, you could use a different string interpolation method than the % operator, for example, the newer format string method:

  cur.execute('''
                    UPDATE coefficients
                    SET {} = %s 
                    WHERE coef_id = %s

                '''.format(sql_col_name) ,
                (fgi, ici))

This example shows the simplest way that would make your example work - just write it in code so that it is easily readable and maintanable - for example, using an extra variable for the statement, and calling format prior to the line calling execute. But that is just style.

Upvote, thanks for confirming my approach in the comments. That has been a source of discomfort for quite a while but, as you say, it's not explicit in documentation :) — roganjosh, Aug 30 '16 at 20:16

Using a variable as field name in MySQLdb, Python 2.7

1 Answers1