How to pass a value from Cocoa to an SQLite query

Question

Hi, first of all please let me say that I am quite new to objective c development. I am writing a small app for personal use for the iphone, but I have some problems executing the following code:

NSString *sql = [[NSString alloc] initWithFormat:@"select color_r, color_g, color_b from Calendar where ROWID = %@", [calendarsID objectForKey:[arrayColors objectAtIndex:row]]];

sqlite3_stmt *selectstmt;

if(sqlite3_prepare_v2(database, sql, -1, &selectstmt, NULL) == SQLITE_OK)

The compiler tells me that I am passing argument 2 of sqlite3_prepare_v2 from an incompatible pointer type. The program gets, anyhow, compiled and runs but, when it has to execute the code that I've just shown you, it produces an error. It says that there is a syntax error in the query, and the syntax error is just in the last part of the query. Instead of having:

select color_, color_g, color_b from Calendar where ROWID = 63 (for example)

I get strange characters in the place of the last number (63). I guess this is a problem related to string conversion. Can please anyhone help me?

Thank you very much for your attention. Alessio

Answer 1

Alessio,

It would be easier to answer this with a little more detail. For example, what type of objects are in arrayColors?

If the objects in arrayColors are not NSStrings, then you want to call a method (e.g. description) to create an NSString representation.

Aaron

Answer 2

The problem is that a NSString is not what sqlite3_prepare_v2() is expecting for its second argument. It wants a const char*. C is a little lax here, and it lets you implicitly cast an NSString* to a const char*, which is WRONG -- the compiler is giving you a warning which you shouldn't ignore.

You need to convert the NSString into a const char*. The simplest way to do this is to use the -UTF8String message to convert it into UTF-8:

//                              V---- HERE ----V
if(sqlite3_prepare_v2(database, [sql UTF8String], -1, &selectstmt, NULL) == SQLITE_OK)

Alternatively, in the unlikely case that you want to use a different encoding, you can use the -cStringUsingEncoding: message to convert the string to a C string using a specific character encoding.

Answer 3

I think your main problem is that you're using the sqlite C api directly instead of a wrapper.

Answer 4

Why are you not using one of SQLite's value binding API? The statement would look something like

SELECT color_r, color_g, color_b FROM Calendar WHERE ROWID = ?;

And you would pass the argument directly to SQLite, for it to safely insert into the query. This will protect you from SQL injection attacks. (The example you show may be a safe one, depending on where those values ultimately come from, but using sprintf or stringWithFormat: to construct SQL queries is a very very bad habit to get into. Don't get screwed by Little Bobby Tables.)

As for why you're having the problem, SQLite doesn't accept Cocoa types like NSString *. When you switch to using SQLite to format the query, you'll be able to learn from its documentation which type you should be using. You can then ask another question about how to perform that conversion.