Re: inserting bytea using PHPs pg_escape_bytea()

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

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?