Store binary data structure into BLOB columns in C

Question

I load a dictionary and made many manipulation in it. To save some CPU time, I actually store the result into a flat file for future use. How can I store that memory structure into a BLOB columns of MariaDB database (program in C)

He is my actual code et what an example of what I try to do.

//--- 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] ;

  //----------------------------------------------------------------
  // The way I dump the memory (structure) into a flat file actually
  //----------------------------------------------------------------
  fd = fopen("./mem_dump.binary","wb");
  fwrite(&st_dic, sizeof(struct Dictionary), 1, fd);
  fclose (fd);


  //----------------------------------------------------------------
  // The way I need to do it 
  // Insert the structure into a BLOB column
  //----------------------------------------------------------------
  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) ;
}

Answer 1

When working with binary objects text protocol (mysql_query/mysql_real_query) is not the best option, since special characters like '\0' are not supported. That means you have to allocate additional buffer (2 * (size of blob) + 1) for transforming the binary object.

Solution 1: mysql_real_escape()

char *buffer = malloc(sizeof(struct Dictionary) * 2 + 1);
mysql_real_escape_string(conn, buffer, &st_dic, sizeof(struct Dictionary));

sprintf(str, "INSERT INTO myTables (id, blob_field) VALUES (0, '%s')", buffer);
if (mysql_query(conn, str))
{
  /* Error handling */
}

Solution 2: mysql_hex_string()

char *buffer = malloc(sizeof(struct Dictionary) * 2 + 1);
mysql_hex_string(buffer, &st_dic, sizeof(struct Dictionary));
sprintf(str, "INSERT INTO myTables (id, blob_field) VALUES (0, X'%s')", buffer);
if (mysql_query(conn, str))
{
  /* Error handling */
}

Alternative: A better solution is to use prepared statements which use the binary protocol:

Code without error handling:

MYSQL_BIND bind;
MYSQL_STMT *stmt;

stmt= mysql_stmt_init(conn);
mysql_stmt_prepare(stmt, INSERT INTO myTables (id, blob_field) VALUES (0, ?)", 1);

memset(&bind, 0, sizeof(MYSQL_BIND));
bind.buffer_type= MYSQL_TYPE_BLOB;
bind.buffer= &st_dic;
bind.buffer_length= sizeof(struct Dictionary);

mysql_stmt_bind_param(stmt, &bind);
mysql_stmt_execute(stmt);