0

Does anyone know how to properly prepare for BYTEA data type insertion into postgresql? I have an encrypted string generated from libmcrypt. I am I wish to store the encryption in a table column defined as "cdata bytea not null"

I have the core working perfectly with command line but now I wish to store the encryptions in the RDBMS as opped to files. The code snippet is a follow:

int rs;
char buffer[1];
char dbuffer[1024];
datafile = "This is my house";  // assume this to be a file
crypt_key[] = "12345678901234567890123456789012";  //  32 bytes
crypt_iv[] =  "11111111111111111111111111111111";  // 32 bytes
mfd = mcrypt_module_open(MCRYPT_RIJNDAEL_256, NULL, "cfb", NULL);  // assume success
mcrypt_generic_init(mfd, crypt_Key, 32,crypt_iv);  // assume success

while(readInputFile(datafile,buffer,sizeof(buffer),&bytes) == cgiFormSuccess) {
        mcrypt_generic(mfd,buffer,sizeof(buffer));  // buffer size s/b 1
        dbuffer[i++] = *buffer;
        dbuffer[i] = '\0';  // Time spent on string sanity
}  // processed each byte is now encrypted

// Now I wish to prepare dbuffer for table insertion
sb = PQescapeByteaConn(dbconn,dbuffer,(size_t)strlen(dbuffer),&rs);

// Perform Insertion --> cdata::BYTEA
sprintf(query,"INSERT INTO crypto (uid,crypt_key,crypt_iv,cdata,cfile)"
                  "VALUES('%s','%s','%s','%s','%s')",
         ebs->uid,ebs->crkey,ebs->crivs,sb,credf);  // cfile == original filename
ebs->r=db_func_query(ebs->r,query,0,proc);  // Please assume DB command success

// Expected output sb == \x...some hex, dbuffer == encrypted bytes.  sb is now in bytea table column.
######################################
// Prepare to decrypt the cdata::bytea column

sprintf(query,"DECLARE %s CURSOR FOR SELECT crypt_iv,cdata,cfile "  // not sure if cursor s/b regular or binary for this
                  "FROM crypto WHERE uid='%s' AND crypt_iv='%s' AND action=true",
         VCURSOR,ebs->uid,ebs->crkey);

