如何使用Python将带引号和换行符的字符串插入sqlite数据库？

Question

我正在尝试将从文件中读取的字符串插入到Python中的sqlite数据库中。这些字符串具有空格（换行符、制表符和空格），并且还包含单引号或双引号。下面是我的尝试：

import sqlite3
conn = sqlite3.connect('example.db')
c = conn.cursor()

# Create table
c.execute('''CREATE TABLE test
             (a text, b text)''')

f = open("foo", "w")
f.write("hello\n\'world\'\n")
f.close()

testfield = open("foo").read()

# Insert a row of data
c.execute("INSERT INTO test VALUES ('%s', 'bar')" %(testfield))

# Save (commit) the changes
conn.commit()

我发现这会出现错误：

    c.execute("INSERT INTO test VALUES ('%s', 'bar')" %(testfield))
sqlite3.OperationalError: near "world": syntax error

我该如何实现这个？在插入数据库之前，这些字符串需要进行转义吗？如果需要，应该怎么做？谢谢。

- user248237

Answer 1

您应该使用SQL参数而不是字符串格式化：

使用SQL参数可防止SQL注入攻击，同时还能提高查询性能。

c.execute("INSERT INTO test VALUES (?, 'bar')", (testfield,))

在使用SQL参数时，您可以让数据库库处理引号，并且更好的是，将数据库优化查询并重复使用已优化的查询计划以执行相同基本查询的多个执行（具有不同的参数）。

最后但并非最不重要的，您对抗SQL注入攻击的能力更强，因为数据库库最了解如何转义危险的类似SQL的值。

引用sqlite3文档：

通常，您的SQL操作将需要使用Python变量中的值。 您不应该使用Python的字符串操作来组装查询，因为这样做是不安全的； 它使您的程序容易受到SQL注入攻击的威胁（有关可能出错的幽默示例，请参见http://xkcd.com/327/）。

相反，请使用DB-API的参数替换。 在任何要使用值的地方放置?作为占位符，然后将值元组提供为游标的execute()方法的第二个参数。

- Martijn Pieters

---

这个网站需要一个“+n”按钮，这样我就可以对答案点赞，点赞SQL参数的所有优点的解释以及指向xkcd的链接，每个都点赞。 - abarnert

文本字段后面的尾随逗号有什么意义？ - Jason Sperske

在这种情况下，链接是带着引用的。 :-) - Martijn Pieters

@JasonSperske：我正在确保第二个参数是一个只有一个元素的元组。如果省略逗号，它就只是一个表达式分组。 - Martijn Pieters

@user248237：参数仅适用于值。表名是标识符，而不是值。您的参数被数据库引擎转义，因此最终结果是CREATE TABLE 'test' (a text, b text)，这不是有效的SQL语句。这是一件好事。 - Martijn Pieters