Replace table name in updateWithFormat?

[hellomaya]

NSString *sql = @"INSERT INTO %@ (name) VALUES (%@)";

NSString table = @"tag"; NSString name = @"cool-game";

[db executeUpdateFormat:sql, table, name];

Seems not working, if we can replace table name too, will be easier for us to re-use this code. What you think?

[robertmryan]

@hellomaya

You can do precisely what you ask. The problem with your SQL is not the table name, but rather the value you're inserting. Your code will generate SQL that looks like:

INSERT INTO tag (name) VALUES (cool-game)

The immediate problem is probably obvious, that cool-game is not inside quotes. (If you looked at [db lastErrorMessage], it would probably provide an informative error message that would have helped you diagnose this problem.)

Anyway, you could try:

NSString *sql = @"INSERT INTO %@ (name) VALUES ('%@')";  // note the quotes

NSString *table = @"tag";
NSString *name = @"cool-game";

[db executeUpdateFormat:sql, table, name];

I think you'd find that works.

The deeper problem, though, is that you now suffer from the risk of the name value might contain an apostrophe. Worse, you're theoretically exposed to SQL injection attacks. This is the reason why we discourage the use of executeUpdateFormat. So, personally, I'd suggest:

NSString *table = @"tag";
NSString *name = @"cool-game";

NSString *sql = [NSString stringWithFormat:@"INSERT INTO %@ (name) VALUES (?)", table];

[db executeUpdate:sql, name];

This enjoys the robust behavior of parameterized query values, but you can dynamically build your SQL, too. I know this doesn't look as elegant as my first code sample, but it's much safer and more robust.

[hellomaya]

good point, thank you very much! That's a nice and elegant solution, though I did use the first one you mentioned. :+1:

[liufsd]

If i create a table name like this :"burnett-secret-garden-illustrations" ,it will not working. log here: error:Error Domain=FMDatabase Code=1 "near "-": syntax error" UserInfo=0xca66310 {NSLocalizedDescription=near "-": syntax error}

How to fix it ?

[robertmryan]

@79144876 , As I mentioned in your other issue (https://github.com/ccgus/fmdb/issues/242), if your table name has - character in it, surround the table name with double quotes. Thus, using my example above, if the table name included a - you might use the escaped double quote:

NSString *table = @"tag-entry";
NSString *name = @"cool-game";

NSString *sql = [NSString stringWithFormat:@"INSERT INTO \"%@\" (name) VALUES (?)", table];

[db executeUpdate:sql, name];

In this case, the SQL will end up being:

INSERT INTO "tag-entry" (name) VALUES (?)

And this avoids the problem associated with having a hyphen in the table name.

Note, you don't have to do anything with the name value, because we're binding it with executeUpdate (which uses the sqlite3_bind_text() function behind the scenes), the hyphen and any other reserved characters is handled seamlessly for us). It's why executeUpdate with ? placeholders is so much better than executeUpdateFormat with printf-style sequences.

[liufsd]

I got it. I replace the table name . Thanks.

[nonstriater]

when the tablename as varible in UPDATE statement, It doesnot work. https://github.com/ccgus/fmdb/issues/287 please help me!