如何用C语言将内存字典结构存入MariaDB的BLOB列？

问题背景 #

我加载了一个字典并对其执行了大量操作，为节省CPU时间，目前会将结果存入平面文件供后续使用。现需使用C语言将该内存结构存入MariaDB数据库的BLOB列，以下是我的现有尝试代码：

//--- Global variables and CONST for databases
#define DB_NAME   "some_name"
#define DB_USER   "admin"
#define DB_PWD    "qwerty"
#define DB_SERVER "localhost"

MYSQL *conn;
MYSQL_RES *res;
MYSQL_ROW row;

int main (int argc, char **argv)
{
  char str_query [2048] ;

  //----------------------------------------------------------------
  // 原有将内存结构写入平面文件的代码
  //----------------------------------------------------------------
  fd = fopen("./mem_dump.binary","wb");
  fwrite(&st_dic, sizeof(struct Dictionary), 1, fd);
  fclose (fd);


  //----------------------------------------------------------------
  // 尝试插入BLOB列的代码（存在问题）
  //----------------------------------------------------------------
  conn = mysql_init(NULL);
  if (!mysql_real_connect(conn, DB_SERVER, DB_USER, DB_PWD, DB_NAME, 0, NULL, 0))
  {
    fprintf(stderr, "ERROR:%s\n", mysql_error(conn));
    exit (1) ;
  }

  //....
  sprintf (str_query, "INSERT INTO myTables (id, blob_field) VALUES (0, '%s')", &st_dic) ;
  //....

  if (mysql_query(conn, str_query))
  {
    fprintf(stderr,"FAIL TO RUN SQL : [%s]\n", str_query) ;
    fprintf(stderr, "%s\n", mysql_error(conn));
  }

  mysql_free_result(res);
  mysql_close(conn);

  return (0) ;
}

问题分析 #

直接用sprintf将二进制结构体拼接成SQL语句是错误的：

• 结构体中的二进制数据可能包含单引号、反斜杠、空字符等特殊字符，会破坏SQL语法结构，导致执行失败。
• 这种方式存在SQL注入风险，即使是内部程序也可能因数据异常引发安全问题。

正确解决方案 #

使用MySQL C API的**预处理语句（Prepared Statements）**来安全处理BLOB数据，步骤如下：

1. 准备预处理语句 #

创建预处理语句句柄，定义带占位符?的SQL插入语句。

2. 绑定参数 #

通过MYSQL_BIND结构体绑定参数，明确指定数据类型、缓冲区地址和长度。

3. 执行语句 #

执行预处理语句完成数据插入，自动处理特殊字符转义。

完整修正代码 #

//--- Global variables and CONST for databases
#define DB_NAME   "some_name"
#define DB_USER   "admin"
#define DB_PWD    "qwerty"
#define DB_SERVER "localhost"

MYSQL *conn;
MYSQL_STMT *stmt;
MYSQL_BIND bind_params[2];

// 假设这是你的Dictionary结构体定义，根据实际情况补充
typedef struct Dictionary {
    int version;
    char data[512];
    unsigned int count;
} Dictionary;

Dictionary st_dic; // 已填充好数据的字典结构

int main (int argc, char **argv)
{
    int insert_id = 0;
    unsigned long blob_length = sizeof(Dictionary);
    FILE *fd;

    // 原有写入平面文件的逻辑（保留）
    fd = fopen("./mem_dump.binary","wb");
    if (fd) {
        fwrite(&st_dic, sizeof(Dictionary), 1, fd);
        fclose(fd);
    }

    // 初始化数据库连接
    conn = mysql_init(NULL);
    if (!mysql_real_connect(conn, DB_SERVER, DB_USER, DB_PWD, DB_NAME, 0, NULL, 0))
    {
        fprintf(stderr, "数据库连接失败:%s\n", mysql_error(conn));
        exit(1);
    }

    // 1. 初始化并准备预处理语句
    const char *insert_sql = "INSERT INTO myTables (id, blob_field) VALUES (?, ?)";
    stmt = mysql_stmt_init(conn);
    if (!stmt || mysql_stmt_prepare(stmt, insert_sql, strlen(insert_sql))) {
        fprintf(stderr, "预处理语句准备失败: %s\n", mysql_error(conn));
        mysql_close(conn);
        exit(1);
    }

    // 2. 绑定参数到预处理语句
    memset(bind_params, 0, sizeof(bind_params));

    // 绑定id参数（整数类型）
    bind_params[0].buffer_type = MYSQL_TYPE_LONG;
    bind_params[0].buffer = &insert_id;
    bind_params[0].is_null = 0;

    // 绑定BLOB参数（二进制类型）
    bind_params[1].buffer_type = MYSQL_TYPE_BLOB;
    bind_params[1].buffer = &st_dic;
    bind_params[1].buffer_length = blob_length;
    bind_params[1].is_null = 0;
    bind_params[1].length = &blob_length;

    if (mysql_stmt_bind_param(stmt, bind_params)) {
        fprintf(stderr, "参数绑定失败: %s\n", mysql_stmt_error(stmt));
        mysql_stmt_close(stmt);
        mysql_close(conn);
        exit(1);
    }

    // 3. 执行预处理语句
    if (mysql_stmt_execute(stmt)) {
        fprintf(stderr, "数据插入失败: %s\n", mysql_stmt_error(stmt));
    } else {
        printf("数据插入成功，影响行数: %lu\n", mysql_stmt_affected_rows(stmt));
    }

    // 清理资源
    mysql_stmt_close(stmt);
    mysql_close(conn);

    return 0;
}

关键注意事项 #

• 结构体可移植性：直接存储结构体到BLOB依赖平台的字节序和内存对齐规则。如果后续需要跨架构读取数据，建议将结构体序列化为平台无关格式（如JSON、Protobuf）后再存入。
• 资源清理：确保数据库连接、预处理语句等资源被正确关闭，避免内存泄漏。
• 错误处理：示例中添加了关键步骤的错误检查，实际项目中可根据需求扩展异常处理逻辑。

内容的提问来源于stack exchange，提问作者Eric Bernier