iPhone SQLlite commands with apostrophes

Question

I'm writing an application for the iPhone that communicates with a SQLite database but I'm running into a small problem. Whenever I try to query information based on a condition that contains an apostrophe, no results are returned.... even if a result that matches the requested condition exists. Let me give some specifics...

SQLite Table

---

Row--Column1--Column2---------

1. Test Data - 001
2. User's Data - 002

Objective-C Code

---

//Create the sql statement
sqlite3_stmt *sqlStatement;

//Create the name of the category that will be passed in
NSString *categoryName = @"User's Data";

//Create the rest of the SQL query
NSString *sqlQuery = "SELECT * FROM theTableName WHERE Column1 = ?";

//If there are no errors in the SQL query
if (sqlite3_prepare_v2(theDatabase, sqlQuery, -1, &sqlStatement, nil) == SQLITE_OK)
{
    //Bind the category name to the sql statement
    sqlite3_bind_text(sqlStatement, 1, [categoryName UTF8String], -1, SQLITE_TRANSIENT);

    //While there are rows being returned
    while (sqlite3_step(sqlStatement) == SQLITE_ROW)
    {
        //Retrieve row data
    }
}
else
{
    //Save error message to the application log and terminate the app
    NSAssert1(0,@"Error: Failed to prepare the SQL statement with message '%s'.", sqlite3_errmsg(database));
}
//Reset the sql statement
sqlite3_reset(sqlStatement);

I'm semi-new to objective C, so my first thought when writing this code was to sanitize the user inputs. But after doing some research, I read that the sqlite3_bind calls do the necessary sanitation for you. But whenever the code runs, the while loop is skipped right over because there are no rows being returned. It should return the second row from the database table. If I copy/paste the exact same SQL query into a SQL managing program (I use SQLite Manager) (and with the necessary query sanitation of course), it returns the correct data.

I've spent a long time trying to debug this myself and even a greater amount of time trying to search online for a similar problem being explained and resolved, but to no avail. As of now, I just disabled the user's ability to key in an apostrophe on the iPhone's virtual keyboard. But this is a feature I'd love to include in my finished product. Can anyone here offer me any helpful tips? Any kind of help would be greatly appreciated.

Answer 1

For sqlite your request will be (as you can see it is even wrong highlighted):

SELECT * FROM theTableName WHERE Column1 = User's data

And it will wait for the closing ' symbol

You should echo ' symbol, for example in following way:

NSString *sqlQuery = [NSString stringWithFormat:@"SELECT * FROM tableName WHERE Column1=\"%@\"", categoryName];

In this case query will be

select * from theTableName where column1="User's data"

that is completely legal query.

In this case you don't need binding any more and final code will look like:

if (sqlite3_prepare_v2(database, [sqlQuery UTF8String], -1, &sqlStatement, nil) == SQLITE_OK)
{
    //While there are rows being returned
    while (sqlite3_step(sqlStatement) == SQLITE_ROW)
    {
       //Retrieve row data
    }
}
else
{
   //Save error message to the application log and terminate the app
    NSAssert1(0,@"Error: Failed to prepare the SQL statement with message '%s'.", sqlite3_errmsg(database));
}

Answer 2

The official character is ''

sanitize with:

NSString *stringToSanitize = @"This is the value with ' character";

NSString *sanitized = [stringToSanitize stringByReplacingOccurrencesOfString:@"'"
                                     withString:@"''"];

Then you can use it on your querys