Objective-C sqlite adding a query parameter inside a LIKE clause

Question

I am using sqlite in Objective-C through the C API. One of the features of my application is that the user can search for the username and each new character that they enter, an SQL query is executed using LIKE to find all the people whose names match each other. The problem I am facing parameterizes a match inside LIKE, without a question mark, which is interpreted literally. That is, I believe that the application is currently looking for the names of people that include ? in them (this is nobody).

My code is as follows:

 const char *sql = "SELECT rowid, name, email FROM person WHERE name LIKE '%?%'"; sqlite3_stmt *sqlStatement; if(sqlite3_prepare_v2(db, sql, -1, &sqlStatement, NULL) != SQLITE_OK) { NSLog(@"Problem preparing getFilteredPeopleStubs statement."); } if(sqlite3_bind_text(sqlStatement, 1, [searchText UTF8String], -1, SQLITE_TRANSIENT) != SQLITE_OK){ NSLog(@"Problem binding search text param."); } 

Basically, searchText is where I want to get the name from, but for now, I think it's just looking for people whose name it contains ? , due to single quotes in '%?%' . Is there a solution to this problem that still allows me to use a parameterized query (protection against SQL injection) and achieve what I am after?

Answer 1

Put % characters in searchText , for example (sorry for the pun):

 const char *sql = "SELECT rowid, name, email FROM person WHERE name LIKE ?"; sqlite3_stmt *sqlStatement; if(sqlite3_prepare_v2(db, sql, -1, &sqlStatement, NULL) != SQLITE_OK) { NSLog(@"Problem preparing getFilteredPeopleStubs statement."); } NSString *bindParam = [NSString stringWithFormat:@"%%%@%%", searchText]; if(sqlite3_bind_text(sqlStatement, 1, [bindParam UTF8String], -1, SQLITE_TRANSIENT) != SQLITE_OK){ NSLog(@"Problem binding search text param."); }