FMDB 出现问题并在“executeQuery:”中插入值来自搜索字符串
在为我的应用程序构建搜索时，我在使用 FMDB SQLite Wrapper (https://github.com/ccgus/fmdb) 时遇到了问题。
当我使用此 SQL 命令搜索数据库时，一切都很好。返回了 13 个对象，我可以使用它们。
FMResultSet *rs = [db executeQuery:@"SELECT * FROM ZARTICLE WHERE ZTITLEDE LIKE '%Daimler%'"];
但是，当我尝试从用户输入中插入 searchQuery 时，如下所示：
FMResultSet *rs = [db executeQuery:@"SELECT * FROM ZARTICLE WHERE ZTITLEDE LIKE (?)", theSearchQuery];
...该值不会插入到 SQL 命令中。而且我没有从数据库中得到任何返回的对象。即使字符串 (theSearchQuery) 与第一个示例中编写的字符串相同。
另外，为了方便您，我发布了 FMDB 文档中的一部分。 :)
Data Sanitization
向 FMDB 提供 SQL 语句时，您不应尝试在插入之前“清理”任何值。相反，您应该使用标准 SQLite 绑定语法：
INSERT INTO myTable VALUES (?, ?, ?) 这 ？ SQLite 将字符识别为要插入的值的占位符。执行方法都接受可变数量的参数（或这些参数的表示形式，例如 NSArray 或 va_list），这些参数都已为您正确转义。
因此，你不应该这样做（或类似的事情）：
[db executeUpdate:[NSString stringWithFormat:@"INSERT INTO myTable VALUES (%@)", @"this has \" much of ' bizarre \"quotes '"] ]; 相反，你应该这样做：
[db executeUpdate:@"INSERT INTO myTable VALUES (?)", @"this has \" 很多 ' 奇怪的 \" 引号 '"]; 提供给 -executeUpdate: 方法（或接受 va_list 作为参数的任何变体）的所有参数都必须是对象。以下内容将不起作用（并且会导致崩溃）：
[db executeUpdate:@"INSERT INTO myTable VALUES (?)", 42]; 插入数字的正确方法是将其放入 NSNumber 对象中：
[dbexecuteUpdate:@"INSERT INTO myTable VALUES (?)", [NSNumber numberWithInt:42]]; 或者，您可以使用 -execute*WithFormat: 变体来使用 NSString 样式替换：
[db executeUpdateWithFormat:@"INSERT INTO myTable VALUES (%d)", 42]; 在内部， -execute*WithFormat: 方法会为您正确地装箱。可以识别以下百分比修饰符：%@、%c、%s、%d、%D、%i、%u、%U、%hi、%hu、%qi、%qu、%f、%g、% ld、%lu、%lld 和 %llu。使用除这些之外的修饰符将会产生不可预测的结果。如果由于某种原因需要在 SQL 语句中出现 % 字符，则应使用 %%。
While building a Search for my app i ran into a problem whilst using the FMDB SQLite Wrapper (https://github.com/ccgus/fmdb).
When I search my database with this SQL Command, everything is fine. 13 objects are returned and I can use them.
FMResultSet *rs = [db executeQuery:@"SELECT * FROM ZARTICLE WHERE ZTITLEDE LIKE '%Daimler%'"];
But when i try to insert the searchQuery from the User Input like this:
FMResultSet *rs = [db executeQuery:@"SELECT * FROM ZARTICLE WHERE ZTITLEDE LIKE (?)", theSearchQuery];
... the value is dont be inserted into SQL Command. And I dont get any returned objects from the DB. even if the String (theSearchQuery) is the same written in the first example.
Additionaly I post a part from the documentation of FMDB for your convinience. :)
Data Sanitization
When providing a SQL statement to FMDB, you should not attempt to "sanitize" any values before insertion. Instead, you should use the standard SQLite binding syntax:
INSERT INTO myTable VALUES (?, ?, ?)
The ? character is recognized by SQLite as a placeholder for a value to be inserted. The execution methods all accept a variable number of arguments (or a representation of those arguments, such as an NSArray or a va_list), which are properly escaped for you.
Thus, you SHOULD NOT do this (or anything like this):
[db executeUpdate:[NSString stringWithFormat:@"INSERT INTO myTable VALUES (%@)", @"this has \" lots of ' bizarre \" quotes '"]];
Instead, you SHOULD do:
[db executeUpdate:@"INSERT INTO myTable VALUES (?)", @"this has \" lots of ' bizarre \" quotes '"];
All arguments provided to the -executeUpdate: method (or any of the variants that accept a va_list as a parameter) must be objects. The following will not work (and will result in a crash):
[db executeUpdate:@"INSERT INTO myTable VALUES (?)", 42];
The proper way to insert a number is to box it in an NSNumber object:
[db executeUpdate:@"INSERT INTO myTable VALUES (?)", [NSNumber numberWithInt:42]];
Alternatively, you can use the -execute*WithFormat: variant to use NSString-style substitution:
[db executeUpdateWithFormat:@"INSERT INTO myTable VALUES (%d)", 42];
Internally, the -execute*WithFormat: methods are properly boxing things for you. The following percent modifiers are recognized: %@, %c, %s, %d, %D, %i, %u, %U, %hi, %hu, %qi, %qu, %f, %g, %ld, %lu, %lld, and %llu. Using a modifier other than those will have unpredictable results. If, for some reason, you need the % character to appear in your SQL statement, you should use %%.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我强烈建议避免使用 stringWithFormat:! 创建查询FMDB 试图强迫您使用他们的数据清理是有充分理由的。但是，由于 FMDB 正在对您的输入进行装箱，因此不需要以下代码中的括号，这可能会导致您的问题。
简单地添加参数而无需任何括号，因为您永远不知道 FMDB 在内部如何包装您的参数。
如果这仍然不起作用，请尝试使用建议的 FMDB 的executeQueryWithFormat：方法：
I would highly recommend to avoid creating queries with stringWithFormat:! There is a good reason why FMDB tries to force you to use their data sanitization. However, since FMDB is boxing your input, surrounding parenthesis in the following code are not needed and may cause your problem.
Simple add arguments without any parenthisis because you never know how FMDB boxes your argument internally.
If this still doesn't work try to use the suggested executeQueryWithFormat: method of FMDB: