Обсуждение: 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. +