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?
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.