Using NSString/parameters into SQLite statement iO

问题:

I have my code below. My values _emailVaue _passwordValue and _nameValue are taken from UITextFields in my app. I was under the impression that passing these values into parameters in my SQLite code would allow me to have special characters in those values such as double quotation marks ( " ), however, if I put them in, the SQLite crashes. Is there something I'm doing wrong?

I'm aware that it's probably best to use something like FMDB, but I was hoping that there might be a quicker fix to get me through an upcoming demo.

I'd appreciate any help, thanks!

if (sqlite3_open(dbpath, &_contactDB) == SQLITE_OK)
{

    NSString *insertSQL = [NSString stringWithFormat:
                           @"INSERT INTO CONTACTS (email, password, name) VALUES (\"%@\", \"%@\", \"%@\")",
                           _emailValue, _passwordValue, _nameValue];
    NSLog(insertSQL);
    const char *insert_stmt = [insertSQL UTF8String];
    sqlite3_prepare_v2(_contactDB, insert_stmt,
                       -1, &statement, NULL);
    if (sqlite3_step(statement) == SQLITE_DONE)
    {

    } else {

    }
    sqlite3_finalize(statement);
    sqlite3_close(_contactDB);
}

回答1:

I hope it's OK to answer my own question if it seems to work. Hopefully someone will find it useful. Would appreciate any feedback on where I might be going wrong.

sqlite3_stmt    *statement;
const char *dbpath = [_databasePath UTF8String];
const char *insertSQL;
if (sqlite3_open(dbpath, &_contactDB) == SQLITE_OK)
{
    insertSQL = "INSERT INTO CONTACTS (email, password, name) VALUES (?, ?, ?)";
    if(sqlite3_prepare_v2(_contactDB, insertSQL, -1, &statement, NULL) == SQLITE_OK)
    {
    sqlite3_bind_text(statement, 1, [_emailValue UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(statement, 2, [_passwordValue UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(statement, 3, [_nameValue UTF8String], -1, SQLITE_TRANSIENT);
    }
    if (sqlite3_step(statement) == SQLITE_DONE)
    {
//worked
    } else {


//didn't work
    }
    sqlite3_finalize(statement);
    sqlite3_close(_contactDB);
}

回答2:

I'll try to explain what happened with your code and how it could be improved so that the crash would not occur. I totally agree with the usage of bound arguments, this answer is posted only as it represents an answer to how your crash can be fixed, and might help people that don't have the time to switch to bound statements.

Here's what happened:

1. sqlite3_prepare_v2() failed as your query string was invalid due to the fact that your strings contained the " characted
2. due to the above, statement was either NULL or contained a garbage value
3. sqlite3_step() crashed as an invalid pointer was passed as argument.

Here's the fix:

1. escape all your strings, by replacing " by \", this will generate a valid query; if you were using ' in your query, then you would have to replace ''s by \'

Example for email:

NSString *escapedEmail = [_emailValue stringByReplacingOccurrencesOfString:@"\"" withString:@"\\\""];

2. even if you're sure the query is correct, is still mandatory to check the result of sqlite3_prepare_v2() before using the statement in any other sqlite calls.

As a general note, you need to code very defensively when dealing with C API's as C doesn't forgive you if you forget to check for NULL pointers. Objective-C is more soft, as it allows you to send messages to nil objects.