django + south + python: strange behavior when using a text string received as a parameter in a function

Question

Hello, this is my first question.

I'm trying to execute a SQL query in django (south migration):

from django.db import connection
# ...
class Migration(SchemaMigration):
    # ...
    def transform_id_to_pk(self, table):
        try:
            db.delete_primary_key(table)
        except:
            pass
        finally:
            cursor = connection.cursor()

            # This does not work
            cursor.execute('SELECT MAX("id") FROM "%s"', [table])

            # I don't know if this works.
            try:
                minvalue = cursor.fetchone()[0]
            except:
                minvalue = 1
            seq_name = table + '_id_seq'

            db.execute('CREATE SEQUENCE "%s" START WITH %s OWNED BY "%s"."id"', [seq_name, minvalue, table])
            db.execute('ALTER TABLE "%s" ALTER COLUMN id SET DEFAULT nextval("%s")', [table, seq_name + '::regclass'])
            db.create_primary_key(table, ['id'])
    # ...

I use this function like this:

self.transform_id_to_pk('my_table_name')

So it should:

1. Find the biggest existent ID or 0 (it crashes)
2. Create a sequence name
3. Create the sequence
4. Update the ID field to use sequence
5. Update the ID as PK

But it crashes and the error says:

  File "../apps/accounting/migrations/0003_setup_tables.py", line 45, in forwards
    self.delegation_table_setup(orm)
  File "../apps/accounting/migrations/0003_setup_tables.py", line 478, in delegation_table_setup
    self.transform_id_to_pk('accounting_delegation')
  File "../apps/accounting/migrations/0003_setup_tables.py", line 20, in transform_id_to_pk
    cursor.execute(u'SELECT MAX("id") FROM "%s"', [table.encode('utf-8')])
  File "/Library/Python/2.6/site-packages/django/db/backends/util.py", line 19, in execute
    return self.cursor.execute(sql, params)
psycopg2.ProgrammingError: relation "E'accounting_delegation'" does not exist
LINE 1: SELECT MAX("id") FROM "E'accounting_delegation'"
                              ^

I have shortened the file paths for convenience.

What does that "E'accounting_delegation'" mean? How could I get rid of it?

Thank you!

Carlos.

Answer 1

The problem is that you're using DB-API parameterization for things that are not SQL data. When you do something like:

cursor.execute('INSERT INTO table_foo VALUES (%s, %s)', (col1, col2))

the DB-API module (django's frontend for whatever database you are using, in this case) will know to escape the contents of 'col1' and 'col2' appropriately, and replace the %s's with them. Note that there are no quotes around the %s's. But that only works for SQL data, not for SQL metadata, such as table names and sequence names, because they need to be quoted differently (or not at all.) When you do

cursor.execute('INSERT INTO "%s" VALUES (%s, %s)', (tablename, col1, col2))

the tablename gets quoted as if you mean it to be string data to insert, and you end up with, for example, "'table_foo'". You need to separate your SQL metadata, which is part of the query, and your SQL data, which is not, like so:

sql = 'INSERT INTO TABLE "%s" VALUES (%%s, %%s)' % (tablename,)
cursor.execute(sql, (col1, col2))

Note that because the django DB-API frontend's paramstyle is 'pyformat' (it uses %s for placeholders) you need to escape those when you do the string formatting to create the SQL you want to execute. And note that this isn't secure against SQL injection attacks when you take the tablename from an insecure source and don't validate it.