Re: inserting bytea using PHPs pg_escape_bytea()

On Thu, 2011-10-20 at 14:13 -0300, Martín Marqués wrote:
> How would that work with abstraction layers like MDB2 or PDO?

I'm not sure. If there isn't some way to use parameterized queries, then
it's not a very good abstraction layer, in my opinion (because
parameterized queries are widely recognized as a good idea).

Sometimes it is tied to the mechanism for preparing a query -- you might
try that.

> The only place I get these messages are when inserting (or updateing)
> bytea columns with images (normally jpeg and png).

That's probably because normal strings aren't as likely to use escape
sequences. But binary data pretty much needs to, so it does octal
escapes (or is it hex now?), like: \000 for a zero byte.

However, because the non-standard string literals allow for backslash
escapes as well, it ends up looking like (for
standard_conforming_strings=FALSE):

'\\000'

after escaping the bytea and escaping it to be a string literal.

When standard_conforming_strings is on, then backslash is no longer a
special character in string literals, so it can just do the bytea
escaping and that's it, so the zero byte as a string literal would look
like:

'\000'

or perhaps:

'\x00'

I hope this helps. My advice is to just try it in different ways and see
what strings are sent to postgresql (by setting
log_statement_min_duration=0, which will log all the SQL).

Regards,
Jeff Davis