How to save & retrieve NSdata into sqlite through FMDB

Question

How can I save NSData into sqlite, I am using FMDB wrapper for saving data.

Below is the code which I have tried so far
For saving

NSData *data = [NSKeyedArchiver archivedDataWithRootObject:model.expertArray];; 
NSString *query = [NSString stringWithFormat:@"insert into save_article values ('%@','%@','%@','%@','%@','%@')",
                       model.Id, model.title, model.earliestKnownDate, data, model.excerpt,model.image_url];

For Retriving

while([results next]) {
  NSData *data = [results dataForColumn:@"experts"];
        NSMutableArray *photoArray = [NSKeyedUnarchiver unarchiveObjectWithData:data];
 }

I have tried both datatype blob & text but no luck so far, can anybody guide me how to do it?

Generally NSData should be stored as a blob. And, with a blob you pretty much must use bound parameters. Otherwise the (non-ASCII) blob data will cause a corrupted insert statement to be generated. — Hot Licks, Mar 06 '14 at 19:48
(BTW, if you're building your statements that way you're not really using FMDB, you'll only working around it.) — Hot Licks, Mar 06 '14 at 19:50
You must use bound parameters. Simply inserting a blob with `stringWithFormat` results in a corrupted SQL statement. — Hot Licks, Mar 06 '14 at 19:51

score 15 · Accepted Answer · edited Sep 10 '16 at 11:34

Below is the Snippet for all who may face the same issue while inserting NSData to Sqlite using FMDB.

In my Case I wanted to store NSArray in Sqlite So i first convert the NSArray into NSData & then store it in Sqlite.

Converting NSArray into NSData

NSData *data = [NSKeyedArchiver archivedDataWithRootObject:YourNSarray];;

Saving NSData into Sqlite

[database executeQuery:@"insert into save_article values (?,?,?,?,?,?)", model.Id, model.title, model.earliestKnownDate, data, model.excerpt,model.image_url];

Note:- Don't build a query using stringWithFormat[below is the code which you should not use]:. Thanks to @rmaddy & @hotlicks for pointing me to this :)

NSString *query = [NSString stringWithFormat:@"insert into user values ('%@', %d)",
@"brandontreb", 25];
[database executeUpdate:query];

and now the last step i.e retrieving NSData back to NSArray

NSArray *array = [NSKeyedUnarchiver unarchiveObjectWithData:[database dataForColumn:@"yourcololumname"]];

Hope this will help the needy & beginner :)

@DAMM108 I have tried this but it is not working for me even i have the data type blob. Is there any more clarification you can give ? Also, dont you think query should be like this Insert into user (coulmn1, 2,3) values(?,?,?)" ... — MQ., Apr 17 '15 at 19:15

score 6 · Answer 2 · answered Mar 06 '14 at 19:45

6

Don't build a query using stringWithFormat:. This is a bad idea for several reasons.

Use the executeQuery method where you put a ? for each value to be bound to the query.

Something like this:

[database executeQuery:@"insert into save_article values (?,?,?,?,?,?)", model.Id, model.title, model.earliestKnownDate, data, model.excerpt,model.image_url];

answered Mar 06 '14 at 19:45

rmaddy

314,917
42
532
579

which datatype should i use then blob or text – DAMM108 Mar 06 '14 at 19:48
Use a blob for `NSData`. – rmaddy Mar 06 '14 at 19:50
@rmaddy, can you please elaborate the reasons for not using stringWithFormat? – Eric Chuang Mar 05 '15 at 08:45
1

@EricChuang The problem is that if you use `stringWithFormat:`, the values may contain special characters that aren't properly escaped. This leads to bugs and crashes. It also can lead to SQL injection attacks (search on that term if you don't know what that is). By properly binding values you eliminate all of these issues. – rmaddy Mar 05 '15 at 15:06
@rmaddy i faced same issue for stringWithFormat: while inserting string like ex. That's. this ' is creating issue while inserting how to fix that? – Avijit Nagare Dec 16 '15 at 07:36
the `'` needs to be escaped, in sqlite it's escaped with an apostrophe, so it ends up looking like this: `''` (2 apostrophes not a double quote) or you can use SQLite's built in formatters: `char *result = sqlite3_vmprintf("%Q", variadic params);`. `%q` and `%Q` are likely what you want. sqlite has a few different sqlite3_*printf versions. As others noted, it's safer to just use statement binding then you don't need to worry about it. https://www.sqlite.org/c3ref/mprintf.html – AJ Venturella Jan 20 '16 at 12:15
So if I used to build the query like in your example, I have to pass the values in the order they are in table? @rmaddy – Hemang Sep 10 '16 at 11:37
1

@Hemang The best solution is to list the column names in the SQL: `INSERT INTO table_name (col1, col2, col3) VALUES (?, ?, ?)`. Then the values should be passed in the order listed in the `INSERT` statement. – rmaddy Sep 10 '16 at 14:55
executeUpdate, not executeQuery – Andrey Chernukha Nov 01 '18 at 18:11

How to save & retrieve NSdata into sqlite through FMDB

2 Answers2

Linked