The following issue has been CLOSED ====================================================================== http://www.dbmail.org/mantis/view.php?id=702 ====================================================================== Reported By: Maarten Deprez Assigned To: ====================================================================== Project: DBMail Issue ID: 702 Category: Authentication layer Reproducibility: always Severity: feature Priority: normal Status: closed target: Resolution: no change required Fixed in Version: ====================================================================== Date Submitted: 15-May-08 14:47 CEST Last Modified: 30-May-09 15:16 CEST ====================================================================== Summary: postgresql binary string quoting Description: Postgresql needs <E'...'> quoting for binary strings. Currently by default it accepts normal quoting with a warning, but the manual warns it will change in a future release. ======================================================================
---------------------------------------------------------------------- (0002553) paul (administrator) - 15-May-08 18:41 http://www.dbmail.org/mantis/view.php?id=702#c2553 ---------------------------------------------------------------------- Maarten, As far as I understand the PG docs, whenever a string has been put through the PGescapeString or related calls, the string or bytea has indeed been made safe to include in a query string - even without the E'' construct. That is why I closed the earlier report. In the trunk this issue is moot anyway since there all insertions involving strings or binary data is done with prepared statements. ---------------------------------------------------------------------- (0002554) Maarten Deprez (reporter) - 15-May-08 22:27 http://www.dbmail.org/mantis/view.php?id=702#c2554 ---------------------------------------------------------------------- Well, my log is full of the following, so it definitely not all right. ---------- HINT: Use the escape string syntax for backslashes, e.g., E'\\'. WARNING: nonstandard use of \\ in a string literal LINE 1: ..., messageblk,blocksize, physmessage_id) VALUES (0,'... ---------- The documentation says (http://www.postgresql.org/docs/8.3/interactive/sql-syntax-lexical.html): ---------- PostgreSQL also accepts "escape" string constants, which are an extension to the SQL standard. An escape string constant is specified by writing the letter E (upper or lower case) just before the opening single quote, e.g. E'foo'. [...] Within an escape string, a backslash character (\) begins a C-like backslash escape sequence, in which the combination of backslash and following character(s) represents a special byte value. \b is a backspace, \f is a form feed, \n is a newline, \r is a carriage return, \t is a tab. Also supported are \digits, where digits represents an octal byte value, and \xhexdigits, where hexdigits represents a hexadecimal byte value. [...] Caution If the configuration parameter standard_conforming_strings is off, then PostgreSQL recognizes backslash escapes in both regular and escape string constants. This is for backward compatibility with the historical behavior, in which backslash escapes were always recognized. Although standard_conforming_strings currently defaults to off, the default will change to on in a future release for improved standards compliance. Applications are therefore encouraged to migrate away from using backslash escapes. If you need to use a backslash escape to represent a special character, write the constant with an E to be sure it will be handled the same way in future releases. ---------- ---------------------------------------------------------------------- (0002555) paul (administrator) - 15-May-08 23:08 http://www.dbmail.org/mantis/view.php?id=702#c2555 ---------------------------------------------------------------------- My point remains and is confirmed by this excerpt. We don't use backslash-escaping at all. All strings are passed through PGescapeString before inclusion in a sql statement. You can safely set standard_conforming_strings to 'on' as far as dbmail is concerned. Feel free to seek confirmation of my assessment in the postgresql community if you like. ---------------------------------------------------------------------- (0002556) Maarten Deprez (reporter) - 15-May-08 23:58 http://www.dbmail.org/mantis/view.php?id=702#c2556 ---------------------------------------------------------------------- Okay. I think you're right. This bug should be closed. ---------------------------------------------------------------------- (0002558) Maarten Deprez (reporter) - 21-May-08 19:35 http://www.dbmail.org/mantis/view.php?id=702#c2558 ---------------------------------------------------------------------- An excerpt from an answer to my question on the postgresql mailing list. You should always use PQescapeByteaConn and not PQescapeBytea. [...] You can get rid of the warnings by setting escape_string_warning=off This requires that standard_conforming_strings=off Alternatively (and this is better) you can preceed the string with E (as in E'SA\\304...') and leave escape_string_warning=on. [...] This makes you independent of the setting of standard_conforming_strings. You can also (third option) set standard_conforming_strings=on. But then you must use the PQescapeByteaConn function [...] Or you execute the statement with PQexecParams and do not escape the bytea at all. ---------------------------------------------------------------------- (0002559) paul (administrator) - 21-May-08 21:47 http://www.dbmail.org/mantis/view.php?id=702#c2559 ---------------------------------------------------------------------- Definitely 'not-a-bug'. Issue History Date Modified Username Field Change ====================================================================== 15-May-08 14:47 Maarten Deprez New Issue 15-May-08 14:47 Maarten Deprez File Added: quote.patch 15-May-08 18:41 paul Note Added: 0002553 15-May-08 22:27 Maarten Deprez Note Added: 0002554 15-May-08 23:08 paul Note Added: 0002555 15-May-08 23:58 Maarten Deprez Note Added: 0002556 21-May-08 19:35 Maarten Deprez Note Added: 0002558 21-May-08 21:47 paul Note Added: 0002559 21-May-08 21:47 paul Severity minor => feature 21-May-08 21:47 paul Status new => acknowledged 21-May-08 21:47 paul Resolution open => no change required 30-May-09 15:16 paul Status acknowledged => closed ====================================================================== _______________________________________________ Dbmail-dev mailing list Dbmail-dev@dbmail.org http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail-dev
