C SQLite Inserts BLOB instead of String and Vice Versa When Binding Parameters

Question

So I'm having some trouble with parameter binding with SQLite in C. I am using sqlite3_bind_* functions to insert BLOBs and strings into a database. After an insertion however, I inspect the database with SQLiteBrowser and find to my surprise that the types are all jumbled up! Here is some sample code that should reproduce the effect.

This chunk creates the table.

const char *TABLE_NAME = "PASSWORD_ENTRY";
const char *USER_ID_COLUMN_NAME = "USER_ID";
const char *INDEX_COLUMN_NAME = "INDEX_VALUE";
const char *SERVICE_COLUMN_NAME = "SERVICE";
const char *SYM_ENC_KEY_COLUMN_NAME = "SYM_ENC_KEY";
const char *ASYM_ENC_KEY_COLUMN_NAME = "ASYM_ENC_KEY";
const char *TIMESTAMP_COLUMN_NAME = "TIMESTAMP";

/* CREATE TABLE IF NOT EXISTS TABLE_NAME (
        USER_ID_COLUMN_NAME INTEGER, 
        INDEX_COLUMN_NAME INTEGER, 
        SERVICE_COLUMN_NAME TEXT, 
        SYM_ENC_KEY_COLUMN_NAME BLOB, 
        ASYM_ENC_KEY_COLUMN_NAME BLOB,
        TIME_STAMP_COLUMN_NAME BLOB, 
        PRIMARY KEY (USER_ID_COLUMN_NAME, INDEX_COLUMN_NAME)); 
*/

char *f = "CREATE TABLE IF NOT EXISTS %s (%s INTEGER, %s INTEGER, %s TEXT, %s BLOB, %s BLOB, %s BLOB, PRIMARY KEY (%s, %s));";

char *s = malloc(snprintf(NULL, 0, f, TABLE_NAME, USER_ID_COLUMN_NAME, INDEX_COLUMN_NAME, SERVICE_COLUMN_NAME, SYM_ENC_KEY_COLUMN_NAME, ASYM_ENC_KEY_COLUMN_NAME, TIMESTAMP_COLUMN_NAME, USER_ID_COLUMN_NAME, INDEX_COLUMN_NAME) + 1);

sprintf(s, f, TABLE_NAME, USER_ID_COLUMN_NAME, INDEX_COLUMN_NAME, SERVICE_COLUMN_NAME, SYM_ENC_KEY_COLUMN_NAME, ASYM_ENC_KEY_COLUMN_NAME, TIMESTAMP_COLUMN_NAME, USER_ID_COLUMN_NAME, INDEX_COLUMN_NAME);

const char *DB_NAME = "passwordmanager.db";

sqlite3* db;
int r = 0;

// Get the database
r = sqlite3_open(DB_NAME, &db);
if (r) {
        printf("Error opening database: %s\n", sqlite3_errmsg(db));
        return NULL;
}
printf("Database opened.\n");

r = sqlite3_prepare_v2(db, sql, strlen(sql), &stmt, NULL);
if (r) {
        printf("Error preparing create table statement: %s\n", sqlite3_errmsg(db));
        return 1;
}

r = sqlite3_step(stmt);
if (r != 101 && r) {
        printf("Error executing create table statement: %s\n", sqlite3_errmsg(db));
        return 1;
}
printf("Password entry table ready.\n");
sqlite3_finalize(stmt);

Now that that's done, I'll give you a sample insertion.

sqlite3_stmt *stmt2;
long userId = 50l;
short index = 2;
long timestamp = 100l;
char *service = "stackoverflow.com";
const int SYM_ENC_KEY_LEN = 10;
const int ASYM_ENC_KEY_LEN = 11;
char *symEncKey = "symEncKey";
char *asymEncKey = "asymEncKey";

char *f = "INSERT INTO PASSWORD_ENTRY (USER_ID, INDEX_VALUE, SERVICE, TIMESTAMP, SYM_ENC_KEY, ASYM_ENC_KEY) VALUES (?, ?, ?, ?, ?, ?);";

printf("SQL ready.\n");

r = sqlite3_prepare_v2(db, f, strlen(f), &stmt2, NULL);
if (r != 0) {
        printf("Error preparing addition statement: %s\n", sqlite3_errmsg(db));
        sqlite3_finalize(stmt2);
        sqlite3_close(db);
        return;
}
printf("Prepared the addition statement, binding...\n");

sqlite3_bind_int64(stmt2, 1, (sqlite3_int64) userId);
sqlite3_bind_int(stmt2, 2, (int) index);
sqlite3_bind_text(stmt2, 3, service, strlen(service) + 1, 0);
sqlite3_bind_int64(stmt2, 4, (sqlite_int64) timestamp);
sqlite3_bind_blob(stmt2, 5, (void *) symEncKey, SYM_ENC_KEY_LEN, 0);
sqlite3_bind_blob(stmt2, 6, (void *) asymEncKey, ASYM_ENC_KEY_LEN, 0);

// Execute the statement
r = sqlite3_step(stmt2);
if (r != 101) {
        printf("Error executing addition statement: %s\n", sqlite3_errmsg(db));
        sqlite3_finalize(stmt2);
        sqlite3_close(db);
        return;
}
printf("Executed the addition statement.\n");

sqlite3_finalize(stmt2);
sqlite3_close(db);

Now, if you'd care to view the database with SQLiteBrowser or any similar tool you may have, provided you have the same luck as me, you'll see that column SERVICE contains a BLOB and the SYM_ENC_KEY column contains a string, regardless of the fact that I used the opposite sqlite3_bind_* functions. Does anyone have any idea as to how this could be happening? If you need more information, please ask. I am a new poster.

This code does not compile.

CL.
– CL.

2014-08-19 07:10:17 +00:00
Commented Aug 19, 2014 at 7:10 — CL.
– CL., Commented Aug 19, 2014 at 7:10

CL. · Accepted Answer · 2014-08-19 07:13:27Z

2

sqlite3_bind_text(stmt2, 3, service, strlen(service) + 1, 0);

The zero terminator is not considered part of the string's data. Remove the + 1, or better, just give -1.

The last parameter is wrong; you must provide a destructor function, or SQLITE_TRANSIENT or SQLITE_STATIC.

(The _blob calls have the same problems.)

However, the output of the .dump command in the command-line shell contains this:

INSERT INTO "PASSWORD_ENTRY" VALUES(50,2,'stackoverflow.com',X'73796D456E634B657900',X'6173796D456E634B657900',100);

This is correct. There is no data type problem.

edited Aug 19, 2014 at 7:13

answered Aug 19, 2014 at 7:06

CL.

182k18 gold badges241 silver badges282 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

MawrCoffeePls Over a year ago

Thanks for getting back so quickly! I made the changes you specified, and things seem to be working correctly with the SERVICE_NAME_COLUMN, however according to SQLiteBrowser the SYM_ENC_KEY column is still storing a string. Maybe this is just an error in SQLiteBrowser since you were able to use the command line tool to examine the statement, and the correct types were being inserted. Anyway, I will accept your answer, and thanks for the help!

Collectives™ on Stack Overflow

C SQLite Inserts BLOB instead of String and Vice Versa When Binding Parameters

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related