Parameterized SQLite queries in Objective-C

Question

I have a function that accepts 3 parameters, these are used to construct SQL queries that do various things to various tables in my database. I have tried converting the current code which just uses standard NSString replacement in the query strings but am having a problem following a couple of simple looking guides.

This is what I had before:

NSString *queryStrInsert = [NSString stringWithFormat:@"INSERT INTO %@ (%@) VALUES (%@)", tableName, columns, values];

This is what I now have:

NSString *queryStrInsert = [NSString stringWithFormat:@"INSERT INTO ? (?) VALUES (?)"];

const char *sqlInsert = [queryStrInsert UTF8String];
if ((sqlite3_open([[self filePath] UTF8String], &congressDB)==SQLITE_OK))
{
    if (sqlite3_prepare_v2(congressDB, sqlInsert, -1, &stmtInsert, NULL)==SQLITE_OK)
    {

        if(sqlite3_bind_text(stmtInsert, 0, [tableName UTF8String], -1, SQLITE_TRANSIENT) != SQLITE_OK)
        {
            NSLog(@"Binding 0 did not work");
        } 
        if(sqlite3_bind_text(stmtInsert, 1, [columns UTF8String], -1, SQLITE_TRANSIENT) != SQLITE_OK)
        {
            NSLog(@"Binding 1 did not work");
        } 
        if(sqlite3_bind_text(stmtInsert, 2, [values UTF8String], -1, SQLITE_TRANSIENT) != SQLITE_OK)
        {
            NSLog(@"Binding 2 did not work");
        }
    }
}

My code has problems at sqlite3_prepare_v2 (have also tried sqlite3_prepare) with:

near "?": syntax error

The 3 bind statements never fire. I have tried moving above the prepare but the SLITE_OK also returns false for each one.

The code worked fine before (without parameterised queries) but would like it to be more secure and to handle apostrophes etc.

Thanks.

EDIT:

It seems part of the problem is that table and column names cannot be paramterized only the values can.

Updated code:

NSString *queryStrInsert = [NSString stringWithFormat:@"INSERT INTO %@ (%@) VALUES (?)", tableName, columns];

[self openDB];

const char *sqlInsert = [queryStrInsert UTF8String];
if ((sqlite3_open([[self filePath] UTF8String], &congressDB)==SQLITE_OK))
{
    if (sqlite3_prepare_v2(congressDB, sqlInsert, -1, &stmtInsert, NULL)==SQLITE_OK)
    {
        if(sqlite3_bind_text(stmtInsert, 1, [values UTF8String], -1, SQLITE_TRANSIENT) != SQLITE_OK)
        {
            NSLog(@"Binding 2 did not work");
        } else {
            NSLog(@"Binding 2 worked - ,%@", stmtInsert);
        }
    }
}

This now gives error:

1 values for 6 columns

I think I have a much bigger problem here, I am trying to generate fairly dynamic SQL queries here and not have lots and lots of static insert queries. You can see from my original code I was trying to build a flexible query that can take different numbers of columns/values based on what table the query was being run against. I have effectively created a variable string for list of columns and their values but I'm fairly sure now that this approach will not work with parameters. For example the parameter for values in this case is:

'8ffed886-5f7c-e311-a863-000c29beef51','','','','1389623548','Rick Sanchez'

AS it's parameterised, the whole string gets treated as one value. Don't suppose there's a nice easy way of doing what I am trying here? If not I will have to revert to the original method and just carry out a basic NSString replace for ' to be '' etc...

Ah, I did suspect this and tried without doing so but seemed to have the same problem. I'll double check. — Agamemnon
– Agamemnon, Commented Jan 21, 2014 at 14:16
Nah, it's the same with NSString *queryStrInsert = [NSString stringWithFormat:@"INSERT INTO %@ (?) VALUES (?)", tableName]; — Agamemnon
– Agamemnon, Commented Jan 21, 2014 at 14:20
Is the database open for writing? Is the file at a writable location (i. e. outside the app bundle)? — user529758
– user529758, Commented Jan 21, 2014 at 14:24
Yeah the DB is opened just before this ([self openDB];) and this works fine if it's NOT parameterised. The only reason I noticed this was a problem is when I tried to enter a value that had apostrophes. THEN I tried to do things 'properly'. — Agamemnon
– Agamemnon, Commented Jan 21, 2014 at 14:28

score 3 · Accepted Answer · 2015-06-23 18:11:17Z

3

if ((sqlite3_open([[self filePath] UTF8String], &congressDB) == SQLITE_OK)) {
    if (sqlite3_prepare_v2(congressDB, sqlInsert, -1, &stmtInsert, NULL) == SQLITE_OK) {
        if (sqlite3_bind_text(stmtInsert, 1, [tableName UTF8String], -1, SQLITE_TRANSIENT) != SQLITE_OK) {
            NSLog(@"Binding 1 did not work");
        } 

        if (sqlite3_bind_text(stmtInsert, 2, [columns UTF8String], -1, SQLITE_TRANSIENT) != SQLITE_OK) {
            NSLog(@"Binding 2 did not work");
        } 

        if (sqlite3_bind_text(stmtInsert, 3, [values UTF8String], -1, SQLITE_TRANSIENT) != SQLITE_OK) {
            NSLog(@"Binding 3 did not work");
        }
    }
}

One: You can't parameterize table names or columns, only values.
Two: Start your bind index from 1

edited Jun 23, 2015 at 18:11

answered Feb 24, 2014 at 10:23

user2742371

Sign up to request clarification or add additional context in comments.

1 Comment

Agamemnon Over a year ago

Although this question was kind of answered in the comments, no one actually posted as an answer so to close this question off I will accept this answer. Thanks.

BGS · Accepted Answer · 2017-11-03 14:58:29Z

So you have table name, table columns names and values to insert, then here is how I would approach it:

-(void)insertSpecial:(NSString *)tblNm colNames:(NSArray *)colNms  colVals:(NSArray *)colVls{
sqlite3 *database;
if(sqlite3_open([pathToYourDbHere UTF8String], &database) == SQLITE_OK) {
    NSString *cols=@"";
    NSString *vals=@"";
        for(int i=0;i<colNms.count;i++){
            cols=[cols stringByAppendingString:[colNms objectAtIndex:i]];
            vals=[vals stringByAppendingString:@"?"];
            if(i<colNms.count-1) {
                vals=[vals stringByAppendingString:@","];
                cols=[cols stringByAppendingString:@","];
            }
        }
        const char *sqlStatement3=[[NSString stringWithFormat:@"INSERT INTO %@ (%@) VALUES(%@)",tblNm,cols,vals] UTF8String];
        sqlite3_stmt *compiledStatement3;
        if(sqlite3_prepare_v2(database, sqlStatement3, -1, &compiledStatement3, NULL) == SQLITE_OK) {
            for (int i=0; i<colVls.count; i++) {
                sqlite3_bind_text(compiledStatement3, i+1, [[colVls objectAtIndex:i] UTF8String], -1, SQLITE_TRANSIENT);
            }

            if(sqlite3_step(compiledStatement3) != SQLITE_DONE ) {
                NSLog( @"Error: ins records %s", sqlite3_errmsg(database) );
            }else{
                NSLog( @"Insert into records row id = %lld", sqlite3_last_insert_rowid(database));
            }
        }
        sqlite3_finalize(compiledStatement3);
    }

sqlite3_close(database);
database =nil;
}

Collectives™ on Stack Overflow

Parameterized SQLite queries in Objective-C

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related