SQLite3: Insert BLOB with NULL characters in C++

Question

Hi everyone,

I'm working on the development of a C++ API which uses custom-designed plugins to interface with different database engines using their APIs and specific SQL syntax.

Currently, I'm attempting to find a way of inserting BLOBs, but since NULL is the terminating character in C/C++, the BLOB becomes truncated when constructing the INSERT INTO query string. So far, I've worked with

...
char* sql;
void* blob;
int len;
...
blob = some_blob_already_in_memory;
len = length_of_blob_already_known;
sql = sqlite3_malloc(2*len+1);
sql = sqlite3_mprintf("INSERT INTO table VALUES (%Q)", (char*)blob);
...

I expect that, if it is at all possible to do it in the SQLite3 interactive console, it should be possible to construct the query string with properly escaped NULL characters. Maybe there's a way to do this with standard SQL which is also supported by SQLite SQL syntax?

Surely someone must have faced the same situation before. I've googled and found some answers but were in other programming languages (Python).

Thank you in advance for your feedback.

Answer 1

This page seems to list all the functions you need.

Answer 2

You want to precompile the statement sqlite_prepare_v2(), and then bind the blob in using sqlite3_bind_blob(). Note that the statement you bind in will be "INSERT INTO table VALUES (?)"

Answer 3

You'll want to use this function with a prepared statement.

int sqlite3_bind_blob(sqlite3_stmt*, int, const void*, int n, void(*)(void*));

In C/C++, the standard way of dealing with NULLs in strings is to either store the beginning of the string and a length, or store a pointer to the beginning of a string and one to the end of the string.