Re: 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.

On 03/04/2013 01:51 PM, Cliff_Bytes wrote:
> 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.
It does, but it doesn't let anyone compile it and actually reproduce the
problem you're encountering or trace what it's doing without spending
more time than they might want to. Certainly more time than I want to.

http://sscce.org/

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

*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.

On 03/04/2013 11:54 AM, Cliff_Bytes wrote:
> 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.
For anyone with a similar issue who finds this later, it's been
cross-posted to Stack Overflow at
http://stackoverflow.com/questions/15196151/preparing-storing-retrieving-encrypted-data-in-postgresql
. Look there for information too.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On Sun, Mar 3, 2013 at 9:54 PM, Cliff_Bytes <creid(at)eclipssolutions(dot)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.

On 03/04/2013 11:57 PM, Cliff_Bytes wrote:
> 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.
>
One of the most helpful things you can do when you encounter things like
that is to take notes on what's giving you trouble, then come back later
once you've found out what's going on and explain what we're missing in
the documentation. The things that, if they'd been in the documentation,
would've helped you solve your problem.

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 up as a constructive criticism of what docs
improvements, usability fixes, etc might make coming up to speed easier.
With patches when I get the chance.

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.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services