开发者

sqlite / python - named parameters without enclosing quotes?

When using prepared statements with named parameters in SQLite (specifically with the python sqlite3 module http://docs.python.org/library/sqlite3.html ) is there anyway to include string values without getting quotes put around them ?

I've got this :

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

And it seems the SQL I end up with is this :

SELECT DISTINCT('C1') FROM T1

which isn't much use of course, what I really want is :

SELECT DISTINCT(C1) FROM T1 .

Is there any way to prompt the execute method to interpret the supplied arguments in such a way that it doesn't wrap quotes around them ?

I've written a little test program to explore this fully so for what it's worth here it is :

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 = getDat开发者_如何学GoabaseConnection()
    initializeDBTables(conn)

if __name__ == '__main__':
    main()

Would be interested to hear of anyway of manipulating the execute method to allow this to work.


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


You are using bindings and bindings can only be used for values, not for table or column names. You will have to use string interpolation/formstting to get the effect you want but it does leave you open to SQL injection attacks if the column name came from an untrusted source. In that case you can sanitize the string (eg only allow alphanumerics) and use the authorizer interface to check no unexpected activity will happen.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