0

I am looking for a nice "pythonic" and "SQL-Injection-free" solution for a problem with reserved words in MySQL.

I have the following code:

alter_sql = 'ALTER TABLE %s ADD COLUMN %s TEXT'      
cursor.execute(alter_sql, sql_params)

The problem occurs when column name is something like 'index', 'int', 'limit' ....

In MySQL shell I can do: 

ALTER TABLE test ADD COLUMN test.limit;

or

ALTER TABLE test ADD COLUMN `limit`;

but not

ALTER TABLE test ADD COLUMN 'test.limit';

How can I achieve that with Python and MySQLdb?

kijasek
  • 33
  • 4

2 Answers2

2

Maybe this will work:

alter_sql = 'ALTER TABLE `%s` ADD COLUMN `%s` TEXT'

UPDATE: This seems not to work, because binding parameters this way will add single quotes, as MySQLdb supposes this is a string literal.

Alternatively, you can append the table name before the column name?

table_name = MySQLdb.escape_string(table_name)
escaped_column_name = MySQLdb.escape_string(column_name)
column_name = '`%s`.`%s`' % (table_name, escaped_column_name)

alter_sql = 'ALTER TABLE %s ADD COLUMN %s TEXT' % (table_name, column_name)

This way, you must escape them manually, to avoid binding them and adding single quotes around it.

jadkik94
  • 7,000
  • 2
  • 30
  • 39
  • How is the query you are using working then? it will output something like: `ALTER TABLE 'mytable' ADD COLUMN ...` which does not work... – jadkik94 May 16 '12 at 11:45
  • The resulting SQL would be ``ALTER TABLE '`test`' ADD COLUMN '`limit`' TEXT`` which is invalid. – kijasek May 16 '12 at 11:49
  • Your original query. Didn't it output something similar to what I put in my comment? I tried that and it did not work: "Check the syntax whatever ..." error – jadkik94 May 16 '12 at 11:52
0

From here:

You can issue the same statement by using %s placeholder markers and binding the appropriate values to them:

cursor.execute("""UPDATE animal SET name = %s
                  WHERE name = %s
               """, ("snake", "turtle"))
print "Number of rows updated: %d" % cursor.rowcount

Note the following points about the form of the preceding execute() call:

  • The %s placeholder marker should occur once for each value that is to be inserted into the statement string.
  • No quotes should be placed around the %s markers; MySQLdb supplies quotes for you as necessary.
  • Following the statement string argument to execute(), provide a tuple containing the values to be bound to the placeholders, in the order they should appear within the string. If you have only a single value x, specify it as (x,) to indicate a single-element tuple.
  • Bind the Python None value to a placeholder to insert an SQL NULL value into the statement.

So, based on that, you should just pre-process your arguments to see if they're in a list of reserved keywords, and if so, prefix the table name to the column name, for eg.

RESERVED_KEYWORDS = ['LIMIT', 'INT', 'INDEX']
table_name = 'TESTING'
column_name = 'LIMIT'
if column_name in RESERVED_KEYWORDS:
    column_name = '%s.%s' % (table_name, column_name)
sql_params = [table_name, column_name]
alter_sql = 'ALTER TABLE %s ADD COLUMN %s TEXT'      
cursor.execute(alter_sql, sql_params)
Christian Witts
  • 11,375
  • 1
  • 33
  • 46
  • I tried that, but MySQLdb created the following SQL: ALTER TABLE testing ADD COLUMN 'test.limit'; Which is invalid due to the quotes around test.limit. – kijasek May 16 '12 at 12:23
  • If you are 100% sure your input is sanitized you could just do `sql_query = alter_sql % sql_params; cursor.execute(sql_query)` to interpolate the SQL statement and parameters first, and then execute it. Not exactly the best method, but should work. – Christian Witts May 16 '12 at 12:26