Sqlite无法识别的令牌:“'''" [英] Sqlite unrecognized token: "'''"

问题描述

我在 python 中使用 sqlite3 时遇到问题.

I met problems while using sqlite3 in python.

def getEntryId(self, table, field, value, createNew=True):
    cur=self.con.execute("select rowid from %s where %s = '%s'" % (table, field, value))
    res=cur.fetchone()
    if res==None:
        cur=self.con.execute("insert into %s (%s) values('%s') " % (table, field, value))
        return cur.lastrowid
    else:
        return res[0]

然而，我遇到了这个:操作错误:无法识别的标记:'''".看来我的第二行代码不正确.我不知道为什么，所以我做同样的事情:

However, I met this: OperationalError: unrecognized token: "'''". It seems that my 2nd line of codes is incorrect. I can not figure out why, so I do the same thing:

cu.execute("select rowid from urllist where %s = '%s'" % ('url', 'yes'))

它出来没有错误.为什么?我该如何解决?

It came out without an error. Why? How could I fix it?

推荐答案

您应该参数化查询.您虽然不能参数化表和字段名称，但可以使用字符串格式将表和字段名称插入查询中，但请确保您信任来源或正确验证值:

You should parameterize the query. You cannot though parameterize the table and field names, you can use string formatting to insert the table and field names into the query, but make sure you either trust the source, or validate the values properly:

query = "select rowid from {table} where {field} = %s".format(table=table, field=field)
cur = self.con.execute(query, (value, ))
res = cur.fetchone()

参数化不仅有助于防止SQL注入攻击，还有助于处理数据类型转换, 正确转义参数，这也可以解决您当前的问题.

The parameterization not only helps to prevent SQL injection attacks, but also handles the data types conversions, escapes the parameters properly, which may fix your current problem as well.

这篇关于Sqlite无法识别的令牌:“'''"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！