如果我的SQL语句仅被评估一次，那么为什么要使用sqlite3_bind()? [英] If my SQL statement is only evaluated once, then why would I use sqlite3_bind()?

问题描述

例如，第一个对问题进行评论正确地在SQLite插入语句中转义单引号-iOS 说:不要使用stringWithFormat来构建查询.请使用sqlite3_bind_xxx语句正确地执行此操作."我已经看到(在Stack Overflow上以及之后)许多类似这样的评论/答案，这些评论/答案无条件地建议使用参数而不是文字.

For example, the first comment on the question How to properly escape single quotes in SQLite insert statement - iOS says "Don't use stringWithFormat to build your query. Do it properly with sqlite3_bind_xxx statements." I've seen (on Stack Overflow and beyond) many comments/answers like this that unconditionally suggest using parameters instead of literals.

但是，我在 SQLite网站上没有看到类似的建议.我确实看到"< SQLite C/C ++接口简介"表示"SQLite允许将相同的准备好的语句多次评估"使用 sqlite3_bind().

However, I don't see any suggestions like that on the SQLite website. I do see that section "6. Binding Parameters and Reusing Prepared Statements" in "An Introduction To The SQLite C/C++ Interface" says that "SQLite allows the same prepared statement to be evaluated multiple times" by using sqlite3_bind().

因此，如果我仅评估一次SQL语句，那为什么我要使用参数而不是仅使用文字(并且在必要时转义用户输入的文本或将数据自己转换为BLOB文字)?我在这里想念什么吗?我了解重用已准备好的语句"，从而避免调用 sqlite3_prepare()可以可以显着提高性能"，但我想暂时保持我的代码尽可能简单，以后再提高性能.

So if I'm only evaluating an SQL statement once, then why would I use parameters instead of just using literals (and when necessary escaping user-inputted text or converting data to BLOB literals myself)? Am I missing something here? I understand that "reusing prepared statements" and thereby "avoiding calls to sqlite3_prepare() can give a significant performance improvement", but I'd like to keep my code as simple as possible for now and maybe enhance performance later.

推荐答案

始终使用 sql_bind的原因():

• 由于SQLite限制了SQL语句的长度，它允许您插入更多数据
• 对于大字符串，它更快，因为它不需要进行过多的分析或复制"
• 对于大型BLOB来说，它速度更快且使用的内存更少，因为将数据转换为BLOB文字需要花费时间，而十六进制字符串占用的内存要比它们表示的数据更多

• 在" 3.SQL语句的最大长度"

如果SQL语句的长度限制为一百万个字节，那么很显然，您无法通过将它们作为文字嵌入到INSERT语句中来插入数百万个字节的字符串.但是您无论如何都不应该这样做.对数据使用主机参数.准备这样的简短SQL语句:

If an SQL statement is limited to be a million bytes in length, then obviously you will not be able to insert multi-million byte strings by embedding them as literals inside of INSERT statements. But you should not do that anyway. Use host parameters for your data. Prepare short SQL statements like this:

        INSERT INTO tab1 VALUES(?，?，?);

        INSERT INTO tab1 VALUES(?,?,?);

然后使用 sqlite3_bind_XXXX()函数将大字符串值绑定到SQL语句.绑定的使用避免了对字符串中的引号字符进行转义的需要，从而降低了SQL注入攻击的风险. [ sic ]的运行速度也更快，因为不需要分析或复制大字符串.

Then use the sqlite3_bind_XXXX() functions to bind your large string values to the SQL statement. The use of binding obviates the need to escape quote characters in the string, reducing the risk of SQL injection attacks. It is [sic] also runs faster since the large string does not need to be parsed or copied as much.

注意:我发现使用绑定消除了对字符串中的引号字符进行转义的需要"的原因不足，因为这样做很容易，只需简单地以我的应用程序的编程语言或使用一种语言即可 SQLite的格式化字符串打印功能与

Note: I find the reason that "the use of binding obviates the need to escape quote characters in the string" insufficient on its own because doing that is easy enough to simply do explicitly either in my app's programming language or by using one of SQLite's Formatted String Printing Functions with either the %q or %Q substitution types.

这篇关于如果我的SQL语句仅被评估一次，那么为什么要使用sqlite3_bind()?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！