Thread: 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>