C SQLite 在绑定(bind)参数时插入 BLOB 而不是 String，反之亦然

Question

所以我在使用 C 中的 SQLite 进行参数绑定(bind)时遇到了一些问题。我正在使用 sqlite3_bind_* 函数将 BLOB 和字符串插入数据库。然而，在插入之后，我用 SQLiteBrowser 检查数据库，令我惊讶的是类型都乱七八糟!下面是一些应该重现效果的示例代码。

1. 这个 block 创建表。

   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);
   

2. 既然已经完成，我将给您插入示例。

   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);
   

现在，如果你想用 SQLiteBrowser 或你可能拥有的任何类似工具查看数据库，假设你和我一样幸运，你会看到 SERVICE 列包含一个 BLOB，SYM_ENC_KEY 列包含一个字符串，尽管我使用了相反的 sqlite3_bind_* 函数。有谁知道这是怎么发生的？如果您需要更多信息，请询问。我是新海报。

Answer 1

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

零终止符不被视为字符串数据的一部分。删除 + 1，或者更好，只给 -1。

最后一个参数错误；您必须提供析构函数，或 SQLITE_TRANSIENT 或 SQLITE_STATIC。

(_blob 调用有同样的问题。)

---

但是，命令行 shell 中 .dump 命令的输出包含以下内容:

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

这是正确的。不存在数据类型问题。