Thread: PQescapeBytea* version for parameters

Currently libpq provides a function to escape byteas to include directly in
the query string. But if you're using PQexecParam you still need to do one
layer of quoting but don't need to double the backslashes which PQescapeBytea
does if you have standard_conforming_strings set off.

Do we want something like this which provides a PQescapeByteaParam for
escaping bytea strings before passing them as text-mode parameters in
PQexecParam?

I cheated here and just passed true to standard_conforming_strings which
happens to do what we want. It might be better to provide an additional
parameter to PQescapeByteaInternal which tells it to only escape \ and NUL and
not other binary characters and '.

I didn't document it yet, I'll do that if people agree we want it.



--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com

Gregory Stark <stark@enterprisedb.com> writes:
> Do we want something like this which provides a PQescapeByteaParam for
> escaping bytea strings before passing them as text-mode parameters in
> PQexecParam?

Seems a lot easier and more efficient to just pass out-of-line bytea
parameters as binary mode.
        regards, tom lane

"Tom Lane" <tgl@sss.pgh.pa.us> writes:

> Gregory Stark <stark@enterprisedb.com> writes:
>> Do we want something like this which provides a PQescapeByteaParam for
>> escaping bytea strings before passing them as text-mode parameters in
>> PQexecParam?
>
> Seems a lot easier and more efficient to just pass out-of-line bytea
> parameters as binary mode.

Well that's definitely true. The case in hand was a PHP where the PHP driver
doesn't seem to automatically use binary mode and doesn't provide any way for
the application to select it either. 

It expects the user code to handle the escaping for all parameters using
PQEscape* functions. But there is no candidate function to handle bytea ascii
parameters. I'm sure it can be done in PHP directly though.

Incidentally it seems even using PQEscapeBytea with standard conforming
strings set is still corrupting the byteas so there may be an actual bug
somewhere. Haven't had a chance to look into it yet though.

--  Gregory Stark EnterpriseDB          http://www.enterprisedb.com

"Tom Lane" <tgl@sss.pgh.pa.us> writes:

> Gregory Stark <stark@enterprisedb.com> writes:
>> Do we want something like this which provides a PQescapeByteaParam for
>> escaping bytea strings before passing them as text-mode parameters in
>> PQexecParam?
>
> Seems a lot easier and more efficient to just pass out-of-line bytea
> parameters as binary mode.

Hm, the cause of the problem with using PQescapeBytea with
standard_comforming_strings as a cheap substitute for an actual
PQescapeByteaParam is that it currently escapes ' as '' regardless of the
setting of standard_conforming_string.

else if (*vp == '\'')
{       *rp++ = '\'';       *rp++ = '\'';
}

Shouldn't it escape ' as \' and not '' if standard_conforming_strings is
false?

What I would actually suggest is that it just escape ' and \ the same way it
does binary characters by inserting the bytea escapes \047 and \134. That
actually simplifies the code quite a bit and avoids a lot of special cases for
standard_conforming_strings.


Index: fe-exec.c
===================================================================
RCS file: /home/stark/src/REPOSITORY/pgsql/src/interfaces/libpq/fe-exec.c,v
retrieving revision 1.192
diff -u -r1.192 fe-exec.c
--- fe-exec.c    5 Jan 2007 22:20:01 -0000    1.192
+++ fe-exec.c    11 Jul 2007 15:34:25 -0000
@@ -2755,28 +2755,13 @@    vp = from;    for (i = from_length; i > 0; i--, vp++)    {
-        if (*vp < 0x20 || *vp > 0x7e)
+        if (*vp < 0x20 || *vp > 0x7e || *vp == '\'' || *vp == '\\')        {            if (!std_strings)
 *rp++ = '\\';            (void) sprintf((char *) rp, "\\%03o", *vp);            rp += 4;        }
 
-        else if (*vp == '\'')
-        {
-            *rp++ = '\'';
-            *rp++ = '\'';
-        }
-        else if (*vp == '\\')
-        {
-            if (!std_strings)
-            {
-                *rp++ = '\\';
-                *rp++ = '\\';
-            }
-            *rp++ = '\\';
-            *rp++ = '\\';
-        }        else            *rp++ = *vp;    }


--  Gregory Stark EnterpriseDB          http://www.enterprisedb.com

Gregory Stark <stark@enterprisedb.com> writes:
> Shouldn't it escape ' as \' and not '' if standard_conforming_strings is
> false?

No.  That's always worked and there's no reason to change it.
        regards, tom lane

Tom Lane wrote:
> Gregory Stark <stark@enterprisedb.com> writes:
> > Shouldn't it escape ' as \' and not '' if standard_conforming_strings is
> > false?
> 
> No.  That's always worked and there's no reason to change it.

'' is more standard than \' so we always use ''.

--  Bruce Momjian  <bruce@momjian.us>          http://momjian.us EnterpriseDB
http://www.enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +