Re: LIBPQ Implementation Requiring BYTEA Data

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