bugtrack at dbmail
May 21, 2008, 12:47 PM
Post #7 of 7
(339 views)
Permalink
|
|
[DBMail 0000702]: postgresql binary string quoting
[In reply to]
|
|
The following issue has been ACKNOWLEDGED.
======================================================================
http://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: acknowledged
target:
======================================================================
Date Submitted: 15-May-08 14:47 CEST
Last Modified: 21-May-08 21:47 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.
======================================================================
----------------------------------------------------------------------
paul - 15-May-08 18:41
----------------------------------------------------------------------
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.
----------------------------------------------------------------------
Maarten Deprez - 15-May-08 22:27
----------------------------------------------------------------------
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.
----------
----------------------------------------------------------------------
paul - 15-May-08 23:08
----------------------------------------------------------------------
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.
----------------------------------------------------------------------
Maarten Deprez - 15-May-08 23:58
----------------------------------------------------------------------
Okay. I think you're right. This bug should be closed.
----------------------------------------------------------------------
Maarten Deprez - 21-May-08 19:35
----------------------------------------------------------------------
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.
----------------------------------------------------------------------
paul - 21-May-08 21:47
----------------------------------------------------------------------
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
======================================================================
_______________________________________________
Dbmail-dev mailing list
Dbmail-dev[at]dbmail.org
http://twister.fastxs.net/mailman/listinfo/dbmail-dev
|
