How to insert UIImage as blob using sqlite3_exec in objective-c

I think a large part of the issue you are running into here is that you are trying to simplify the SQLite3 APIs too much. The APIs are not just for executing textual SQL queries; prepared statements and bind parameters exist for a reason. You shouldn't be trying to insert binary data in a string. That's just asking for problems, especially if your binary data has nulls in it.

To insert blobs, you really do need to use sqlite3_bind_blob with sqlite3_prepare_v2. When you bind the blob, you will need to also use [imgData bytes] as the blob data.

Are you perhaps looking for help reconstructing your API to make this sort of thing easier for this particular image caching use case?

Edit

Here's a simple example using bind to insert binary data. Assume there is a table called my_table with 2 columns: name of type VARCHAR and data of type BLOB. Please note that I have not tested or even tried compiling this, so there may be typos or errors.

sqlite3 *database;// Open a connection to the database given its file path.if (sqlite3_open("/path/to/sqlite/database.sqlite3", &database) != SQLITE_OK) {    // error handling...}// Construct the query and empty prepared statement.const char *sql = "INSERT INTO `my_table` (`name`, `data`) VALUES (?, ?)";sqlite3_stmt *statement;// Prepare the data to bind.NSData *imageData = UIImagePNGRepresentation([UIImage imageNamed:@"something"]);NSString *nameParam = @"Some name";// Prepare the statement.if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL) == SQLITE_OK) {    // Bind the parameters (note that these use a 1-based index, not 0).    sqlite3_bind_text(statement, 1, nameParam);    sqlite3_bind_blob(statement, 2, [imageData bytes], [imageData length], SQLITE_STATIC);    // SQLITE_STATIC tells SQLite that it doesn't have to worry about freeing the binary data.}// Execute the statement.if (sqlite3_step(statement) != SQLITE_DONE) {    // error handling...}// Clean up and delete the resources used by the prepared statement.sqlite3_finalize(statement);// Now let's try to query! Just select the data column.const char *selectSql = "SELECT `data` FROM `my_table` WHERE `name` = ?";sqlite3_stmt *selectStatement;if (sqlite3_prepare_v2(database, selectSql, -1, &selectStatement, NULL) == SQLITE_OK) {    // Bind the name parameter.    sqlite3_bind_text(selectStatement, 1, nameParam);}// Execute the statement and iterate over all the resulting rows.while (sqlite3_step(selectStatement) == SQLITE_ROW) {    // We got a row back. Let's extract that BLOB.    // Notice the columns have 0-based indices here.    const void *blobBytes = sqlite3_column_blob(selectStatement, 0);    int blobBytesLength = sqlite3_column_bytes(selectStatement, 0); // Count the number of bytes in the BLOB.    NSData *blobData = [NSData dataWithBytes:blobBytes length:blobBytesLength];    NSLog("Here's that data!\n%@", blobData);}// Clean up the select statementsqlite3_finalize(selectStatement);// Close the connection to the database.sqlite3_close(database);