iPhone SQLite Database Reading And Writing

Question

So I am trying to work with SQLite in one of iPhone applications and I am using the sqlite3 library. I am able to access the database and even make a query; in fact the query accesses the exact data but for some reason the string I am getting back is a long integer and not the string I was looking for. Here is the database and code:

Filename: Package.sql
Table Lessons
LessonID VARCHAR(64) Primary Key | LessonName VARCHAR(100) | EntryDate (DATETIME) | Chrono VARCHAR (20) 
bfow02nso9xjdo40wksbfkekakoe29ak | Learning The History    | 2010-08-05 16:24:35  | 0001

And the iPhone Code

...
-(NSString *)getRow:(NSString *)tablename where:(NSString *)column equals:(NSString *)value {

  const char *query = [[[[[[[@"SELECT * FROM `" stringByAppendingString:tablename] stringByAppendingString:@"` WHERE `"] stringByAppendingString:column] stringByAppendingString:@"` = '"] stringByAppendingString:value] stringByAppendingString:@"';"] cStringUsingEncoding:NSUTF8StringEncoding];

  NSString *result;

  if(sqlite3_open([dbpath UTF8String], &database) == SQLITE_OK) {

    sqlite3_stmt *compiledQuery;

    if(sqlite3_prepare_v2(database, query, -1, &compiledQuery, NULL) == SQLITE_OK) {

      while(sqlite3_step(compiledQuery) == SQLITE_ROW) {

        NSString *str_temp = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledQuery, 2)];

        result = str_temp;
        
      }

      sqlite3_finalize(compiledQuery);

    }

    sqlite3_close(database);

  }
 
  return result;

}
...

When the code executes:

  CDatabase *db = [[CDatabase alloc]initWithDatabase:@"Package.sql"];
  NSString *result = [db getRow:@"Lessons" where:@"Chrono" equals:@"0001"];

the returned value NSString *result has a value of "1,364,111". Why is it doing that??? It should be "Learning The History"

You should try Gus Mueller's FMDatabase. I use it in my projects, and it does the job quite nicely. (Just remember to retain the FMDatabase object if you are going to use it throughout the whole application.) — BP.

Jesse Beder Jesse Beder · Accepted Answer · 2010-08-06T01:19:40

Are you sure that any of your SQLite calls are successful? You should initialize result to nil so that your function returns nil if any errors are caught.

Three (probably related) issues with your code:

The index to sqlite3_column_text should be zero-based; you're passing 2, which should refer to the third column. You probably mean to pass 1. From the docs:

...the second argument is the index of the column for which information should be returned. The leftmost column of the result set has the index 0.
You really shouldn't use SELECT *. Specify the columns you want!
You should specialize your query by binding values, not by concatenating strings! Your code is rife with the possibility of SQL injections (not to mention incorrect queries).

For example (with no error checking):

const char *query = "SELECT * FROM ? WHERE ?=?";
sqlite3_stmt *compiledQuery;
sqlite3_prepare_v2(database, query, -1, &compiledQuery, NULL);
sqlite3_bind_text(compiledQuery, 1, "Lessons", -1, SQLITE_TRANSIENT);
sqlite3_bind_text(compiledQuery, 2, "Chrono", -1, SQLITE_TRANSIENT);
sqlite3_bind_text(compiledQuery, 3, "0001", -1, SQLITE_TRANSIENT);

Note that the index here is 1-based (I don't know why they do that). From the docs:

The second argument is the index of the SQL parameter to be set. The leftmost SQL parameter has an index of 1.

iPhone SQLite Database Reading And Writing

2 Answers