Python，SQLite:“%s”中的撇号

Question

尝试执行SQLite3查询时遇到语法错误。问题是因为变量inputPattern包含的

其功能是：

def searchForExactEnglishToCzechTranslation(inputPattern,db):
    if "\'" in inputPattern:
        inputPattern.replace("'","''")
    result = db.execute("SELECT czech FROM translations WHERE english='%s'"  % (inputPattern)).fetchall()

我想换个牌子，但还是不行。在

你能给我个建议吗？谢谢

Answer 1

不要使用插值；使用SQL参数：

def searchForExactEnglishToCzechTranslation(inputPattern,db):
    result = db.execute("SELECT czech FROM translations WHERE english=?",
                        (inputPattern,)).fetchall()

这将处理引号（引号中的?不包含引号）和转义。在

引用^{} module documentation：

Usually your SQL operations will need to use values from Python variables. You shouldn’t assemble your query using Python’s string operations because doing so is insecure; it makes your program vulnerable to an SQL injection attack (see http://xkcd.com/327/ for humorous example of what can go wrong).

Instead, use the DB-API’s parameter substitution. Put ? as a placeholder wherever you want to use a value, and then provide a tuple of values as the second argument to the cursor’s execute() method. (Other database modules may use a different placeholder, such as %s or :1.)