Advanced Issues (Managing and Using MySQL, 2nd Edition)

12.3. 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 from the second argument into that string. In other words, where the program read a null from the file, it will place the special sequence '\0' into the query string. Because each special character is replaced with two characters, we need to assume that each character will be escaped when checking whether the memory we have allocated for the query string can accept the data. The function finally adds a null to the end of your string to create a null-terminated string.

A more mundane use of this feature is to deal with the problem of arbitrary user input we discussed earlier in the chapter. You can pass user input through this function and get a string that is safe to stick in your query.