libpq, PQExecParams and the inserting of binary data

I have been trying to figure this out all morning, and I've gotten no
where so far.

I am trying to insert binary data into a bytea column with
PQExecParams. I couldn't find any documentation on using PQExecParams
with binary parameters, do I tried to do it this way:

int _ma_logbody(struct MailData *MailData, char *bodyp, size_t bodylen)
{
const char *paramValues[2];
char *text_body;
PGresult *res;
size_t newlen;

text_body = PQescapeBytea(bodyp, bodylen, &newlen);

paramValues[0] = MailData->MsgId;
paramValues[1] = text_body;

res = PQexecParams(conn, "insert into ma_body (msg_id, body)
VALUES ($1, $2);",
2, /* params */
NULL, /* let the backend deduce param type */
paramValues,
NULL, /* don't need param lengths since text */
NULL, /* default to all text params */
0);

PQfreemem(text_body);

if (PQresultStatus(res) != PGRES_COMMAND_OK)
{
log(LOG_MAIL, "Postresql insert failed: %s", PQerrorMessage(conn));
log(LOG_MAIL, "bodylen: %d, bodyp: %s", bodylen, bodyp);
PQclear(res);
PQfinish(conn);
conn = NULL;
return(0);
}

PQclear(res);

return(TRUE);
}

As you can see, I assumed I could use PQexapeBytea to escape the
binary data and then just use the returned value as a text parameter.
However, I randomly get insert errors with the error message:
"invalid input syntax for type Bytea".

If anybody could help me find the right way to do this I'd really
apreciate it...

Hi,

On 6/3/05, David Hinkle <drachs(at)gmail(dot)com> wrote:
> As you can see, I assumed I could use PQexapeBytea to escape the
> binary data and then just use the returned value as a text parameter.

You don't need to (also you shouldn't) escape any data while using
parameters. Because, you'll miss one big advantage of parameter usage.
>From PQexecParams() documentation:

«The primary advantage of PQexecParams over PQexec is that parameter
values may be separated from the command string, thus avoiding the
need for tedious and error-prone quoting and escaping. Unlike PQexec,
PQexecParams allows at most one SQL command in the given string.
(There can be semicolons in it, but not more than one nonempty
command.) This is a limitation of the underlying protocol, but has
some usefulness as an extra defense against SQL-injection attacks.»

Regards.

Yea, I know, but that doesn't help me. I need to know how to insert
binary data. I can't do it as text without escaping because it's not
null terminated and may contain nulls. I can't do it as binary
because I don't have any documentation on how to use it that way.
What I'm asking for is an expample or a document showing me how to do
it. I already know my way doesn't work.

If you could show me the proper way to do it that would be great.

David

On 6/3/05, Volkan YAZICI <volkan(dot)yazici(at)gmail(dot)com> wrote:
> Hi,
>
> On 6/3/05, David Hinkle <drachs(at)gmail(dot)com> wrote:
> > As you can see, I assumed I could use PQexapeBytea to escape the
> > binary data and then just use the returned value as a text parameter.
>
> You don't need to (also you shouldn't) escape any data while using
> parameters. Because, you'll miss one big advantage of parameter usage.
> From PQexecParams() documentation:
>
> «The primary advantage of PQexecParams over PQexec is that parameter
> values may be separated from the command string, thus avoiding the
> need for tedious and error-prone quoting and escaping. Unlike PQexec,
> PQexecParams allows at most one SQL command in the given string.
> (There can be semicolons in it, but not more than one nonempty
> command.) This is a limitation of the underlying protocol, but has
> some usefulness as an extra defense against SQL-injection attacks.»
>
> Regards.
>

David Hinkle <drachs(at)gmail(dot)com> writes:
> As you can see, I assumed I could use PQexapeBytea to escape the
> binary data and then just use the returned value as a text parameter.

No, because PQescapeBytea is designed to do the escaping that would be
needed to put the bytea value into a string literal in a SQL command.
There's an extra level of backslashing involved to do that (because
backslashes are special to both the string-literal syntax and the
text input syntax for bytea).

