C++, mysql, and encryption... something not right.

[shadedecho]

so, I have the need to do the following: from a c++ program, i need to take a string inputted (on the command line for instance), encrypt it (i chose 3DES as my algorithm), and stick the encrypted value (and the randomly generated string key used in the encryption) in a mysql DB table.

Then, either in the same program or another one, retrieve the value (and key) and unencrypt it. I have a lot of this working, but if I run the program about 200 times, it fails about 2-5 times, so the 1-3% error rate is what I'm trying to track down and eliminate, as this application must be completely free from failures.

Even though there is some encryption stuff going on here, I have verified the problem is *not* with that code, but with the escaping/insertion/retrieval of the strings in the mysql calls.

this is with mysql 4.0.24, using gcc version 3.3.5 (Debian 1:3.3.5-13), and the libmysqlclient 10.0.0 version (i believe).

so here's a basic idea of how I'm doing this (i've simplified this a little bit, but this is the general pattern of the code):

string p = argv[1];

char* iv_str = "abcdefgh"; // assign any random string ==> iv_str not actually used by 3DES in ECB mode

string key = "sdkfjnsdlkjnsdfsdftytiyu"; // random string of chars
int key_len = key.length(); // for 3DES, 24 bytes long

MCRYPT td = mcrypt_module_open(MCRYPT_3DES, NULL, MCRYPT_ECB, NULL);

int block_size = 8; // 3DES has blocksize of 8
int data_len = p.length();
int real_data_size = ((data_len / block_size) + 1) * block_size;  // because 3DES is a block algorithm, needs data in whole blocks, so find next highest block size >= data_len

char* p_str = (char*)malloc(real_data_size + 1);
memset(p_str,0,real_data_size + 1);
memcpy(p_str,(char*)p.c_str(),p.length()); // put contents of p into p_str, for use by mcrypt functions

mcrypt_generic_init(td, (char*)(key.c_str()), key_len, iv_str);
mcrypt_generic(td, p_str, data_size);
mcrypt_generic_deinit(td);

MYSQL mysql;
MYSQL_RES *result;
MYSQL_ROW row;
mysql_real_connect(&mysql, "", "", "", "", 0, "", 0); // real values removed for security
mysql_init(&mysql);

char* p_esc = (char*)malloc(256); // plenty big enough for any escaping chars to be added to string
memset(p_esc,0,256);
int p_esc_len = mysql_real_escape_string(&mysql,p_esc,p_str,data_size);

char* query_str = (char*)malloc(1024);
memset(query_str,0,1024);

int query_len = sprintf(query_str,"insert into my_table values ('%*s')",p_esc_len,p_esc);

mysql_real_query(&mysql,query_str,query_len);

memset(query_str,0,1024);
query_len = sprintf(query_str,"select * from my_table");

mysql_real_query(&mysql,query_str,query_len);
result = mysql_store_result(&mysql);
row = mysql_fetch_row(result);

if (strcmp((char*)row[0],p_str) != 0) {
   cout << "inserted/retrieved values don't match." << endl; // the retrieved value differs from what was put into the insert statement about 2% of the time.
}

mcrypt_generic_init(td, (char*)(key.c_str()), key_len, iv_str);
mdecrypt_generic(td, row[0], data_size);
mcrypt_generic_deinit(td);

cout << row[0] << endl;  // this should be unencrypted everytime, but it fails obviously when the retrieved value is wrong

so, basically, i encrypt a number with a key, escape that encrypted string, stick it in a varchar(255) column, retrieve it, and unencrypt it. during retrieval, it is noticed that the encrypted string before and after the mysql operation sometimes differs, which identifies the source of the problem.

i've also tested that it's not the creation of the query_str through sprintf() that causes the problem, as the escaped string goes through sprintf without modification.

any ideas?

 

[Salem]

> so the 1-3% error rate is what I'm trying to track down
Which is about the rate at which you would randomly generate a '\0' byte in the result.

If you try and treat such a result as a C-style string, then you're going to come unstuck.

I would suggest you log the ASCII-HEX versions of string, so say "ABC\0" would be hex-ified into "41424300". If the failures have "00" in them, then that would be the answer.

If that is the case, you need to do that to your strings before storing them in SQL. Or make a point of using some binary interface if that is an option.


--

 

[shadedecho]

i was kinda already going on those lines, thinking that instead of sending the string straight to the varchar field as an ascii string (even mysql_escape'd), i would instead put the hex equivalents in the insert statement.

however, I am stuck on figuring out how to get the hex representation string (like "0x42300") from my encrypted string of ascii characters.

i tried adapting a "bin2hex" function I found (in the source for PHP, in fact!), but it doesn't seem to be working quite right (i still end up with some funky characters sometimes), and furthermore, even if I manually send properly formed sql statements with the hex in them in the insert sql statement, the sql fails.

but if i print out the sql string that is failing, and pop it in myself (into like phpMyAdmin interface), the sql works fine, so that has me thrown for a loop, too.

any help?

 

[shadedecho]

update:
I've gotten my bin2hex routine to reliably (it seems) return me hex strings for my ascii input string. i also figured out how to get the query to work with these hex vals, so my inserts are working correctly.

i will say that this change has reduced the number of errors down by quite a bit, relatively speaking. I just did 200 or so runs of the program, and only had 1 error.

I printed out the hex for the value (which was used in the insert) to see if the 00 problem was in there or not, as you suggested.

the value in question was: 0x8bc567f9082b5120

that doesn't have any 00's in it, so my assumption is that this value is not suffering from that problem. I can't figure out any pattern for that value that causes it to fail, and others not.

the next thing I am gonna try is to put the strings back into hex during the SELECT, and then decompose the hex back once it gets into my code. we'll see if that resolves my issues. just gotta figure out how to go from hex to ascii now.

if you think of anything else, please let me know. i feel i am close, but this is really frustrating!

 

[shadedecho]

further update:
i found out why that particular string was "bad". mysql had a bug, until sometime in the 5.x branch, that if you inserted "binary" data, that is a hex string, for instance, it was stripped of trailing spaces on the right. space happens to be hex "20". so my string above had the "20" on the right cut off when inserted.

i haven't found a way around this bug, but i have determined that it should be the only time when my strings, as hex numbers, would be less than a certain length, so i can use the RPAD function to put any missing "20"'s back on the right side of it when returning it.

This is combined with my attempt to return the strings as hex from the select statement, and then convert them back to their ascii equivalents. I am in the process of writing that function right now. its not as easy as i had hoped.

 

[shadedecho]

i think i finally got it to work. it's messy, but functional. I wish there was a more elegant way to do this.

for posterity sake, what I am doing now is simply storing the hex string AS A VARCHAR STRING, rather than as a hex string, therefore no conversion (or trimming) is done by mysql, and on my SELECT statement, i don't have to mess with HEX() and RPAD(), cause it's already in the exactly correct hex value format.

also for posterity sake, and for any input if anyone has any, here are my bin2hex and hex2bin functions:

static char hexconvtab[] = "0123456789abcdef";

static char *bin2hex(char *old, int oldlen, int *newlen)
{
    char *result = NULL;
    size_t i, j;

    int new_size = (oldlen * 2) + 1;
    result = (char*)(malloc(new_size));
    memset(result,0,new_size);

    j=0;
    unsigned int t1=0,t3=0,t4=0;

    for (i = 0; i < oldlen; i++) {
        t1 = old[i];
        t3 = (t1 >> 4) & 15;
        t4 = t1 & 15;

        result[j++] = hexconvtab[t3];
        result[j++] = hexconvtab[t4];
    }
    result[j] = '\0';

    if (newlen) *newlen = new_size;

    return result;
}

static char *hex2bin(char *old, int oldlen, int *newlen) {
        char *result = NULL;
        size_t i,j;

        int new_size = (oldlen / 2) + 1;
        result = (char*)(malloc(new_size));
        memset(result,0,new_size);

        j = 0;
        unsigned int t1=0,t2=0,t3=0,t4=0;

        for (i=0; i<oldlen; i=i+2) {
                t1 = old[i];
                if ((t1 >= 48) && (t1 <= 57)) t1 -= 48;
                else if ((t1 >= 97) && (t1 <= 102)) t1 -= 87;
                else t1 = 0;
                t2 = t1 << 4;
                t3 = old[i+1];
                if ((t3 >= 48) && (t3 <= 57)) t3 -= 48;
                else if ((t3 >= 97) && (t3 <= 102)) t3 -= 87;
                else t3 = 0;
                t4 = t2 | t3;
                result[j++] = t4;
        }
        result[j] = '\0';

        if (newlen) *newlen = new_size;

        return result;
}