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

I'm trying to insert strings read from a file into an sqlite database in Python. The strings have whitespace (newline, tab characters, and spaces) and also have appearances of single or double quotes. Here's how I try to do it:

我正在尝试将从文件中读取的字符串插入到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()

I find that this fails with the error:

我发现这失败了，错误：

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

How can I achieve this? Do the strings need to be escaped before insertion in the db, and if so how? thanks.

我怎样才能做到这一点？在插入数据库之前是否需要对字符串进行转义，如果是这样的话？谢谢。

1 个解决方案

#1

You use SQL parameters instead of string formatting:

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

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

When using SQL parameters you let the database library handle the quoting, and even better, give the database to optimize the query and reuse the optimized query plan for multiple executions of the same basic query (with different parameters).

使用SQL参数时，让数据库库处理引用，甚至更好，让数据库优化查询并重用优化的查询计划，以便对同一基本查询（具有不同参数）进行多次执行。

Last but not least, you are much better defended against SQL injection attacks as the database library knows best how to escape dangerous SQL-like values.

最后但并非最不重要的是，您可以更好地防范SQL注入攻击，因为数据库库最了解如何逃避危险的类似SQL的值。

To quote the sqlite3 documentation:

引用sqlite3文档：

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).

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

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.

而是使用DB-API的参数替换。放？作为占位符，无论您想要使用哪个值，然后提供一个值元组作为游标的execute（）方法的第二个参数。

#1