Re: inserting bytea using PHPs pg_escape_bytea()

On Wed, 2011-10-19 at 14:30 -0300, Martín Marqués wrote:
> The only concern I have is that on insertion, I get this WARNING:
>
> WARNING: nonstandard use of \\ in a string literal at character 41
> HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
>
> Should I worry? What does it mean?

First of all, the best solution is to use parameterized queries:

http://us.php.net/manual/en/function.pg-query-params.php

But here's the explanation for the warning:

Check the settings for:

SHOW standard_conforming_strings;
SHOW escape_string_warning;

I assume that those are false and true respectively. If that's the case,
you are safe, HOWEVER it means that you are using non-standard literals.

It's advisable to move to standard string literals (that is, as the SQL
spec defines them) because if you port your application to other systems
in the future, or if you later turn standard_conforming_strings to TRUE,
then you could be vulnerable to SQL injection.

To become standards-compliant, set standard_conforming_strings to TRUE,
and pg_escape_bytea should automatically start working in the standard
way. It is advisable to explicitly pass the connection object (first
parameter) to pg_escape_bytea() to make sure no mistakes are made. Try
it out with a few test strings to make sure it's using the correct
escaping, see:

http://www.postgresql.org/docs/9.1/static/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS

Another option is to continue to use the C-style escaping, which you can
do by prefixing the literal with an E (as described in the document
above).

I know this all sounds fairly complicated. Essentially, postgresql
adopted a non-standard literal syntax a long time ago, and has been
trying to move away from that slowly for a long time. In the end,
matching the standard syntax should be a net win against SQL injection
(as well as making porting easier).

I hope this helps.

Regards,
Jeff Davis