But this is really the hard way to do it considering that you are using
PQexecParams. Just tell PQexecParams that you want this parameter to be
binary format, and pass the *raw* binary string as the parameter.

> I couldn't find any documentation on using PQExecParams
> with binary parameters,

src/test/examples/testlibpq3.c ... that example is reproduced in
the libpq manual, as well.

regards, tom lane

David Hinkle wrote:

> I am trying to insert binary data into a bytea column with
> PQExecParams. I couldn't find any documentation on using PQExecParams
> with binary parameters, do I tried to do it this way:
> [...]

This code snippet may help you getting started:

http://archives.postgresql.org/pgsql-general/2004-07/msg00875.php

--
Daniel
PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org

I read all the examples and documentation thouroughly. I would not
bother you guys otherwise. That example doesn't show you how to use
any binary parameters... All the parameters are text, it only shows
you how get binary results. I think it's a huge oversight myself.

I could probably figure out how to use the parameters as binary, but I
have no idea what to do with the OID field.

David

On 6/3/05, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> David Hinkle <drachs(at)gmail(dot)com> writes:
> > As you can see, I assumed I could use PQexapeBytea to escape the
> > binary data and then just use the returned value as a text parameter.
>
> No, because PQescapeBytea is designed to do the escaping that would be
> needed to put the bytea value into a string literal in a SQL command.
> There's an extra level of backslashing involved to do that (because
> backslashes are special to both the string-literal syntax and the
> text input syntax for bytea).
>
> But this is really the hard way to do it considering that you are using
> PQexecParams. Just tell PQexecParams that you want this parameter to be
> binary format, and pass the *raw* binary string as the parameter.
>
> > I couldn't find any documentation on using PQExecParams
> > with binary parameters,
>
> src/test/examples/testlibpq3.c ... that example is reproduced in
> the libpq manual, as well.
>
> regards, tom lane
>

Thankyou very much Daniel,

I will try this. Out of curiosity, where does the value of 17 for the
OID field come from?

David

On 6/3/05, Daniel Verite <daniel(at)manitou-mail(dot)org> wrote:
> David Hinkle wrote:
>
> > I am trying to insert binary data into a bytea column with
> > PQExecParams. I couldn't find any documentation on using PQExecParams
> > with binary parameters, do I tried to do it this way:
> > [...]
>
> This code snippet may help you getting started:
>
> http://archives.postgresql.org/pgsql-general/2004-07/msg00875.php
>
> --
> Daniel
> PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org
>
>

Oh, and another thing Daniel,

Is it neccissary to provide the OID of the other fields even though
they're provided in text format? I guess what I really need to
understand is what the OID field is and how to use it.

David

On 6/3/05, Daniel Verite <daniel(at)manitou-mail(dot)org> wrote:
> David Hinkle wrote:
>
> > I am trying to insert binary data into a bytea column with
> > PQExecParams. I couldn't find any documentation on using PQExecParams
> > with binary parameters, do I tried to do it this way:
> > [...]
>
> This code snippet may help you getting started:
>
> http://archives.postgresql.org/pgsql-general/2004-07/msg00875.php
>
> --
> Daniel
> PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org
>
>

While on the subject might I ask where I could find some documentation
of the raw binary format for other types such a Date and Decimal(,)?

Thanks
Robert Perry

On Jun 3, 2005, at 1:48 PM, Tom Lane wrote:

> David Hinkle <drachs(at)gmail(dot)com> writes:
>> As you can see, I assumed I could use PQexapeBytea to escape the
>> binary data and then just use the returned value as a text parameter.
>
> No, because PQescapeBytea is designed to do the escaping that would be
> needed to put the bytea value into a string literal in a SQL command.
> There's an extra level of backslashing involved to do that (because
> backslashes are special to both the string-literal syntax and the
> text input syntax for bytea).
>
> But this is really the hard way to do it considering that you are using
> PQexecParams. Just tell PQexecParams that you want this parameter to
> be
> binary format, and pass the *raw* binary string as the parameter.
>
>> I couldn't find any documentation on using PQExecParams
>> with binary parameters,
>
> src/test/examples/testlibpq3.c ... that example is reproduced in
> the libpq manual, as well.
>
> regards, tom lane
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 8: explain analyze is your friend
>

