sqlite3_step(statement) == SQLITE_DONE returning false all the time

Question

Guys I have an issue with my snippet. I must also say I'm a newbie. I'm trying to insert data in to sqlite. but I keeps failing as sqlite_step == sqlite_done returns false all the time. Am I doing something wrong here. I had done something similar before and it was working fine. following is the code

sqlite3_stmt *statement;
const char *dbpath = [_databasePath UTF8String];

if(sqlite3_open(dbpath, &_db) == SQLITE_OK){
    NSString *insertSQL = [NSString stringWithFormat:@"INSERT INTO userInfo (name, email, username, password) VALUES (\"%@\",\"%@\",\"%@\",\"%@\")", self.txtName.text, self.txtEmail.text, self.txtUsername.text, self.txtPassword.text];
    if([self validateRegistration])
    {
        const char *insert_statement = [insertSQL UTF8String];
        sqlite3_prepare_v2(_db, insert_statement, -1, &statement, NULL);

        if(sqlite3_step(statement) == SQLITE_DONE){
            [self showUIAlertWithMessage:@"User added to the database" andTitle:@"Message"];
            self.txtName.text = @"";
            self.txtEmail.text = @"";
            self.txtUsername.text = @"";
            self.txtPassword.text = @"";
            self.txtConfirmPassword.text = @"";
        }else{
            [self showUIAlertWithMessage:@"Failed to add the user" andTitle:@"Error"];
        }
        sqlite3_finalize(statement);
        sqlite3_close(_db);
    }
}

