Correct parameter binding for SELECT WHERE .. LIKE

Question

First time user of fmdb here, trying to start off doing things correctly. I have a simple single table that I wish to perform a SELECT WHERE .. LIKE query on and after trying several of the documented approaches, I can't get any to yield the correct results.

e.g.

// 'filter' is an NSString * containing a fragment of
//  text that we want in the 'track' column
NSDictionary *params =
  [NSDictionary dictionaryWithObjectsAndKeys:filter, @"filter", nil];

FMResultSet *results =
  [db executeQuery:@"SELECT * FROM items WHERE track LIKE '%:filter%' ORDER BY linkNum;"
      withParameterDictionary:params];

Or

results = [db executeQuery:@"SELECT * FROM items WHERE track LIKE '%?%' ORDER BY linkNum;", filter];

Or

results = [db executeQuery:@"SELECT * FROM items WHERE track LIKE '%?%' ORDER BY linkNum;" withArgumentsInArray:@[filter]];

I've stepped through and all methods converge in the fmdb method:

- (FMResultSet *)executeQuery:(NSString *)sql withArgumentsInArray:(NSArray*)arrayArgs orDictionary:(NSDictionary *)dictionaryArgs orVAList:(va_list)args

Depending on the approach, and therefore which params are nil, it then either calls sqlite3_bind_parameter_count(pStmt), which always returns zero, or, for the dictionary case, calls sqlite3_bind_parameter_index(..), which also returns zero, so the parameter doesn't get slotted into the LIKE and then the resultSet from the query is wrong.

I know that this is absolutely the wrong way to do it (SQL injection), but it's the only way I've managed to have my LIKE honoured:

NSString *queryString = [NSString stringWithFormat:@"SELECT * FROM items WHERE track LIKE '%%%@%%' ORDER BY linkNum;", filter];
results = [db executeQuery:queryString];

(I've also tried all permutations but with escaped double-quotes in place of the single quotes shown here)

Update:

I've also tried fmdb's own …WithFormat variant, which should provide convenience and protection from injection:

[db executeQueryWithFormat:@"SELECT * FROM items WHERE track LIKE '%%%@%%' ORDER BY linkNum;", filter];

Again, stepping into the debugger I can see that the LIKE gets transformed from this:

… LIKE '%%%@%%' ORDER BY linkNum;

To this:

… LIKE '%%?%' ORDER BY linkNum;

… which also goes on to return zero from sqlite3_bind_parameter_count(), where I would expect a positive value equal to "the index of the largest (rightmost) parameter." (from the sqlite docs)

Answer 1

The error was to include any quotes at all:

[db executeQuery:@"SELECT * FROM items WHERE track LIKE ? ORDER BY linkNum;", filter]; 

… and the % is now in the filter variable, rather than in the query.