Обсуждение: inserting bytea using PHPs pg_escape_bytea()

I have some scripts that upload some images to the DB, specifically to
a bytea column. For that I use PHPs (all scripts are in PHP)
pg_escape_bytea (and pg_unescape_bytea after extraction) before
inserting. Images go good in the DB and are shown correctly after
extraction, so it looks like there is no problem at all.

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?

--
Martín Marqués
select 'martin.marques' || '@' || 'gmail.com'
DBA, Programador, Administrador

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

El día 19 de octubre de 2011 23:20, Jeff Davis <pgsql@j-davis.com> escribió:
> 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

How would that work with abstraction layers like MDB2 or PDO?

> 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.

They are exactly that way.

> 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.

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

This is done in this way:

$foto =  file_get_contents($myFile);
$escapado = pg_escape_bytea($foto);

// $db is a MDB2 object conecting to PG
$db->exec("INSERT INTO fotos VALUES ('{$escapado}'));

> 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:

OK, so I'd have to do something like:

$escapado = pg_escape_bytea($db->connection, $foto);

But setting standard_conforming_strings to TRUE first.

If I don't change the value of standard_conforming_strings, what does
pg_escape_bytea do different?

--
Martín Marqués
select 'martin.marques' || '@' || 'gmail.com'
DBA, Programador, Administrador

El día 19 de octubre de 2011 23:20, Jeff Davis <pgsql@j-davis.com> escribió:

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

How would that work with abstraction layers like MDB2 or PDO?

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.

They are exactly that way.

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.

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

This is done in this way:

$foto =  file_get_contents($myFile);
$escapado = pg_escape_bytea($foto);

// $db is a MDB2 object conecting to PG
$db->exec("INSERT INTO fotos VALUES ('{$escapado}'));

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:

OK, so I'd have to do something like:

$escapado = pg_escape_bytea($db->connection, $foto);

But setting standard_conforming_strings to TRUE first.

If I don't change the value of standard_conforming_strings, what does
pg_escape_bytea do different?

El día 20 de octubre de 2011 14:28, Rodrigo Gonzalez
<rjgonzale@estrads.com.ar> escribió:
> El 20/10/11 14:13, Martín Marqués escribió:
>
> El día 19 de octubre de 2011 23:20, Jeff Davis <pgsql@j-davis.com> escribió:
>
> 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
>
> How would that work with abstraction layers like MDB2 or PDO?
>
> With PDO just check http://www.php.net/manual/en/pdo.prepare.php

AFAIK, that's not the same as pg_query_params.

Thats more like pg_prepare() + pg_execute().

I use it a lot with MDB2, but couldn't find something like pg_query_params()

--
Martín Marqués
select 'martin.marques' || '@' || 'gmail.com'
DBA, Programador, Administrador

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