Thread: 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 20/10/11 14:13, Martín Marqués escribió:
With PDO just check http://www.php.net/manual/en/pdo.prepare.phpEl 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.phpHow 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