how to add escape character to string when init sql string?

Question

I am using FMDB to manage my sqlite database. I implement an insert function and use it to insert some rows. But there is a row which cannot be inserted, when inserted, the error code is 7 and the error message is out of memory.

I trace sql execution and find one string value has a ' character which may cause a wrong sql statement. So i wonder how to convert this character to insert it to database.

the sql string is NSString *sql = [NSString stringWithFormat:@"INSERT INTO table( COMPANY, PAGE_NO, RECORD_ID) VALUES('%@',%d,%d)",table.company,table.page_no,table.record_id]; and the console output is

<FMDatabase: 0x433e80> executeUpdate: INSERT INTO table(COMPANY, PAGE_NO, RECORD_ID) VALUES('Wendy's',-1,234)

COMPANY is a text and the corresponding value is Wendy's. the ' character may cause the error.

Thanks in advance.

score 2 · Accepted Answer · answered Apr 17 '13 at 16:19

2

Don't try to escape strings; just use parameters:

sql = [NSString stringWithFormat:
       @"INSERT INTO tbl(COMPANY, PAGE_NO, RECORD_ID) VALUES(?, %d, %d)",
       table.page_no, table.record_id];
sqlite3_prepare_v2(...);
sqlite3_bind_text(statement, 1, [table.company UTF8String], -1, SQLITE_TRANSIENT);

answered Apr 17 '13 at 16:19

CL.

173,858
17
217
259

Why do this half way? Get rid of the `stringWithFormat:` completely and use `sqlite3_bind_xxx` for all values, not just strings. – rmaddy Jan 04 '15 at 01:36

how to add escape character to string when init sql string?

1 Answers1