It would be great if someone could take the time to write this up and
include it in the manual, I have to do about a million extraniouse
text conversions, small string allocations and deallocations because I
couldn't find any docs.

David

On 6/3/05, Robert Perry <rlperry(at)lodestonetechnologies(dot)com> wrote:
> While on the subject might I ask where I could find some documentation
> of the raw binary format for other types such a Date and Decimal(,)?
>
> Thanks
> Robert Perry
>
>
> On Jun 3, 2005, at 1:48 PM, Tom Lane wrote:
>
> > David Hinkle <drachs(at)gmail(dot)com> writes:
> >> As you can see, I assumed I could use PQexapeBytea to escape the
> >> binary data and then just use the returned value as a text parameter.
> >
> > No, because PQescapeBytea is designed to do the escaping that would be
> > needed to put the bytea value into a string literal in a SQL command.
> > There's an extra level of backslashing involved to do that (because
> > backslashes are special to both the string-literal syntax and the
> > text input syntax for bytea).
> >
> > But this is really the hard way to do it considering that you are using
> > PQexecParams. Just tell PQexecParams that you want this parameter to
> > be
> > binary format, and pass the *raw* binary string as the parameter.
> >
> >> I couldn't find any documentation on using PQExecParams
> >> with binary parameters,
> >
> > src/test/examples/testlibpq3.c ... that example is reproduced in
> > the libpq manual, as well.
> >
> > regards, tom lane
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 8: explain analyze is your friend
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
>

David Hinkle <drachs(at)gmail(dot)com> writes:
> ... That example doesn't show you how to use
> any binary parameters... All the parameters are text, it only shows
> you how get binary results. I think it's a huge oversight myself.

Duh, you are right. Will fix that.

> I could probably figure out how to use the parameters as binary, but I
> have no idea what to do with the OID field.

If you didn't need it for the text version, you won't need it for the
binary version. The type OID is only needed if the query is such that
the backend can't determine the type of the parameter symbol from
context. Whether you send the parameter value as text or binary has
nada to do with that.

regards, tom lane

David Hinkle wrote:

> I will try this. Out of curiosity, where does the value of 17 for the
> OID field come from?

From the pg_type table:

template1=# select oid from pg_type where typname='bytea';
oid
-----
17
(1 row)

Alternatively, there's the server/catalog/pg_type.h include file
which has #defines for built-in datatypes:

$ grep BYTEA server/catalog/pg_type.h
#define BYTEAOID 17

--
Daniel
PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org

David Hinkle wrote:

> Is it neccissary to provide the OID of the other fields even though
> they're provided in text format? I guess what I really need to
> understand is what the OID field is and how to use it.

As far as I understand, no it's not necessary. 0 instead of an
OID in the paramTypes array is fine for any parameter as long
as the type of this parameter can be correctly guessed by the
server at execution time.

--
Daniel
PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org

Hi,

On 6/3/05, David Hinkle <drachs(at)gmail(dot)com> wrote:
> [snipped]
> What I'm asking for is an expample or a document showing me how to do
> it.

While trying to prepare an example for your question, I scracthed sth.
like this:

