Select query with like to avoid SQL injection when using fmdb

I am building an iOS app using SQLite and fmdb as wrapper. I am trying to execute the following SQL statement:

SELECT * FROM TABLE WHERE FIELD LIKE '%text%'

with the text coming from a UISearchBar.

Up to now the only way I got it working was with the following code:

NSString *query = [NSString stringWithFormat:@"SELECT * FROM TABLE WHERE FIELD LIKE '%%%@%%'", text];
FMResultSet *results = [db executeQuery:query];

Please note I am posting only the query code. It works well.

What I want though, is to avoid SQL injection so I am trying something like:

NSString *query = @"SELECT * FROM TABLE WHERE FIELD LIKE %%?%%";
FMResultSet *results = [db executeQuery:query, text];

Or something like:

NSString *query = @"SELECT * FROM TABLE WHERE FIELD LIKE ?";
FMResultSet *results = [db executeQuery:query, [NSString stringWithFormat:@"%%%@%%", text]];

That do not work. The same happens with single quotes around the like clause ('%?%') Or single % instead of double.

Can you walk me through this issue?

Solution

You last attempt (without any quotes) is the correct syntax. Are you checking to see if results is non-nil? If nil, you should check for the error string. For example, this works:

NSString *searchString = @"larry";
NSString *likeParameter = [NSString stringWithFormat:@"%%%@%%", searchString];
NSString *sql = @"SELECT text_column FROM test WHERE text_column LIKE ?";

FMResultSet *results = [db executeQuery:sql, likeParameter];

if (!results)
{
    NSLog(@"error: %@", [db lastErrorMessage]);
    [db close];
    return;
}

while ([results next])
{
    NSLog(@"%s: %@", __FUNCTION__, results[0]);
}

[results close];
[db close];

By the way, if you're being particular and you don't want the end user manipulating the parameters or getting unintuitive responses (and you don't want the end user applying their own wildcard characters), you might want to escape occurrences of wildcard characters, such as % or _, using the ESCAPE SQL syntax. Thus, you might want to define a constant for an escape character:

NSString * const kEscapeCharacter = @"\\";

And then, build your SQL like:

NSString *likeParameter = [NSString stringWithFormat:@"%%%@%%", [self escapedLikeParameter:searchString]];
NSString *sql = [NSString stringWithFormat:@"SELECT text_column FROM test WHERE text_column LIKE ? ESCAPE '%@'", kEscapeCharacter];

Where escapedLikeParameter escapes %, _, and the wildcard character itself. Thus:

- (NSString *)escapedLikeParameter:(NSString *)string
{
    NSString *escapedString;
    escapedString = [string stringByReplacingOccurrencesOfString:kEscapeCharacter
                                                                withString:[NSString stringWithFormat:@"%@%@", kEscapeCharacter, kEscapeCharacter]];
    escapedString = [escapedString stringByReplacingOccurrencesOfString:@"_"
                                                             withString:[NSString stringWithFormat:@"%@_", kEscapeCharacter]];
    return [escapedString stringByReplacingOccurrencesOfString:@"%"
                                                    withString:[NSString stringWithFormat:@"%@%%", kEscapeCharacter]];
}

That way, you can search for any string, including those with the multi-character wildcard character, /, or the single character wildcard character, _.