db_func_txn_begin(ebs->r,proc);
ebs->r = db_func_query(ebs->r,query,1,proc);  // process the query and assume it delivers the row
if(totalrow) {
     nFields = PQnfields(ebs->r);
     char* results[nFields];
     for(i = 0;i < totalrow;i++) {
          for(j = 0;j < nFields;j++)
               results[j] = PQgetvalue(ebs->r,i,j);
          strcpy(crypt_iv,results[0]);
          strcpy(dbuffer,results[1]);
          strcpy(cfile,results[2]);
}
mcrypt_generic_init(mfd, crypt_Key, 32,crypt_iv);  // assume success
sb = PQunescapeBytea(dataBuf,&rs);

for(i = 0;i < rs+1;i++) {
     mdecrypt_generic(mfd,sb[i],1);  // buffer size s/b 1
     dbuffer[i] = sb[i];
     dbuffer[i+1] = '\0';  // Time spent on string sanity
}

// Expected output sb == reverse of PQescapeByteaConn, dbuffer == unencrypted bytes.

There has got to be a way to successfully insert and query encrypted strings for decryption.

Thanks in advance.

Rico
  • 11
  • 6
  • Cross-post from pgsql-hackers: http://www.postgresql.org/message-id/1362369274285-5747243.post@n5.nabble.com . Readers who find this later should look that thread up too, in case there's relevant information posted there later. – Craig Ringer Mar 04 '13 at 07:54
  • BTW, this code should really be using libpq's parameterized query interface to make sure it's safe from any possible SQL injection risks, and for easier debugging and escaping. – Craig Ringer Mar 04 '13 at 07:59
  • The procedure db processing procedure you refer to is just a wrapper for my libpq library call so to cut down on number of lines written and it's been working for years now without any known or reported compromise. However, I am trying to find the answer to a binary string issue I'm having with encrypted output and, what I believe is, the preparation technique I am using. This code works perfectly with file I/O but not with DB insertion call. I am developing this for PSQL version 9.2.3 – Rico Mar 04 '13 at 15:39
  • OK... and if you print to the console the encrypted bytes you also wrote to the DB, can you verify that they're the same in the DB? i.e. is your problem with the write- or the read-side of your code? – Craig Ringer Mar 04 '13 at 23:48
  • Craig, yes, the console and DB content are identical. If I comment out the encryption and PGescapeByteaConn calls, I verify what I am actually trying to encrypt. The problem seems to begin with PGencryptByteaConn. – Rico Mar 05 '13 at 18:26
  • Try to keep in mind that the this code works as expected in file I/O mode. Only with DB preparation call, I begin to experience the problem. Thanks. – Rico Mar 05 '13 at 18:32
  • Do you mean `PGescapeByteaConn`? There's no `PGencryptByteaConn`. Can you please show some data before and after a `PGencryptByteaConn` call if you suspect it is the problem in your situation? – Craig Ringer Mar 05 '13 at 23:10
  • Yes, I did mean to write PGescapeByteaConn, thank you. I am so engrossed with encryption these days that the word is now fraudian in my mind. `This is 'my' house` == `2013-03-06 | 09:17:15.052726 | W016689 | 180c14b5-4cfa-4163-8934-bfee2f53b251 | dzUIZmXA42QBqf110w0bu16pRQQBygDQ | 12345678901234567890123456789012 | \x5468697320697320276d792720686f757365 | cipher_test.c | t | This is 'my' house (1 row) ` this is the pre and post prmats out of my PGescape call. – Rico Mar 06 '13 at 15:23
  • Yes, I did mean to write PGescapeByteaConn, thank you. I am so engrossed with encryption these days that the word is now freudian in my mind. `This is 'my' house` == `2013-03-06 | 09:17:15.052726 | W016689 | 180c14b5-4cfa-4163-8934-bfee2f53b251 | dzUIZmXA42QBqf110w0bu16pRQQBygDQ | 12345678901234567890123456789012 | \x5468697320697320276d792720686f757365 | cipher_test.c | t | This is 'my' house (1 row) ` this is the pre and post prmats out of my PGescape call. So when I perform the escape call after encryption is when failure occur. – Rico Mar 06 '13 at 15:34
  • Those all look like a `psql` row output. What I'm saying is that for debugging you should `printf()` the data before calling `PQescapeByteaConn` on it and `printf()` it after as well. Since you suspect the problem is with PQescapeByteaConn you should *isolate it* to remove other factors and see if the output matches the expected output for a given input. Personally I think you're wasting your time and should be using parameterised queries. – Craig Ringer Mar 06 '13 at 23:31
  • Ok... I resolved the issue. However, prior to doing that, I have sliced and diced all possibilities going into the issue to include traps and debugging views. I apologize for not fully understanding your request. I got a very good understanding of the PGescapeString, PGescapeBytea, PGunescapeBytea calls by looking under the hood in the source tree. Better documentation on these functions is probably in order, especial with encrypted scenarios. Anyway, using a combination of PGescapeStringConn and PGescapeBytea at the right places did the trick. This issue is resolved. – Rico Mar 07 '13 at 06:08
  • It would be immensely helpful if you could explain what you found to be wrong, what you changed, how you expected to work and how that differed from how it actually works so I can submit a patch to improve the documentation. – Craig Ringer Mar 07 '13 at 06:16
  • i am stuck in a similar but strange situation.. For me escaping code is written in C language, I did that and it is working. Now the problem is I need to decode (unescape) the code inside JAVA code. I can't find any way of doing that. Please help!!! – Amit Nov 18 '13 at 08:56

1 Answers1

0

Issue resolved. You can can get away with using just a text column as long as you escape the output of mcrypted string. Bytea is cleaner but more code lines and PGunescapeBytea is obviously mandatory for decryption.

Rico
  • 11
  • 6