Обсуждение: LIBPQ Implementation Requiring BYTEA Data
Hello All First, I am new to this great forum. I have a challenge on my hand as follows. I am a long time libpq user but have never used the BYTEA data type nor its related functions until now. I have am writing an interface for a web based application written in C using libmcrypt and, of course, libpq. My problem seems to be proper preparation of encrypted data for insert into a BYTEA column. For insertion, I properly process a file (byte-by-byte) through mcrypt, then I use PQescapeByteaConn as (snippet) follows: *while(readInputFile(file,buffer,sizeof(buffer),&bytes) == Success) { mcrypt_generic(mfd,buffer,sizeof(buffer)); // buffer size == 1 byte dbuffer[i++] = *buffer; dbuffer[i] = '\0'; // Time spent on string sanity } close(inputFile); sb = PQescapeByteaConn(dbconn,dbuffer,(size_t)strlen(dbuffer),&rl); sprintf(query,"INSERT INTO crypto(uid,tdkey,ivkey,cdata,cfile)" //cdata is a bytea column "VALUES('%s','%s','%s','%s','%s')", ebs->uid,ebs->crkey,ebs->crivs,sb,credf); ebs->r=db_func_query(ebs->r,query,0,proc); * What I insert into the bytea column is \x748a590ffdb8dc748dd3fba... Now sb returns these same bits consistently each time I run the same file through mcrypt, using the same key/salt combo which I expect. However, I cannot verify whether the the data inserted is what it should be since I cannot decrypt. I've tried using PQunescapeBytea(data,&size) for the decrypt preparation expecting pretty much the reverse of PQescapeByteaConn but end up with garbage. If anyone can lend me a good suggestion or example of properly preparing binary data strings for pg insertion, i will be very much grateful. -- View this message in context: http://postgresql.1045698.n5.nabble.com/LIBPQ-Implementation-Requiring-BYTEA-Data-tp5747243.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
On 03/04/2013 11:54 AM, Cliff_Bytes wrote: > Hello All > > First, I am new to this great forum. > > I have a challenge on my hand as follows. I am a long time libpq user but > have never used the BYTEA data type nor its related functions until now. I > have am writing an interface for a web based application written in C using > libmcrypt and, of course, libpq. > > My problem seems to be proper preparation of encrypted data for insert into > a BYTEA column. For insertion, I properly process a file (byte-by-byte) > through mcrypt, then I use PQescapeByteaConn as (snippet) follows: > > *while(readInputFile(file,buffer,sizeof(buffer),&bytes) == Success) { > mcrypt_generic(mfd,buffer,sizeof(buffer)); > // buffer size == 1 byte > dbuffer[i++] = *buffer; > dbuffer[i] = '\0'; // Time spent on string > sanity > } > close(inputFile); > sb = PQescapeByteaConn(dbconn,dbuffer,(size_t)strlen(dbuffer),&rl); > sprintf(query,"INSERT INTO crypto(uid,tdkey,ivkey,cdata,cfile)" //cdata is > a bytea column > "VALUES('%s','%s','%s','%s','%s')", > ebs->uid,ebs->crkey,ebs->crivs,sb,credf); > ebs->r=db_func_query(ebs->r,query,0,proc); > * > > What I insert into the bytea column is \x748a590ffdb8dc748dd3fba... > > Now sb returns these same bits consistently each time I run the same file > through mcrypt, using the same key/salt combo which I expect. However, I > cannot verify whether the the data inserted is what it should be since I > cannot decrypt. I've tried using PQunescapeBytea(data,&size) for the > decrypt preparation expecting pretty much the reverse of PQescapeByteaConn > but end up with garbage. You probably need to supply a self-contained, compileable test case. Right now there's no sign that this isn't an issue elsewhere in the application/client code. -- Craig Ringer http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services
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.
<div class="moz-cite-prefix">On 03/04/2013 01:51 PM, Cliff_Bytes wrote:<br /></div><blockquote cite="mid:1362376283576-5747260.post@n5.nabble.com"type="cite"><pre wrap=""> 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. </pre></blockquote> It does, but it doesn't let anyone compile it and actually reproduce the problem you're encounteringor trace what it's doing without spending more time than they might want to. Certainly more time than I wantto.<br /><br /><a href="http://sscce.org/">http://sscce.org/</a><br /><br /><br /><pre class="moz-signature" cols="72">--Craig Ringer <a class="moz-txt-link-freetext" href="http://www.2ndQuadrant.com/">http://www.2ndQuadrant.com/</a>PostgreSQLDevelopment, 24x7 Support, Training & Services</pre>
*That was a brilliant response! Thank you.* -- View this message in context: http://postgresql.1045698.n5.nabble.com/LIBPQ-Implementation-Requiring-BYTEA-Data-tp5747243p5747263.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
<div class="moz-cite-prefix">On 03/04/2013 11:54 AM, Cliff_Bytes wrote:<br /></div><blockquote cite="mid:1362369274285-5747243.post@n5.nabble.com"type="cite"><pre wrap="">I have a challenge on my hand as follows. Iam a long time libpq user but have never used the BYTEA data type nor its related functions until now. I have am writing an interface for a web based application written in C using libmcrypt and, of course, libpq. </pre></blockquote> For anyone with a similar issue who finds this later, it's been cross-posted to Stack Overflow at <a href="http://stackoverflow.com/questions/15196151/preparing-storing-retrieving-encrypted-data-in-postgresql">http://stackoverflow.com/questions/15196151/preparing-storing-retrieving-encrypted-data-in-postgresql</a> .Look there for information too.<br /><br /><pre class="moz-signature" cols="72">-- Craig Ringer <a class="moz-txt-link-freetext"href="http://www.2ndQuadrant.com/">http://www.2ndQuadrant.com/</a>PostgreSQL Development, 24x7Support, Training & Services</pre>
On Sun, Mar 3, 2013 at 9:54 PM, Cliff_Bytes <creid@eclipssolutions.com> wrote: > Hello All > > First, I am new to this great forum. > > I have a challenge on my hand as follows. I am a long time libpq user but > have never used the BYTEA data type nor its related functions until now. I > have am writing an interface for a web based application written in C using > libmcrypt and, of course, libpq. > > My problem seems to be proper preparation of encrypted data for insert into > a BYTEA column. For insertion, I properly process a file (byte-by-byte) > through mcrypt, then I use PQescapeByteaConn as (snippet) follows: > > *while(readInputFile(file,buffer,sizeof(buffer),&bytes) == Success) { > mcrypt_generic(mfd,buffer,sizeof(buffer)); > // buffer size == 1 byte > dbuffer[i++] = *buffer; > dbuffer[i] = '\0'; // Time spent on string > sanity > } > close(inputFile); > sb = PQescapeByteaConn(dbconn,dbuffer,(size_t)strlen(dbuffer),&rl); > sprintf(query,"INSERT INTO crypto(uid,tdkey,ivkey,cdata,cfile)" //cdata is > a bytea column > "VALUES('%s','%s','%s','%s','%s')", > ebs->uid,ebs->crkey,ebs->crivs,sb,credf); > ebs->r=db_func_query(ebs->r,query,0,proc); > * > > What I insert into the bytea column is \x748a590ffdb8dc748dd3fba... > > Now sb returns these same bits consistently each time I run the same file > through mcrypt, using the same key/salt combo which I expect. However, I > cannot verify whether the the data inserted is what it should be since I > cannot decrypt. I've tried using PQunescapeBytea(data,&size) for the > decrypt preparation expecting pretty much the reverse of PQescapeByteaConn > but end up with garbage. > > If anyone can lend me a good suggestion or example of properly preparing > binary data strings for pg insertion, i will be very much grateful. Why don't you give libpqtypes a whirl. It manages binary wire formats for you: PGbytea b; b.len = 500 b.data = some_data_ptr; res = PQexecf(conn, "INSERT INTO foo(byteacol) VALUES (%bytea)", &b); http://libpqtypes.esilo.com/ If you don't do that, advise either: * using libpq binary wire format via PQexecParams * manually decode/encode bytea to hex via server encode/decode functions and deal with data as text on client. I don't like the escaping functions. merlin
Merlin I will try your suggestion, thanks. I am somewhat surprised to find few hacks related to my issue. And the BYTEA type and function documentation leave much to be desired, IMHO, being a newbie on the Type BYTEA front. -- View this message in context: http://postgresql.1045698.n5.nabble.com/LIBPQ-Implementation-Requiring-BYTEA-Data-tp5747243p5747352.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
<div class="moz-cite-prefix">On 03/04/2013 11:57 PM, Cliff_Bytes wrote:<br /></div><blockquote cite="mid:1362412624808-5747352.post@n5.nabble.com"type="cite"><pre wrap="">Merlin I will try your suggestion, thanks. I am somewhat surprised to find few hacks related to my issue. And the BYTEA type and function documentation leave much to be desired, IMHO, being a newbie on the Type BYTEA front. </pre></blockquote> One of the most helpful things you can do when you encounter things like that is to take notes on what'sgiving you trouble, then come back later once you've found out what's going on and explain what we're missing in thedocumentation. The things that, if they'd been in the documentation, would've helped you solve your problem.<br /><br/> I try to do this whenever I'm learning a new technology; I start a "newbie notepad" with all the things I found confusing,underdocumented, or generally awful. I come back to it once I've got some idea what's going on and write it upas a constructive criticism of what docs improvements, usability fixes, etc might make coming up to speed easier. Withpatches when I get the chance.<br /><br /> Merlin's suggestion to use libpqtypes makes sense. I'd also look at using PQexecParams; you should be using it pretty much all the time anyway.<br /><pre class="moz-signature" cols="72">--Craig Ringer <a class="moz-txt-link-freetext" href="http://www.2ndQuadrant.com/">http://www.2ndQuadrant.com/</a>PostgreSQLDevelopment, 24x7 Support, Training & Services</pre>