python - python twitter Streamer 中的引号问题

Question

user = "'" + "@%s" % data['user']['screen_name'] + "'"
coordinates = "'" + ",".join(str(e)for e in data['coordinates']['coordinates']) + "'"
tweet = "'" + data['text'].encode("ascii", "ignore") + "'"
query = "INSERT INTO tweets (location, tweet, author) values (" + coordinates + "," + tweet + "," + user + ")"

我遇到了传递带有撇号 (') 或随机引号的推文的问题，这会破坏插入查询。任何建议将不胜感激，因为我想减少插入推文时的错误。谢谢

错误消息示例:

(1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's Stadium https://t.co/uJ2U2tcXlr','@Pucker21')' at line 1")

Answer 1

这是为什么您不应该通过字符串插值手动构造查询的原因之一。

相反，让数据库驱动程序处理它:

query = """
    INSERT INTO
        tweets 
        (location, tweet, author) 
    VALUES 
        (%s, %s, %s)
"""
cursor.execute(query, (coordinates, tweet, user))

这里我们创建一个参数化查询，%s是数据库驱动程序要填充的占位符。 MySQL 驱动程序将处理正确的引用、转义并使查询免受 SQL 注入(inject)。