Re: LIBPQ Implementation Requiring BYTEA Data - Mailing list pgsql-hackers

Thanks for the reply, Craig

Fair enough so a little more background, perhaps.  I have the core of this
program running (command line) successfully with libpq and mcrypt already
for some time.  My goal now is to house the encrypted file data in a table
with all user processing done over the SSL internet.

I am highly confident that the problem involves the preparation and
insertion of encrypted data into a bytea column then selection and
preparation for decryption.  So I will approach you this way with my
issue...

*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(dataBuf,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.*

I hope this pseudo illustrates more of what I am doing to insert encrypted
data into a bytea column and then query the same column for decryption.

Thanks again.




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/LIBPQ-Implementation-Requiring-BYTEA-Data-tp5747243p5747260.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.