how to retrieve data from sqlite using WHERE clause iOS 8

Question

I'm very new to IOS programming and I'm having an issue retrieving some data from my database. It's a simple login page that I have done on my application. I need to check if the username exists and It seems to fail at the following line sqlite3_step(statement) == SQLITE_ROW and I also seem to get a warning in the line where I'm writing my sql query saying "Data argument not used by format string". Following is my code

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

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

        NSString *query = [NSString stringWithFormat:@"SELECT USERNAME,PASSWORD FROM UserInfo WHERE USERNAME = \"%%@\"", self.txtUsername.text];
        const char *query_statement = [query UTF8String];

        if(sqlite3_prepare_v2(_db, query_statement, -1, &statement, NULL) == SQLITE_OK){
            if(sqlite3_step(statement) == SQLITE_ROW){

                NSString *username = [[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 0)];
                NSString *password = [[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 1)];

                if([username isEqualToString:self.txtUsername.text] && [password isEqualToString:self.txtPassword.text])
                {
                    [self showUIAlertWithMessage:@"Successfully logged in" andTitle:@"Message"];
                    [self shouldPerformSegueWithIdentifier:@"Login" sender:self];
                }
            }
            else{

                [self showUIAlertWithMessage:@"Username not found" andTitle:@"Message"];
            }
        }
        sqlite3_finalize(statement);
        sqlite3_close(_db);
    }

1) Don't just check return codes for "OK", but capture and log them if bad, and also log the result from calling sqlite3_errmsg. 2) NSLog your query strings while debugging. 3) Use sqlite3 in a command window to test your query strings, until you become familiar with them. — Hot Licks, Oct 27 '14 at 12:05

score 1 · Accepted Answer · answered Oct 27 '14 at 10:58

1

The format specifier for NSString is %@ and not %%@ (actually any NSObject-derived class that implements the description method).

Using %% escapes the specifier so it will generate the literal sequence %@ in your case.

NOTE: Consider using bind variables which will avoid possible SQL-injection attacks.

NOTE 2: This test is redundant, given the database will have already done it:

[username isEqualToString:self.txtUsername.text]

answered Oct 27 '14 at 10:58

trojanfoe

120,358
21
212
242

Thanks @trojanfoe. I still have no luck with the string literal fix. fails at the same line of code and when I debug the query looks like SELECT USERNAME,PASSWORD FROM UserInfo WHERE USERNAME = "". it doesn't seem to pick up the value from my textfield – Mr.Noob Oct 27 '14 at 11:54
1

@Mr.Noob OK so the field is empty or not connected properly in IB. – trojanfoe Oct 27 '14 at 12:05

how to retrieve data from sqlite using WHERE clause iOS 8

1 Answers1