Opened 5 years ago

Closed 5 years ago

Last modified 5 years ago

#16407 closed Bug (invalid)

Unicode not working for direct SQL INSERT

Reported by: mashedmeat Owned by: nobody
Component: Database layer (models, ORM) Version: 1.3
Severity: Normal Keywords:
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

I've only tried this bug using "INSERT INTO" and "INSERT OR REPLACE INTO". The problem is that I can't use params. Here's what I was trying:

word = "pickle"
translation = googleTranslate('english', 'chinese', word) #from, to, word-to-be-translated
cursor.execute("INSERT INTO %s VALUES (%s, %s)", [table, word, translation]) # table won't insert here...

In order to bypass this, I had to insert it using a string, which was explicitly stated to be a security problem in the documentation:

insert_sql = "INSERT INTO %s VALUES" % (table_name)
insert_sql = insert_sql + " (%s, %s)"
cursor.execute(insert_sql, [title, translation])

(the code is a little different, because it's my actual code. I wrote the last code bit to try to better illustrate what's going on.)

Best,
Loren

Change History (3)

comment:1 Changed 5 years ago by Bernhard Essl

Needs documentation: unset
Needs tests: unset
Patch needs improvement: unset

The tablename gets escaped with single quotes, which isn't a correct SQL Syntax.

cursor.execute("INSERT INTO %s VALUES (NULL, %s, %s)", ["django_site", "foo", "bar"])

#INSERT INTO 'django_site' VALUES (NULL, 'foo', 'bar')

I'm not sure if this is really a bug.

comment:2 Changed 5 years ago by Aymeric Augustin

Resolution: invalid
Status: newclosed

This isn't specific to Django; it's a direct consequence of the DB-API (PEP 249, if memory serves).

The database adapter has no way of knowing which parameters should be escaped as table names and which parameters should be escaped as "regular parameters" — no magic here.

You must use string interpolation to insert the table name in the SQL query, and parameter substitution for the parameters. I hope your table names are not derived from user input :) You may validate them against a whitelist or a simple regexp if they're really variable.

Last edited 5 years ago by Aymeric Augustin (previous) (diff)

comment:3 Changed 5 years ago by Luke Plant

A quick note - you should use connection.ops.quote_name to quote the table name before doing string interpolation. This is not guaranteed to protect against malicious input, but can help with spaces and some other funny characters.

Note: See TracTickets for help on using tickets.
Back to Top