如何使用Python DB-API安全地生成SQL LIKE语句

Question

我正在尝试使用Python的DB-API组装以下SQL语句：

SELECT x FROM myTable WHERE x LIKE 'BEGINNING_OF_STRING%';

BEGINNING_OF_STRING应该是一个Python变量，通过DB-API安全地填充。我尝试过了。

beginningOfString = 'abc'

cursor.execute('SELECT x FROM myTable WHERE x LIKE '%s%', beginningOfString) 
cursor.execute('SELECT x FROM myTable WHERE x LIKE '%s%%', beginningOfString)

我已经没有想法了，正确的做法是什么？

- laramichaels

Answer 1

如果可以的话，最好将参数与SQL语句分离。 这样，您可以让数据库模块负责正确引用参数。

sql='SELECT x FROM myTable WHERE x LIKE %s'
args=[beginningOfString+'%']
cursor.execute(sql,args)

- unutbu

---

@~unutbu：谢谢，这就解决了。在通过db-api传递参数之前，我没有想到直接将%附加到字符串本身。 - laramichaels

Answer 2

编辑：

正如Brian和Thomas所指出的，更好的方法是使用：

beginningOfString += '%'
cursor.execute("SELECT x FROM myTable WHERE x LIKE ?", (beginningOfString,) )

由于第一种方法容易受到SQL注入攻击的威胁，建议使用其他方式。

---

以下内容仅供参考：

尝试使用：

cursor.execute("SELECT x FROM myTable WHERE x LIKE '%s%%'" % beginningOfString)

- Sean Vieira

---

抱歉？ -1 "SELECT x FROM myTable WHERE x LIKE '%s%%'" % "doom' ; drop table x; select '" - Brian

@Brian；非常有价值的观点！我一直在关注语法错误，我不应该假设“beginning”是干净的数据。感谢你的提醒！ - Sean Vieira

你已经移除了-1，但仍存在语法错误。使用双引号来表示字符串，就不需要转义单引号了。 - Brian

已经编辑修复了语法错误和Thomas指出的bug。 - Sean Vieira

我尝试使用“？”而不是“%s”与mysql-connector-python一起使用，但没有成功。 - Schäfer

Answer 3

请注意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.

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. (Other database modules may use a different placeholder, such as %s or :1.) For example:

# Never do this -- insecure!
symbol = 'IBM'
c.execute("... where symbol = '%s'" % symbol)

# Do this instead
t = (symbol,)
c.execute('select * from stocks where symbol=?', t)

# Larger example
for t in [('2006-03-28', 'BUY', 'IBM', 1000, 45.00),
          ('2006-04-05', 'BUY', 'MSOFT', 1000, 72.00),
          ('2006-04-06', 'SELL', 'IBM', 500, 53.00),
         ]:
    c.execute('insert into stocks values (?,?,?,?,?)', t)

我想你需要这个：

cursor.execute('SELECT x FROM myTable WHERE x LIKE '%?%', (beginningOfString,) )

- Brian

---

@Thomas：为什么？除了我在查询中使用了'%?%'而不是'?%'（他的查询不够一致），我没有看到任何问题。 - Brian

问题在于 "?" 会被替换为你传递的实际参数的引用版本。因此，如果给出例如 "';DROP TABLE x;SELECT '"（请注意这里的单引号和双引号），那么您最终会得到这个明显错误的查询：SELECT x FROM myTable WHERE x LIKE '%';DROP TABLE x; SELECT '%'。 - Thomas Wouters

我不明白。DB-API的参数替换难道不能解决这个问题吗？ - Brian