How to pass a variable to sqlstatement?

Question

const char *sqlStatement = "select rowid from mytable where name = 'mac'";

Then I am getting correct result. But If I do :-

NSString *str=@"mac";

const char *sqlStatement = "select rowid from mytable where name = str";

I am getting error no such column : str. How to pass variable to sqlstatements?

Answer 1

NSString *str=@"mac";

NSString *sql =[NSString stringWithFormat:@"select rowid from mytable where name = %@",str];
if (sqlite3_prepare_v2(database, [sql UTF8String], -1, &select_statement, NULL) != SQLITE_OK)
{
    NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(database));
}

Answer 2

As you've found, merely using the same text as a variable name and as a substring of a string literal does not cause the contents of the variable to be spliced into the string. The compiler could theoretically set this up for you, but it doesn't—doing this with no explicit formatting characters isn't a feature of any language I've ever heard of. Even if this were supported, you still couldn't splice NSString objects into C strings.

So, the question is how to splice the string from str into the query string.
The answer is not stringWithFormat:!

That opens you up to SQL injection, which, in the context of a database that the user is accessing from the same machine where it resides, means that the user could corrupt their data by entering the wrong thing. Trying to fix that by escaping things is an interminable game of whack-a-mole and self-doubt (“I hope that's the last of these bugs”).

The correct solution is to let SQLite splice the parameters into the query as only it knows how. You do that by preparing the statement with a question mark at every point where you need to splice in a value, then binding the value(s) into the statement. Each call to one of the bind functions will replace one not-yet-bound placeholder with the value you pass in in a way that is safe—i.e., won't create an invalid or destructive query statement.

Answer 3

@Peter Hosey is absolutely correct: the way to do this is the SQLite binding functions. However, the even better way to do this is to use a wrapper like FMDB or TouchSQL, which both allow you to use stringWithFormat: style syntax, but while still using the binding functions underneath. In other words, they do everything you want to do, but have already done it for you and (no offense) likely have done it better than you would have.