Managing & Using MySQL, 2nd Edition

Advanced Issues

The MySQL C API provides the tools for manipulating queries and results using their lengths rather than null termination. This feature is useful if you have strings encoded with nulls in them.

char *sql = (char *)malloc(3246);
  
...
state = mysql_real_query(connection, sql, 3246);

This function behaves just like mysql_query( ) but requires you to specify the string length.

Of course, if you are intent on using MySQL to store binary data, you need to worry about a lot more than nulls in your query data. You need to worry about all of the special characters interpreted by MySQL. The mysql_escape_string( ) function is critical to getting around this concern.

The following example shows how to load an MP3 from a file in MySQL:

void add_mp3(char *song, FILE *f) {
    unsigned int read;
    char sql[1024000];
    char mp3[1024000];
    char *p;
  
    sprintf(sql, "INSERT INTO MP3 ( title, song ) VALUES ( '%s', '", song);
    p = sql + strlen(sql);
    while( (read = fread(mp3, 1, sizeof(mp3), f)) > 0 ) {
        if( (p + (2*read) + 3) > (sql + sizeof(sql)) ) {
            // reallocate memory
        }
        p += mysql_escape_string(p, mp3, read);
    }
    strcpy(p, "')");
    if( mysql_query(connection, sql) != 0 ) {
        printf("%s\n", mysql_error(connection));
    }
}

In this example, the application reads an MP3 from a file. Instead of placing it directly into the query string, however, it runs the binary data read from the file through the mysql_escape_string( ) function. This function takes a pointer to a position in a string and places escaped data ...