Geek Answers Handbook

Problem with FMDB and pasting value into "executeQuery:" from searchString

When creating a search for my application, I ran into a problem when using the SQLite SQLite framework (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 searchQuery from user input, for example:

 FMResultSet *rs = [db executeQuery:@"SELECT * FROM ZARTICLE WHERE ZTITLEDE LIKE (?)", theSearchQuery];

... the value will not be inserted into SQL Command. And I do not get any returned objects from the database. even if String (theSearchQuery) is the same as in the first example.

Additionally, I am publishing part of the FMDB documentation for your convenience. :)

 Data Sanitization

When providing the SQL statement for FMDB, you should not attempt to "sanitize" any values ​​before inserting. Instead, you should use the standard SQLite binding syntax:

INSERT INTO myTable VALUES (?,?,?)? the character is recognized by SQLite as a placeholder for the value to be inserted. All execution methods accept a variable number of arguments (or a representation of these arguments, such as NSArray or va_list) that have been correctly escaped for you.

So you SHOULD NOT do this (or something like this):

[db executeUpdate: [NSString stringWithFormat: @ "INSERT INTO myTable VALUES (% @)", @ "this has \" many "fancy \" quotes' "]]; Instead, you SHOULD do:

[db executeUpdate: @ "INSERT INTO myTable VALUES (?)", @ "it has \" many "fancy \ quotes'"]; All arguments provided to the -executeUpdate: method (or any of the options that accept the va_list parameter as a parameter) must be objects. Below will not work (and will crash):

[db executeUpdate: @ "INSERT INTO myTable VALUES (?)", 42]; The correct way to insert a number is to put it in an NSNumber object:

[db executeUpdate: @ "INSERT INTO myTable VALUES (?)", [NSNumber numberWithInt: 42]]; Alternatively, you can use the -execute * WithFormat: option to use the NSString-style replacement:

[db executeUpdateWithFormat: @ "INSERT INTO myTable VALUES (% d)", 42]; Inside, -execute * WithFormat: the right boxing methods for you. The following percentage 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 this will have unpredictable results. If for some reason you need the% character that appears in your SQL statement, you should use %%.

+4
source share
2 answers
 NSString *search_text = [NSString stringWithFormat:@"%%%@%%", theSearchQuery]; FMResultSet *rs = [db executeQuery:@"SELECT * FROM ZARTICLE WHERE ZTITLEDE LIKE ?", search_text];
+7
source

I highly recommend avoiding creating queries with stringWithFormat :! There is a good reason why FMDB is trying to force you to use their data sanitation. However, since FMDB is boxing your input, the surrounding bracket in the following code is not needed and may cause your problem.

 [db executeQuery:@"SELECT * FROM ZARTICLE WHERE ZTITLEDE LIKE (?)", theSearchQuery];

Just adding arguments without any parenthisis, because you never know how FMDB blocks your argument internally.

 [db executeQuery:@"SELECT * FROM ZARTICLE WHERE ZTITLEDE LIKE ?", theSearchQuery];

If this still does not work, try using the suggested executeQueryWithFormat: FMDB method:

 [db executeQueryWithFormat:@"SELECT * FROM ZARTICLE WHERE ZTITLEDE LIKE %@", theSearchQuery];
+4
source

More articles:


All Articles