O'Reilly logo

Managing & Using MySQL, 2nd Edition by Hugh E. Williams, Randy Yarger, George Reese, Tim King

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required