Using single quote when building a string in Cocoa

Question

I am building a string in Cocoa to be used as a SQL statement which will be passed into the FMDB wrappers for sqlite; however, the database craps out with a BAD_ACCESS failure. I have the following code:

prefix = @"SELECT * FROM Table WHERE Field1 LIKE '%";

middle = @"%' OR Field2 LIKE '%";

suffix = @"%' ORDERY BY ";

orderby = @"%' ORDER BY Fieldnames";

sqlStatement = [NSString stringWithFormat:@"%@%@%@%@%@%@", prefix, searchString, middle, searchString, suffix, orderby];

At runtime sqlStatement ends up containing something like the following:

SELECT * FROM Table WHERE Field1 LIKE \'%A%\' OR Field2 LIKE \'%A%\' ORDER BY Fieldnames

For some reason a \ is being added into the string which causes the database to bomb out.

Any ideas how I can keep the \ from appearing in my string?

UPDATE: When I output my variable via NSLog I get the following: SELECT * FROM Table WHERE Field1 LIKE 'OX1.87A8013DBD18F-1027' OR Field2 LIKE 'OX1.87A8013DBD18F-1027'ORDER BY Fieldname.

Problem seems to be with using %A in the string. Even if I hard code the sqlStatement variable to include '%A%' the %A seems to get converted to a memory address or something. If I do a '%ABAP%' I get the output like 'OX1.87A8013DBD18F-1027BAP'. Notice i'm losing the % sign.

Answer 1

The string seems to be constructed correctly. If I run the code:

int main(int argc, char *argv[])
{
    NSAutoreleasePool* pool = [NSAutoreleasePool new];

    NSString* prefix = @"SELECT * FROM Table WHERE Field1 LIKE '%";

    NSString* middle = @"%' OR Field2 LIKE '%";

    NSString* suffix = @"%' ORDERY BY ";

    NSString* orderby = @"%' ORDER BY Fieldnames";

    NSString* sqlStatement = [NSString stringWithFormat:@"%@%@%@%@%@%@", prefix, @"blah", middle, @"blah", suffix, orderby];

    NSLog(@"sqlStatement: %@", sqlStatement);

    [pool drain];

    return 0;
}

I get the following result in the console:

2009-03-05 08:04:29.734 Untitled[79999:813] sqlStatement: SELECT * FROM Table WHERE Field1 LIKE '%blah%' OR Field2 LIKE '%blah%' ORDERY BY %' ORDER BY Fieldnames

Answer 2

I'm going to guess that you're using sqlite, in which case you want to do this slightly differently. First start by creating your query like this:

SELECT * FROM Table WHERE Field1 LIKE ?001 OR Field2 LIKE ?002 ORDER BY Fieldnames

The ?000 syntax is for parameters.

You can then use sqlite3_bind_text to bind your parameters:

sqlite3_bind_text(statement, 1, [@"%bar%" UTF8String], -1, SQLITE_STATIC);
sqlite3_bind_text(statement, 2, [@"%foo%" UTF8String], -1, SQLITE_STATIC);

Hopefully this should point you in the right direction.

Answer 3

What do you mean with "at runtime"? Do you mean in the Xcode debugger?

Seems you are targeting this problem.

The backslash-escaped single quotes only seem to appear inside of the Xcode debugger window's variable summary column. Examining the value of "sql" manually in gdb reveals the correct format:

(gdb) po sql select * from t_state where state_code = 'XX'

and NSLogging the string also leaves out the backslashes. So, I'd say those single quotes are escaped for some reason only in the process of Xcode formatting the object for display in the variable view.

Answer 4

Regarding your 'update':

It looks like the "%A" is getting used as a format specifier, namely:

%A: a 64-bit floating-point number (double), printed in scientific notation with a leading 0X and one hexadecimal digit before the decimal point using a uppercase P to introduce the exponent

See String Programming Guide for Cocoa.

So what's happening is that you're using the sqlStatement string later on as a format control string, which is bad. If you do need to pass sqlStatement to a method/function that interprets the string as a format specifier, you should pass it like do_query(@"%@", sqlStatement)