Re: escaping and quoting

Hello.

My dbmail server using postgresql produces a lot of warnings about '\\'
in strings. The particular string it is complaining about is escaped by
EscapeBytea, and included in single quotes (not E''). Is it all right to
set standard_compliant_strings to on?

Greetings,
Maarten Deprez

Maarten Deprez wrote:
> My dbmail server using postgresql produces a lot of warnings about '\\'
> in strings. The particular string it is complaining about is escaped by
> EscapeBytea, and included in single quotes (not E''). Is it all right to
> set standard_compliant_strings to on?

Depends.

Yours,
Laurenz Albe

I wrote:
> My dbmail server using postgresql produces a lot of warnings about '\\'
> in strings. The particular string it is complaining about is escaped by
> EscapeBytea, and included in single quotes (not E''). Is it all right to
> set standard_compliant_strings to on?

Laurenz Albe wrote:
> Depends.

Okay, what do you need to know? It can be any string, a part of an email
message. Should strings escaped by EscapeBytea be included in "''", or
"E''" in the SQL command, to avoid the warning?

Greetings,
Maarten

Maarten Deprez wrote:
>>> My dbmail server using postgresql produces a lot of warnings about '\\'
>>> in strings. The particular string it is complaining about is escaped by
>>> EscapeBytea, and included in single quotes (not E''). Is it all right to
>>> set standard_compliant_strings to on?
>>
>> Depends.
>
> Okay, what do you need to know? It can be any string, a part of an email
> message. Should strings escaped by EscapeBytea be included in "''", or
> "E''" in the SQL command, to avoid the warning?

Does the program work correctly despite the warnings?
What API are you using (there is no EscapeBytea function in the C API)?
Exactly how does the bytea string constant look that you send to the server?
Are there any other strings with backslashes in your statements?
If yes, what do they look like?

Yours,
Laurenz Albe

Albe Laurenz wrote:
> Does the program work correctly despite the warnings?

At least it seems so. I'm using it as email server without problems.

> What API are you using (there is no EscapeBytea function in the C API)?

It's the PQescapeBytea function from libpq. Note that i didn't write the
program, so i don't know the code so well.

> Exactly how does the bytea string constant look that you send to the server?

For example (taken from the log and completed a bit):
INSERT INTO ... (..., messageblk,blocksize, physmessage_id) VALUES
(0,'SA\\304...')

> Are there any other strings with backslashes in your statements?

Don't know, probably yes. The backslashes are probably generated by the
escape function.

Greetings,
Maarten Deprez

Maarten Deprez wrote:
[wants to get rid of backslash escape warnings]
> > Does the program work correctly despite the warnings?
>
> At least it seems so. I'm using it as email server without problems.
>
>
> > What API are you using (there is no EscapeBytea function in the C API)?
>
> It's the PQescapeBytea function from libpq. Note that i didn't write the
> program, so i don't know the code so well.
>
>
> > Exactly how does the bytea string constant look that you send to the server?
>
> For example (taken from the log and completed a bit):
> INSERT INTO ... (..., messageblk,blocksize, physmessage_id) VALUES
> (0,'SA\\304...')
>
>
> > Are there any other strings with backslashes in your statements?
>
> Don't know, probably yes. The backslashes are probably generated by the
> escape function.

I use information from the online documentation:
http://www.postgresql.org/docs/current/static/runtime-config-compatible.html
http://www.postgresql.org/docs/current/static/libpq-exec.html#AEN31630

You should always use PQescapeByteaConn and not PQescapeBytea.
Presumably you have no choice, though, as you did not write the code.

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.
I don't know if you can do that since you didn't write the code.
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, which you probably cannot.

Or you execute the statement with PQexecParams and do not escape
the bytea at all.

Yours,
Laurenz Albe