将sqlite prepared语句与SELECT一起使用

Question

我正在做一个小的宠物项目，它组织人们的漫画书，并将他们的元数据存储在一个SQL数据库中。为了正确起见，我使用的是准备好的语句，而不是Python内置的字符串操作符，但是我不能让它正常工作。这是我为说明我遇到的问题而编写的简短代码片段：

#!/usr/bin/env python

import sqlite3

connection = sqlite3.connect("MyComicBox.db")

curs = connection.cursor()

curs.execute("CREATE TABLE IF NOT EXISTS comic_collection (id INTEGER PRIMARY KEY, series TEXT, publisher TEXT, issue TEXT, tags TEXT)")

connection.commit()

def addComic(series = "", publisher = "", issue = "", tags = ""):
    curs.execute("INSERT INTO comic_collection (series, publisher, issue, tags) VALUES(?, ?, ?, ?)", (series, publisher, issue, tags))
    connection.commit()

def search(attribute, val):
    """
    Do an SQL query for all comics where attribute LIKE val and return a list of them.
    """

#   cmd = "SELECT * from comic_collection WHERE %s LIKE '%s'" % (attribute, val)
#   curs.execute(cmd)

    cmd = "SELECT * from comic_collection WHERE ? LIKE ?"
    curs.execute(cmd, (attribute, val))

    results = []

    for comic in curs:
        results.append(comic)

    return results

addComic(series = "Invincible Iron Man", issue = "500", publisher = "Marvel Comics", tags = "Iron Man; Spider-Man; Mandarin")


searchResults = search("issue", "500")

for item in searchResults:
    print item

我的问题在于搜索功能。查询不会返回任何内容，除非我使用替换执行cmd的两行？运算符，其中有两行（注释掉），我使用Python内置的字符串运算符执行cmd。有谁能帮我弄清楚我做错了什么吗？

Answer 1

不能将占位符用于列名-它们仅用于值。所以这是可行的：

cmd = "SELECT * from comic_collection WHERE %s LIKE ?" % attribute
curs.execute(cmd, (val,))

Answer 2

你使用attribute是错误的。假设?已经包含它的投降''s

一种方法是这样做：

cmd = "SELECT * from comic_collection WHERE %s LIKE ?" % attribute
curs.execute(cmd, val)

但您必须确保属性不来自这里的不受信任的源，或者它只有有效的值，可能是

assert attribute in ('series', 'publisher', 'issue', 'tags')

Answer 3

根据我对sqlite C api中准备好的语句的回忆，您可能必须这样编写它：

cmd = "SELECT * from comic_collection WHERE ?1 LIKE ?2"