Named parameters without quotation marks? Sqlite / python?

Question

When using ready-made statements with named parameters in SQLite (in particular, with the python sqlite3 module http://docs.python.org/library/sqlite3.html ), can you include string values ​​in any case without getting quotes around them?

I have it:

columnName = '''C1''' cur = cur.execute('''SELECT DISTINCT(:colName) FROM T1''', {'colName': columnName}) 

And it looks like SQL, in the end I get the following:

 SELECT DISTINCT('C1') FROM T1 

which, of course, is not very useful, I really want:

 SELECT DISTINCT(C1) FROM T1 . 

Is there a way to get the execute method to interpret the provided arguments so that they don't exchange quotes around them?

I wrote a small test program to fully explore it, as it stands here:

 import sys import sqlite3 def getDatabaseConnection(): DEFAULTDBPATH = ':memory:' conn = sqlite3.connect(DEFAULTDBPATH, detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES) conn.text_factory = str return conn def initializeDBTables(conn): conn.execute(''' CREATE TABLE T1( id INTEGER PRIMARY KEY AUTOINCREMENT, C1 STRING);''') cur = conn.cursor() cur.row_factory = sqlite3.Row # fields by name for v in ['A','A','A','B','B','C']: cur.execute('''INSERT INTO T1 values (NULL, ?)''', v) columnName = '''C1''' cur = cur.execute('''SELECT DISTINCT(:colName) FROM T1''', {'colName': columnName}) #Should end up with three output rows, in #fact we end up with one for row in cur: print row def main(): conn = getDatabaseConnection() initializeDBTables(conn) if __name__ == '__main__': main() 

It would be interesting to hear about any case of manipulation of the execute method so that this can work.

Answer 1

In SELECT DISTINCT(C1) FROM T1 C1 not a string value, it is a piece of SQL code. Parameters (escaped in execute ) are used to insert values, not code snippets.

Answer 2

You use bindings, and bindings can only be used for values, not table or column names. You will need to use row interpolation / formatting to get the effect you want, but it will leave you open for SQL injection if the column name was obtained from an untrusted source. In this case, you can sanitize the string (for example, allow an alphanumeric letter) and use the authorizer interface to verify that unexpected activity does not occur.