{{{ Code snippet
const char *command = "INSERT INTO tbl1 VALUES ($1)";
int nParams = 1;
const Oid paramTypes[] = {17}; // 17, for bytea type's OID.
const char * const paramValues[] = {"\\101\\102\\103"}; // "ABC"
const int *paramLengths = NULL; // Letting the backend to
const int *paramFormats = NULL; // deduce these params.
int resultFormat = 0; // Result will be in text format.

conn = PQconnectdb("dbname=test");
if ( PQstatus(conn) != CONNECTION_OK ) {
fprintf(stderr, "Connection failed!\n%s", PQerrorMessage(conn));
exit_nicely(conn);
}

printf("Command: \"%s\",\n", command);
printf("Param. : \"%s\".\n", paramValues[0]);
res = PQexecParams(conn, command, nParams, paramTypes, paramValues,
paramLengths, paramFormats, resultFormat);

if ( PQresultStatus(res) != PGRES_COMMAND_OK ) {
fprintf(stderr, "Insert failed!\n%s", PQresultErrorMessage(res));
PQclear(res);
exit_nicely(conn);
} else {
printf("Insert succeeded.\n");
PQclear(res);
}
}}}

Above code is working well for me. But while trying some other
combinations (out of NULL usage) for PQexecParams parameters, I
realized that when we use "const int paramFormats[] = {1};" execution
dumps SegFault:

{{{ Command output snippet
$ gcc -g -Wall -lpq binIns.c && ./a.out
Command: "INSERT INTO tbl1 VALUES ($1)",
Param. : "\101\102\103".
Segmentation fault
$ gdb ./a.out
[snipped]
Using host libthread_db library "/lib/tls/libthread_db.so.1".

(gdb) run
Starting program: /home/knt/temp/a.out
Command: "INSERT INTO tbl1 VALUES ($1)",
Param. : "\101\102\103".

Program received signal SIGSEGV, Segmentation fault.
0xb7fcee05 in PQsendQueryGuts () from /usr/lib/libpq.so.4
(gdb) bt
#0 0xb7fcee05 in PQsendQueryGuts () from /usr/lib/libpq.so.4
#1 0xb7fce902 in PQsendQueryParams () from /usr/lib/libpq.so.4
#2 0xb7fcf420 in PQexecParams () from /usr/lib/libpq.so.4
#3 0x08048811 in main () at binIns.c:34
}}}

[Line 34: "res = PQexecParams(conn, ..."]

Is this an expected behaviour or did I miss sth. important?

Regards.

Volkan YAZICI wrote:

> res = PQexecParams(conn, command, nParams, paramTypes, paramValues,
> paramLengths, paramFormats, resultFormat);
[...]
> Above code is working well for me. But while trying some other
> combinations (out of NULL usage) for PQexecParams parameters, I
> realized that when we use "const int paramFormats[] = {1};" execution
> dumps SegFault:

Are you passing a NULL pointer in paramLengths? You need to
pass an array containing the actual length of your binary data.

--
Daniel
PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org

Hi,

On 6/4/05, Daniel Verite <daniel(at)manitou-mail(dot)org> wrote:
> Are you passing a NULL pointer in paramLengths? You need to
> pass an array containing the actual length of your binary data.

Yes, paramLengthts is a NULL pointer. But, is SegFault an expected result?

Himms... When I take a look at src/interfaces/libpq/fe-exec.c:

956 if (paramValues && paramValues[i])
957 {
958 int nbytes;
960 if (paramFormats && paramFormats[i] != 0)
961 {
962 /* binary parameter */
963 nbytes = paramLengths[i];

It appears to be SegFault was caused by paramLengths[i] on line 963.
So, this means, user _has_to_ specify the size of input while using
binary data. Maybe a "if ( paramLengths )" check can be useful.

Regards.

Volkan YAZICI <volkan(dot)yazici(at)gmail(dot)com> writes:
> So, this means, user _has_to_ specify the size of input while using
> binary data.

You expected something different? libpq could hardly assume that
strlen() is the way to find out the length of a binary parameter.

regards, tom lane

Hi,

On 6/4/05, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Volkan YAZICI <volkan(dot)yazici(at)gmail(dot)com> writes:
> > So, this means, user _has_to_ specify the size of input while using
> > binary data.
>
> You expected something different?

No. But, throwing an exception inspite of a segmentation fault could be better.

Regards.