title: Correct parameter binding for SELECT WHERE .. LIKE using fmdb?
body: <p>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.</p>
  
  <p>e.g.</p>
  
  <pre><code>// '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];
  </code></pre>
  
  <p>Or</p>
  
  <pre><code>results = [db executeQuery:@"SELECT * FROM items WHERE track LIKE '%?%' ORDER BY linkNum;", filter];
  </code></pre>
  
  <p>Or</p>
  
  <pre><code>results = [db executeQuery:@"SELECT * FROM items WHERE track LIKE '%?%' ORDER BY linkNum;" withArgumentsInArray:@[filter]];
  </code></pre>
  
  <p>I've stepped through and all methods converge in the fmdb method:</p>
  
  <pre><code>- (FMResultSet *)executeQuery:(NSString *)sql withArgumentsInArray:(NSArray*)arrayArgs orDictionary:(NSDictionary *)dictionaryArgs orVAList:(va_list)args
  </code></pre>
  
  <p>Depending on the approach, and therefore which params are nil, it then either calls <code>sqlite3_bind_parameter_count(pStmt)</code>, which always returns zero, or, for the dictionary case, calls <code>sqlite3_bind_parameter_index(..)</code>, which also returns zero, so the parameter doesn't get slotted into the LIKE and then the resultSet from the query is wrong.</p>
  
  <p>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:</p>
  
  <pre><code>NSString *queryString = [NSString stringWithFormat:@"SELECT * FROM items WHERE track LIKE '%%%@%%' ORDER BY linkNum;", filter];
  results = [db executeQuery:queryString];
  </code></pre>
  
  <p>(I've also tried all permutations but with escaped double-quotes in place of the single quotes shown here)</p>
  
  <p><strong>Update:</strong></p>
  
  <p>I've also tried fmdb's own …WithFormat variant, which should provide convenience and protection from injection:</p>
  
  <pre><code>[db executeQueryWithFormat:@"SELECT * FROM items WHERE track LIKE '%%%@%%' ORDER BY linkNum;", filter];
  </code></pre>
  
  <p>Again, stepping into the debugger I can see that the LIKE gets transformed from this:</p>
  
  <pre><code>… LIKE '%%%@%%' ORDER BY linkNum;
  </code></pre>
  
  <p>To this:</p>
  
  <pre><code>… LIKE '%%?%' ORDER BY linkNum;
  </code></pre>
  
  <p>… which also goes on to return zero from <code>sqlite3_bind_parameter_count()</code>, where I would expect a positive value equal to "the index of the largest (rightmost) parameter." (from the sqlite docs)</p>