如何防止 sqlite 将字符串评估为数学表达式? [英] How do I prevent sqlite from evaluating a string as a math expression?

问题描述

可能有一个非常简单的答案的简单问题.我正在将格式为2012-06-10"的日期字符串从 Python 脚本写入 TEXT 列.

例如

<前>cur.execute("CREATE TABLE tbl(date TEXT, ...)")cur.execute('INSERT INTO tbl VALUES(%s)' % (str(date[i])), ...)

脚本实际上是评估日期字符串，因此2012-06-10"被写入表中为1996".通过阅读文档，我猜这与类型亲和力有关，但我无法弄清楚如何覆盖甚至为什么要评估字符串.

解决方案

两种方式:

• 通过使用参数化查询:cur.execute("INSERT INTO tbl VALUES (?), [str(date[i])]).这是最好的方法.这样做. 第二种方法仅供后人使用.
• 通过引用值(注意:这几乎肯定在几乎所有情况下都是错误的):cur.execute("INSERT INTO tbl VALUES ('%s')" %(str(date[i]), ).这种方法不是最理想的，因为除非你小心，否则你很容易受到 SQL 注入的攻击.<​​/li>

要理解为什么会这样，请想象一下您的代码发送到 SQLite 的查询:

 INSERT INTO tbl VALUES (2012-06-10);

SQL 引擎正确评估为:

 INSERT INTO tbl VALUES (1996);

引用该值将解决此问题:

 INSERT INTO tbl VALUES ('2012-06-10');

但如果值中包含某些字符(例如 ' 或空字节等字符)，则会导致问题.

但是，对于参数化查询，值与查询分开发送，因此它们不会被误解.

Simple question that probably has a very simple answer. I am writing date strings of the format "2012-06-10" to a TEXT column from a Python script.

e.g.

cur.execute("CREATE TABLE tbl(date TEXT, ...)")

cur.execute('INSERT INTO tbl VALUES(%s)' % (str(date[i])), ...)

The script is actually evaluating the date string, so "2012-06-10" gets written into the table as "1996". From reading the docs, I'm guessing this has something to do with type affinity, but I can't figure how to override or even why the string would be evaluated.

解决方案

Two ways:

• By using a parameterized query: cur.execute("INSERT INTO tbl VALUES (?), [str(date[i])]). This is the best way. Do it this way. The second method is only being included for posterity.
• By quoting the value (note: this is almost certainly in almost every case the wrong way): cur.execute("INSERT INTO tbl VALUES ('%s')" %(str(date[i]), ). This method is suboptimal because, unless you're careful, you'll be vulnerable to SQL injection.

To understand why this is going on, imagine the query that your code is sending to SQLite:

 INSERT INTO tbl VALUES (2012-06-10);

Which the SQL engine correctly evaluates to:

 INSERT INTO tbl VALUES (1996);

Quoting the value will solve this issue:

 INSERT INTO tbl VALUES ('2012-06-10');

But can lead to issues if the value has certain characters in it (characters like ' or the null byte).

However, with a parameterized query, the values are sent separately from the query, so there is no chance they will be misinterpreted.

这篇关于如何防止 sqlite 将字符串评估为数学表达式?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！