Capture return codes & log the values of "bad" ones, don't simply test them. And capture *all* of them. And then log the value from sqlite3_errmsg when you get a bad return code. — Hot Licks, Oct 25 '14 at 15:48
It's a Bad Idea to use +[NSString stringWithFormat:] to construct a SQLite statement. Best case is that you'll get mysterious failures when a user enters a double-quote character or similar. Worst case it can leave you open to SQL injection from a malicious user. You should instead use placeholders and the [`sqlite3_bind_*`](https://www.sqlite.org/c3ref/bind_blob.html) APIs to do this safely. — bdash, Oct 26 '14 at 06:05

score 1 · Answer 1 · answered Oct 25 '14 at 15:23

1

You must check the return value of sqlite3_prepare_v2.

If either sqlite3_prepare_v2 or sqlite3_step fails, you must get the actual error message with sqlite3_errmsg.

answered Oct 25 '14 at 15:23

CL.

173,858
17
217
259

Thanks @CL. I am a bit new to this! could you explain to me how I could do it please? I mean to the actual error message? and I seem to get the result code from sqlite_prepare_v2 as 1 – Mr.Noob Oct 25 '14 at 15:33

Rob · Answer 2 · 2014-10-26T05:58:46.257

If you check the result of sqlite3_prepare_v2, it almost certainly is not SQLITE_OK. And if you look at sqlite3_errmsg, it will tell you precisely what is wrong:

if (sqlite3_prepare_v2(_db, insert_statement, -1, &statement, NULL) != SQLITE_OK) {
    NSLog(@"insert failed: %s", sqlite3_errmsg(_db));

Unrelated, but you should not use stringWithFormat to build your SQL. You should use ? placeholders in the SQL and then manually bind the values with sqlite3_bind_text() (or whatever).

const char *insert_statement = "INSERT INTO userInfo (name, email, username, password) VALUES (?, ?, ?, ?)";

if (sqlite3_prepare_v2(_db, insert_statement, -1, &statement, NULL) != SQLITE_OK) {
    NSLog(@"prepare failed: %s", sqlite3_errmsg(_db));

if (sqlite3_bind_text(statement, 1, [self.txtName.text UTF8String], -1, NULL) != SQLITE_OK) 
    NSLog(@"bind 1 failed: %s", sqlite3_errmsg(_db));

if (sqlite3_bind_text(statement, 2, [self.txtEmail.text UTF8String], -1, NULL) != SQLITE_OK) 
    NSLog(@"bind 2 failed: %s", sqlite3_errmsg(_db));

if (sqlite3_bind_text(statement, 3, [self.txtUsername.text UTF8String], -1, NULL) != SQLITE_OK) 
    NSLog(@"bind 3 failed: %s", sqlite3_errmsg(_db));

if (sqlite3_bind_text(statement, 4, [self.txtPassword.text UTF8String], -1, NULL) != SQLITE_OK) 
    NSLog(@"bind 4 failed: %s", sqlite3_errmsg(_db));

if(sqlite3_step(statement) == SQLITE_DONE) {
    [self showUIAlertWithMessage:@"User added to the database" andTitle:@"Message"];
    self.txtName.text = @"";
    self.txtEmail.text = @"";
    self.txtUsername.text = @"";
    self.txtPassword.text = @"";
    self.txtConfirmPassword.text = @"";
}else{
    NSLog(@"step failed: %s", sqlite3_errmsg(_db));
    [self showUIAlertWithMessage:@"Failed to add the user" andTitle:@"Error"];
}

If you find this cumbersome, I'd suggest you consider FMDB, a SQLite wrapper, that does all of the appropriate binding of values to ? placeholders for you.

score -2 · Answer 3 · edited Mar 24 '17 at 22:07

-2

You can use sqlite3_exec() for this:

char *err;
int code = sqlite3_exec(_db,insert_statement,NULL,NULL,&err);
if (code != SQLITE_OK) {
    NSLog(@"something went wrong: %s", err);
}

You then tend to use the prepare function for reading data like this:

sqlite3_stmt *stmt;
int code = sqlite3_prepare_v2(_db,_query,-1,&stmt,NULL);
if (code == SQLITE_OK) {
    while (sqlite3_step(stmt) == SQLITE_ROW) {
        // Retrieve data here e.g.
        // int num = sqlite3_column_int(stmt, 0);
    }
}

See the documentation here for sqlite3_exec()

edited Mar 24 '17 at 22:07

marc_s

732,580
175
1,330
1,459

answered Oct 25 '14 at 15:12

Rob Sanders

5,197
3
31
58

Thanks @RASS I see. But can you find anything wrong in the code i have provided? Because i had the same thing working! – Mr.Noob Oct 25 '14 at 15:13
I can't see anything overtly wrong with your code but I've always used `sqlite3_exec()` to insert data into a database. You should always check that `sqlite3_prepare_v2()` returns `SQLITE_OK` to make sure that your insert_statement is OK. Also you could see if `sqlite3_step()` is retuning `SQLITE_OK` rather than `SQLITE_DONE`. – Rob Sanders Oct 25 '14 at 15:19
Have you run the create table statement? – Rob Sanders Oct 25 '14 at 15:20
No, advising `sqlite3_exec` is definitely the wrong direction to go here. Not only should he be using `sqlite3_prepare_v2`, but he should retire `stringWithFormat` with (a) SQL with `?` placeholders for the values; and (b) use `sqlite3_bind_xxx()` functions to bind values to those `?` placeholders in the SQL. Using `sqlite3_exec` makes it impossible for Sr.Novato to do the right thing here. – Rob Oct 25 '14 at 15:27
`sqlite3_bind_xxx()` as I understand it is for when you wish to run multiple statements without having to prepare multiple times which is expensive. As Mr.Noob is just adding the one set of data, what is the problem with `sqlite3_exec()`? – Rob Sanders Oct 25 '14 at 15:31
No, the `sqlite3_bind_xxx()` is invaluable whenever inserting text values into databases. With `sqlite3_exec`, it would fail if the inserted value happened to have a quotation mark in it. As general counsel, you should always avoid `stringWithFormat` to build SQL as it makes you susceptible to innocent mistakes like that (as well as make your database susceptible to SQL injection attacks). – Rob Oct 25 '14 at 15:33
@RASS Well I feel a bit stupid right now as I haven't changed columns of my table as I had changed some of the values that I am inserting. Everything works fine. you put me the correct path to check my create table statement. Thanks again – Mr.Noob Oct 25 '14 at 15:42

score -2 · Accepted Answer · answered Oct 25 '14 at 15:44

-2

I had this issue because I haven't updated my create table statement according to my insert statements as I had made some changed some values that I am inserting.

answered Oct 25 '14 at 15:44

Mr.Noob

1,005
3
24
58

sqlite3_step(statement) == SQLITE_DONE returning false all the time

4 Answers4