This issue tracker has been migrated to GitHub, and is currently read-only.
For more information, see the GitHub FAQs in the Python's Developer Guide.

Title: possible SQL injection into db APIs via table names... sqlite3
Type: security Stage: resolved
Components: Versions:
Status: closed Resolution: wont fix
Dependencies: Superseder:
Assigned To: Nosy List: eric.araujo, illume, loewis, petri.lehtinen
Priority: normal Keywords:

Created on 2011-03-26 16:18 by illume, last changed 2022-04-11 14:57 by admin. This issue is now closed.

Messages (11)
msg132247 - (view) Author: Rene Dudfield (illume) Date: 2011-03-26 16:18

you can possibly do an SQL injection via table names (and maybe some other parts of queries).  Tested with sqlite3, but maybe it affects others too.

You can not do parameter substitution for table names, so people use normal python string formatting instead.

If the table name comes from an untrusted source, then possibly an SQL injection could happen.

msg132249 - (view) Author: Martin v. Löwis (loewis) * (Python committer) Date: 2011-03-26 17:02
Why do you think this is a bug in Python?
msg132251 - (view) Author: Rene Dudfield (illume) Date: 2011-03-26 17:06

because the sqlite3 package comes with python.
msg132255 - (view) Author: Martin v. Löwis (loewis) * (Python committer) Date: 2011-03-26 17:16
But putting untrusted strings into the table name is a bug in the application, not in Python.
msg132256 - (view) Author: Rene Dudfield (illume) Date: 2011-03-26 17:20
The bug in python is that you can not use parameter substitution to put the table names into the queries.  So people are forced to use string substitution instead.
msg132257 - (view) Author: Martin v. Löwis (loewis) * (Python committer) Date: 2011-03-26 17:41
Ah. That's not a limitation of Python, but a limitation of sqlite. See

for how parameter binding works. The table name is not supported as a parameter; neither are column names or database names.

So if you want this feature added, please request it from the sqlite developers; Python will then naturally inherit it. I'm skeptical that they are open to such a proposal, though, since it will be a massive change in SQL parsing.
msg132293 - (view) Author: Rene Dudfield (illume) Date: 2011-03-27 08:19

aaah, ok.

It seems to require the use of a quote function.  See  

However python does not seem to expose the function?  I don't see how you can write safe queries using python without it.
msg132374 - (view) Author: Éric Araujo (eric.araujo) * (Python committer) Date: 2011-03-27 22:21
Aren’t you supposed to use the DB API to get safe queries?
msg132426 - (view) Author: Martin v. Löwis (loewis) * (Python committer) Date: 2011-03-28 21:42
> Aren’t you supposed to use the DB API to get safe queries?

Yes, but the OP complains that the DB API doesn't support specification
of the table name from a parameter. So the DB API won't help here.
msg161896 - (view) Author: Petri Lehtinen (petri.lehtinen) * (Python committer) Date: 2012-05-29 16:22
No SQL library that I know of provides a way to escape table names. The quoting functions are always meant to escape string parameters. This is true for sqlite3_mprintf(), too (the %q and %Q options).

If you build table names from user input, your database design is somehow flawed.
msg161903 - (view) Author: Rene Dudfield (illume) Date: 2012-05-29 17:01

Here is an article with people trying to find a solution:

"The psycopg2 documentation explicitly recommends using normal python % or {} formatting to substitute in table and column names."

Sqlalchemy uses a format_table method with their sql compiler to quote table names for sqlite.

It's probably just sane to either use SQLalchemy, use ctypes to get at the sqlite mprintf function, or perhaps look at the above stackoverflow article for more solutions.

There is python code out there vulnerable to attack, that doesn't quote table names correctly.  Including at least one major python framework.  Hopefully people who care will follow some of the above links.

Date User Action Args
2022-04-11 14:57:15adminsetgithub: 55894
2012-05-29 17:01:17illumesetmessages: + msg161903
2012-05-29 16:22:00petri.lehtinensetstatus: open -> closed
resolution: wont fix
messages: + msg161896

stage: resolved
2012-05-22 19:13:49petri.lehtinensetnosy: + petri.lehtinen
2011-03-28 21:42:19loewissetmessages: + msg132426
2011-03-27 22:21:41eric.araujosetnosy: + eric.araujo
messages: + msg132374
2011-03-27 08:19:55illumesetmessages: + msg132293
2011-03-26 17:41:43loewissetmessages: + msg132257
2011-03-26 17:20:29illumesetmessages: + msg132256
2011-03-26 17:16:53loewissetmessages: + msg132255
2011-03-26 17:06:23illumesetmessages: + msg132251
2011-03-26 17:02:22loewissetnosy: + loewis
messages: + msg132249
2011-03-26 16:18:38illumecreate