mysql_real_escape_string including slashes in output (C, not PHP)

Question

I've seen this question several times relating to PHP (here is an example). The answer was generally 'stop using magic quotes'. I am having this problem in C however. When I insert binary data into a BLOB in my MySQL database, having run it through mysql_real_escape_string(), some 5c ('\') characters appear in the blob. This disrupts the data and makes it unusable. How can I prevent / fix this?

#define CHUNK_SZ        (1024*256)
void insertdb(int16_t *data, size_t size, size_t nmemb)
{   
    static int16_t *buf; 
    static unsigned long index;
    static short initialized;
    unsigned long i;
    struct tm *info;
    time_t rawtime;
    char dbuf[12];
    char tbuf[12];
    char *chunk;

    if(initialized==0){
        buf = (int16_t *) malloc(CHUNK_SZ);
        initialized = 1;
    }

    if(index + (nmemb*size) + 1 >= CHUNK_SZ || do_exit == 1){
        time(&rawtime);
        info = localtime(&rawtime);
        snprintf(dbuf, 16, "%d-%02d-%02d", 1900+info->tm_year, 1+info->tm_mon, info->tm_mday);
        snprintf(tbuf, 16, "%02d:%02d:%02d", info->tm_hour, info->tm_min, info->tm_sec);

        chunk = (char *) malloc(index*2+1);

        mysql_real_escape_string(con, chunk, (char *) buf, index);
        char *st = "INSERT INTO %s (date, time, tag, data) VALUES ('%s', '%s', %d, '%s')";
        int len = strlen(st)+strlen(db_mon_table)+strlen(dbuf)+strlen(tbuf)+sizeof(tag)+index*2+1;
        char *query = (char *) malloc(len); 
        int qlen = snprintf(query, len, st, our_table, dbuf, tbuf, tag, chunk);

        if(mysql_real_query(con, query, qlen)){
            fprintf(stderr, "%s\n", mysql_error(con));
            mysql_close(con);
            exit(1);
        }

        free(chunk);
        index = 0;

    } else {
        memcpy((void *) buf+index, (void *) data, nmemb*size);
        index += (nmemb*size);
    }

    return;
}

EDIT: Please look here. They use the same function to escape binary data (from an image), insert it, and afterward get the same image from the database. That my binary data is somehow different from an image's binary data makes no sense to me.

score 0 · Answer 1 · answered Dec 22 '14 at 20:08

0

If you're inserting into a BLOB column, then instead of escaping the data via mysql_real_escape_string(), you should probably express it as a HEX string. You will have to figure out how to encode your int16_t data into the needed byte sequence, as at minimum you have a byte-order question to sort out (but if you're in control of both encoding and decoding then you just need to make them match).

Alternatively, if the data are genuinely textual, rather than binary, then the type of the column should probably be Text rather than BLOB. In that case, you should continue to use an ordinary SQL string and mysql_real_escape_string().

answered Dec 22 '14 at 20:08

John Bollinger

160,171
8
81
157

It's a blob, and it's binary data. The thing is, the demonstrations online that show how to do this generally deal with binary image files, and use mysql_real_escape_string() to do it :\ I don't know why it gives me problems, but not them. – Ken - Enough about Monica Dec 22 '14 at 20:12
Well, depending on your data and on your connection's character set, your binary data might not even constitute a valid string. Moreover, if your connection's character set differs from the server's/database's/table's configured character set, then you should get a transformation of the data that depends on the configured source and destination character sets. Sometimes you can get away with it, but it is never a good idea to pretend that raw binary data is really character data. – John Bollinger Dec 22 '14 at 20:28

mysql_real_escape_string including slashes in output (C, not PHP)

1 Answers1