在 Python 和 sqlite 中转义字符 [英] Escaping chars in Python and sqlite

问题描述

我有一个 python 脚本，可以将原始电影文本文件读入 sqlite 数据库.

I have a python script that reads raw movie text files into an sqlite database.

在执行插入之前，我使用 re.escape(title) 将转义字符添加到字符串中，使它们成为数据库安全.

I use re.escape(title) to add escape chars into the strings to make them db safe before executing the inserts.

为什么这不起作用:

In [16]: c.execute("UPDATE movies SET rating = '8.7' WHERE name='\'Allo\ \'Allo\!\"\ \(1982\)'")
--------------------------------------------------------------------------- OperationalError                       Traceback (most recent call last)

/home/rajat/Dropbox/amdb/<ipython console> in <module>()

OperationalError: near "Allo": syntax error

然而这有效(在两个地方删除了\'):

Yet this works (removed \' in two places) :

In [17]: c.execute("UPDATE movies SET rating = '8.7' WHERE name='Allo\ Allo\!\"\ \(1982\)'") Out[17]: <sqlite3.Cursor object at 0x9666e90>

我想不通.我也不能放弃那些引言，因为它们实际上是电影名称的一部分.谢谢.

I can't figure it out. I also can't ditch those leading quotes because they're actually part of the movie title. Thank you.

推荐答案

你做错了.字面意思.您应该使用参数，如下所示:

You're doing it wrong. Literally. You should be using parameters, like this:

c.execute("UPDATE movies SET rating = ? WHERE name = ?", (8.7, "'Allo 'Allo! (1982)"))

这样，您根本不需要任何引用，并且(如果这些值来自不受信任的任何人)，您(此处)也将 100% 安全地免受 SQL 注入攻击.

Like that, you won't need to do any quoting at all and (if those values are coming from anyone untrusted) you'll be 100% safe (here) from SQL injection attacks too.

这篇关于在 Python 和 sqlite 中转义字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！