Home
  Software
    HowTo
      MySQL
        Intro

Language:
  Espaņol

How to store any binary data into a MySQL table

The problem

MySQL requires programs to send in their requests in a special language. This language (SQL) is a humanly understandable language, which means that if the human is versed in that language, then he or she can write the requests. The requests are written in a fairly natural sounding (English based) prose.

This works fine in most cases, when the data we manipulate are also near 'normal' language, i.e. using normal characters. Images, or program code, or just random data do not respect such the 'natural language' restriction and can contain any binary value. This wreaks havoc on the requests we send to MySQL, and the conversation (remember - English bases), relies on a restricted set of characters.

Several of the binary values which do appear in binary data, confuse the SQL requests. One such dangerous character is the binary value 0. This never occurs in natural language (not even in the international character sets (unicode). So, if we need to write binary data, with a 'natural' language SQL statement, we have to mark these dangerous characters as such.

The trick MySQL uses is to prepend the dangerous value with a backslash ('\'). So, when we send a write command to MySQL, the database engine knows that after the '\' there's a dangerous character. It then discards the '\' character, and saves the dangerous character without interpreting it at all. The '\' is called an escape character.

Note that this makes the backslash itself somewhat un'natural'! This is why we have to add a '\' before the '\' itself if we actually need to save a backslash.

The solution

Of course we don't have to do this manually. The MySQL API (library) provides us with a special function mysql_real_escape_string() which takes anything binary, and converts it into a string which can be safely included in an SQL statement.

Mind, it doesn't make it humanly readable. It will still look like a jumble to characters to us, but at least MySQL will be able to accept it and, after removing the escape characters, store it correctly in the table.

A simple example

In the next section, there's a - relatively - simple example which shows how to save block of binary data into a table. In this case, it stores an array of (random) 16-bit integers. But, just as easily, this could be an image, or sound samples, or floating point values. To the program, it just saves a number of bytes - no matter what they represent.

To show what happens, here's the sequence of events - just for the first few bytes, which happen to contain a backslash which needs to be escaped itself.

The integers are in an array. If I look at it as integers, I get:

17767 9158 -26519 18547 -9135 23807 -27574 22764 7977 31949 22714 -10325 16882 7931 -22045 -7866 124 25282 2132 10232 8987 -5656 -12825 17293

Looking at the same array as bytes (or characters), I see:

67 45 C6 23 69 98 73 48 51 dc ff 5c 4a 94 ec 58 29 1f cd 7c ba 58 ab d7

(The 67 45 is the first int value, 17767)

I can't insert this in a query, as byte 11 is 0x5C (a backslash), which would confuse the query string:

67 45 C6 23 69 98 73 48 51 dc ff 5c 4a 94 ec 58 29 1f cd 7c ba 58 ab d7

So, I call the mysql_real_escape_string() function, and use the result to form the query:

67 45 c6 23 69 98 73 48 51 dc ff 5c 5c 4a 94 ec 58 29 1f cd 7c ba 58 ab d7

Notice the extra 0x5c? The first 5c is inserted, to warn MySQL not to interpret the second one as an escape character.

Now have a look at the code below - particularly the ints_saved() and int_loaded() functions which do the important work.

The example

/* -*- Mode: C; indent-tabs-mode: t; c-basic-offset: 8; tab-width: 8 -*-  */
/*
 * mysql_blobs.c
 * Copyright (C) 2014 John Coppens <john@jcoppens.com>
 * 
 * blob_test is free software: you can redistribute it and/or modify it
 * under the terms of the GNU General Public License as published by the
 * Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 * 
 * blob_test is distributed in the hope that it will be useful, but
 * WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
 * See the GNU General Public License for more details.
 * 
 * You should have received a copy of the GNU General Public License along
 * with this program.  If not, see <http://www.gnu.org/licenses/>.
 */

#include <stdio.h>
#include <stdint.h>
#include <stdlib.h>
#include <string.h>
#include <mysql/mysql.h>

#define SIZE 	20000
#define USER    "user"
#define PASS    "pass"
#define DBASE   "localdata"

int16_t ints[SIZE],	     // Array of int16_t's to be written to a table 
        ints_read[SIZE];     // Space to read back the array from the table

MYSQL *con;

int
dbase_connected(void)	   // Connect to the database. Return TRUE if connected
{
  con = mysql_init(NULL);
  if (mysql_real_connect(con, "localhost", USER, PASS, DBASE, 
		0, NULL, CLIENT_MULTI_STATEMENTS) == NULL) {
    printf("%s\n", mysql_error(con));
    return(0);
  }
  
  return(1);
}

void
fill_ints(void)		// Fill the ints array with random 16-bit values
{
  int i;
  
  for (i = 0; i < SIZE; i++)
    ints[i] = random() & 0xffff;
}

int
arrays_equal(void)      // Compare ints and ints_read. Return TRUE if they
			// are completely equal.
{
  int i;
  
  for (i = 0; i < SIZE; i++)
    if (ints[i] != ints_read[i]) {
      printf("At %d: %04x <-> %04x\n", i, ints[i], ints_read[i]);
      return(0);
    }
  return(1);
}

void
show_hex(char *str)     // Show a (NULL-terminated) string as hex bytes
{
  int i;
  
  for (i = 0; str[i] != 0; i++) {
    if (i % 24 == 0)
      printf("\n");
    printf("%02x ", (unsigned char)str[i]);
  }
}

void
show_int(void)	      // Show the ints array (completely) as decimal numbers
{
  int i;
  
  for (i = 0; i < SIZE; i++) {
    if (i % 12 == 0)
      printf("\n");
    printf("%8d ", ints[i]);
  }
}

int
ints_saved(void)	// Store the ints array as a blob into the Ints
			// table. The Id field is always set to 1. Could be better
{
  char   *chunk, *st, *query;
  size_t ints_size = sizeof(ints), 
         st_len;
  int qlen;
  
  chunk = (char *)malloc(ints_size*2 + 1);
  st = "insert into Ints(Id, Data) values(1, '%s')";
  st_len = strlen(st);
  mysql_real_escape_string(con, chunk, (char *)ints, ints_size);
				// Note we have to call the escape function

  query = (char *)malloc(st_len + 2*ints_size+1); 
  qlen = snprintf(query, st_len + 2*ints_size+1, st, chunk);

  //printf("%s\n", query);      // Uncomment to print the query
  //show_hex(chunk);		// Uncomment to show the escaped chunk as hex
  show_int();			// Uncomment to show the ints array as integers
  if (mysql_real_query(con, query, qlen)) {
    printf("%s\n", mysql_error(con));
    return(0);
  };
  
  return(1);
}

int
ints_loaded(void)       // Read the integers back from the table, now into the
			// ints_read array.
{
  MYSQL_RES *result;
  
  if (mysql_query(con, "SELECT Data FROM Ints WHERE Id=1")) {
    printf("%s\n", mysql_error(con));
    return(0);
  }
  
  result = mysql_store_result(con);
  if (result == NULL) {
    printf("%s\n", mysql_error(con));
    return(0);
  }  

  MYSQL_ROW row = mysql_fetch_row(result);
  
  memcpy(ints_read, row[0], sizeof(ints));
  return(1);
}


int main()
{
  if (!dbase_connected())
    return(1);  // Can't connect
    
  fill_ints();
  if (!ints_saved())
    return(2);  // Can't save array as record
    
  if (!ints_loaded())
    return(3);  // Can't load array back
    
  if (!arrays_equal())
    return(4);  // Arrays are not equal
      
  return (0);
}
1929
(c) John Coppens ON6JC/LW3HAZ mail