SQLite DB-API syntax issues Python 3.5

Question

I have been searching through Stack Overflow as well as some other online research looking for the correct syntax to insert variables into my SQLite query. However, none of the 3 supported syntaxes have worked for my queries.

I found that the three supported syntaxes for the SQLite DB-API are:

qmark:
curs.execute("SELECT * FROM actors where actor.first_name = ?",("D'Angelo", ))

numeric:
curs.execute("SELECT * FROM actors where actor.first_name = :1", ("D'Angelo", ))

named:
curs.execute("SELECT * FROM actors where actor.first_name = :first_name",{'first_name': "D'Angelo"})

taken from http://www.philvarner.com/test/ng-python3-db-api/

However when I execute the following, I receive these errors:

named:

tableListQuery = "SELECT name FROM :dbFile WHERE type='table' ORDER BY Name", {'dbFile': dbFile}
userCursor.execute(tableListQuery)

Pycharm error: Expected type 'str', got 'Tuple[str, Dict[str, Any]]' 

Terminal error:
File "./dedupeDatabase.py", line 15, in Describe
userCursor.execute(tableListQuery)
ValueError: operation parameter must be str
-------------------------------------------------
qmark:

tableListQuery = "SELECT name FROM ? WHERE type='table' ORDER BY Name", (dbFile, )
userCursor.execute(tableListQuery)

Pycharm error: Expected type 'str', got 'Tuple[str, Tuple[Any]]' instead

Terminal error:
File "./dedupeDatabase.py", line 15, in Describe
userCursor.execute(tableListQuery)
ValueError: operation parameter must be str
-------------------------------------------------
numeric:

tableListQuery = "SELECT name FROM :1 WHERE type='table' ORDER BY Name", (dbFile, )
userCursor.execute(tableListQuery)

Pycharm error: Expected type 'str', got 'Tuple[str, Tuple[Any]]'

Terminal error:
File "./dedupeDatabase.py", line 15, in Describe
userCursor.execute(tableListQuery)
ValueError: operation parameter must be str

Please provide any insight you may have on this issue.

You cannot parameterise table names in your query. See [here](http://stackoverflow.com/questions/1274432/sqlite-parameters-not-allowing-tablename-as-parameter). You will have to sanitise this value yourself (if it comes from an outside source) and then use `.format()` to put it into your query string — roganjosh, Mar 07 '17 at 16:21
While this is true, it is not the source of the problem that OP is encountering; that is simply a Python syntax issue, where he is defining a tuple and trying to pass that to the `execute` method rather than passing the query string and parameters separately. — Daniel Roseman, Mar 07 '17 at 16:32
could you please provide some sample code of how I could 'sanitze' these values in python. I am not 100% sure what you mean — , Mar 07 '17 at 16:32
The aim is to try and avoid SQL injection, so using parameters ensures that the data is "escaped" so that it cannot execute a malicious command. See [Bobby Tables](https://xkcd.com/327/). That said, I have never taken something like a table name from an external source, so I'm not confident to advise you on this at all myself. That's just to give you some starting points unless someone can answer more definitively. — roganjosh, Mar 07 '17 at 16:38

score 2 · Accepted Answer · answered Mar 07 '17 at 16:39

So your fundamental issue is simply one of syntax. Defining tableListQuery as "my query string", params defines a tuple; you can't pass that tuple directly to execute, and the documentation does not say you can.[1]

You need to do as shown in the original snippet you quoted:

tableListQuery = "SELECT name FROM table WHERE type=:dbFile ORDER BY Name"
params = {'dbFile': dbFile}
userCursor.execute(tableListQuery, params)

Now the second issue, as pointed out in the comments, is that you can't use SQL parameters for things like field or table names; only for WHERE values. You would need to use normal Python string formatting for that.

[1] You could do it with the * operator, but that's probably confusing at this point.

SQLite DB-API syntax issues Python 3.5

1 Answers1