Escaping string data in Postgre 8.2.0

[Sebastian von Ulmenstein]

Hi,

it seems, that Postgres version 8.2.0 changed its mood in handling escaped
string data from pgsql4rb.
If my investigation is correct, the problem are quotes in strings (in my
case a column of type TEXT). 8.2.0 gives me a dialog:

Server WARNING: nonstandard use of \' in a string literal
Server Notice Code: 22P06

and the logfile shows:

WARNING: nonstandard use of \' in a string literal at character 34
HINT: Use '' to write quotes in strings, or use the escape string syntax
(E'...').

Marc, if I'm right, could you please update the pgSQLescapeLiteralString
method?

Thanks and regards
Sebastian

[Aliacta Support]

Hi Sebastian,

Post by Sebastian von Ulmenstein
WARNING: nonstandard use of \' in a string literal at character 34
HINT: Use '' to write quotes in strings, or use the escape string syntax
(E'...').

It seems we ought to use this "escape string syntax (E'...')" from now on:
http://www.postgresql.org/docs/8.2/static/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS

Post by Sebastian von Ulmenstein
Marc, if I'm right, could you please update the pgSQLescapeLiteralString
method?

If I update it to use " '' " instead of " \' " then you won't get
that warning anymore, which is a bad thing since it would still cause
trouble with (less detectable) escaping where you do have to use the
backslash--which is done in the pgSQLescapeLiteralString method for
you.

So from now on it would be safer to use the RB code:

"E'" + pgSQLescapeLiteralString(MyText) + "'"

Instead of

"'" + pgSQLescapeLiteralString(MyText) + "'"

to build the literal strings that are part of your SQL statements.

Let me know if this works.

Marc

[Sebastian von Ulmenstein]

Hi Marc,

works flawless.
In my case, the incremented column was in fact filled with pgsqlescapebytea
escaped XML-data.
An "E" in front did the job as well.

Thanks
Sebastian

Post by Aliacta Support
"E'" + pgSQLescapeLiteralString(MyText) + "'"
Instead of
"'" + pgSQLescapeLiteralString(MyText) + "'"
to build the literal strings that are part of your SQL statements.
Let me know if this works.
Marc

[Garrett Kalleberg]

Similar problem with ByteA? PostgreSQL 8.2 on OS X Server 10.4.8,
database is UTF-8, I'm getting "invalid byte sequence" messages. Any
direction here?

My code was:

sDataEscaped = App.DatabasePgSQL.pgSQLescapeBytea(sData)
sDataEscaped = me.QuoteString(sDataEscaped)

with sData going into my ByteA column. This worked flawlessly with
PostgreSQL 8.1.x. I tried:

sDataEscaped = "E'" + App.DatabasePgSQL.pgSQLescapeBytea(sData) + "'"

Truncated error message from console below, after trying the E approach:

Dec 11 16:56:17 h-72-244-140-139 /GRB Server/ Artware Alpha/Artware
Dataloader.app/Contents/MacOS/Artware Dataloader[11915]:
ImageData.CreateDataRec - Database said: ERROR: invalid byte
sequence for encoding "UTF8": 0xff\nHINT: This error can also happen
if the byte sequence does not match the encoding expected by the
server, which is controlled by "client_encoding".\n - SQL: INSERT
INTO imagesdataraw (imageid, height, width, imagedata) VALUES (4602,
0, 0, E'ÿØÿà\\000^PJFIF\\000^A^A^A\\000H\\000H\\000\\000ÿÛ\\000C\
\000^A^A^A^A^A^A^A^A^A^A^A^A^A^A^A^A^A^A^A^A^A^A^A^A^A^A^A^A^A^A^A^A^A^A
^A^A^A^A^A^A^A^A^A^A^A^A^A^A^A^A^A^A^A^A^A^A^A^A^A^A^A^A^A^AÿÛ\
\000C^A^A^A^A^A^A^A^A^A^A^A^A^A^A^A^A^A^A^A^A^A^A^A^A^A^A^A^A^A^A^A^A^A^
A^A^A^A^A^A^A^A^A^A^A^A^A^A^A^A^A^A^A^A^A^A^A^A^A^A^A^A^A^A^A^AÿÀ\
\000^Q^H^Až^A§^C^A"\\000^B^Q^A^C^Q^AÿÄ\\000^_\\000\
\000^A^E^A^A^A^A^A^A\\000\\000\\000\\000\\000\\000\\000\
\000^A^B^C^D^E^F^G^H \n^KÿÄ\\000µ^P\\000^B^A^C^C^B^D^C^E^E^D^D\\000\
\000^A}^A^B^C\\000^D^Q^E^R!1A^F^SQa^G"q^T2‘¡^H#B±Á^URÑð$3br‚
\n^V^W^X^Y^Z%&\'()
*456789:CDEFGHIJSTUVWXYZcdefghijstuvwxyzƒ„…†‡ˆ‰Š’“”•–—˜™š¢£€¥Š§š©ª²³
Žµ¶·ž¹ºÂÃÄÅÆÇÈÉ ÒÓÔÕÖ×ØÙÚáâãäåæçèéêñòóôõö÷øùúÿÄ\\000^_^A\
\000^C^A^A^A^A^A^A^A^A^A\\000\\000\\000\\000\\000\
\000^A^B^C^D^E^F^G^H \n^KÿÄ\\000


- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - - - - - - - - - - - -

Garrett Kalleberg

Metadada Experimental

mailto:***@metadada.com
http://www.metadada.com/experimental

Post by Sebastian von Ulmenstein
Hi Marc,
works flawless.
In my case, the incremented column was in fact filled with
pgsqlescapebytea
escaped XML-data.
An "E" in front did the job as well.
Thanks
Sebastian

Post by Aliacta Support
"E'" + pgSQLescapeLiteralString(MyText) + "'"
Instead of
"'" + pgSQLescapeLiteralString(MyText) + "'"
to build the literal strings that are part of your SQL statements.
Let me know if this works.
Marc

_______________________________________________
Postgresql mailing list
http://aliacta.com/mailman/listinfo/postgresql_aliacta.com

[Aliacta Support]

Hi Garrett,

Are you using the latest pgSQL4RB? (v2fc3)

Marc

Post by Garrett Kalleberg
Similar problem with ByteA? PostgreSQL 8.2 on OS X Server 10.4.8,
database is UTF-8, I'm getting "invalid byte sequence" messages. Any
direction here?

[Garrett Kalleberg]

No, I believe it's the 1.x version.

I only use your classes for some bytea stuff in one project, using
the RB plugin otherwise. Smooth sailing in every way until upgrading
PostgreSQL from 8.1 to 8.2.



Garrett

Post by Aliacta Support
Hi Garrett,
Are you using the latest pgSQL4RB? (v2fc3)
Marc

Post by Garrett Kalleberg
Similar problem with ByteA? PostgreSQL 8.2 on OS X Server 10.4.8,
database is UTF-8, I'm getting "invalid byte sequence" messages. Any
direction here?

_______________________________________________
Postgresql mailing list
http://aliacta.com/mailman/listinfo/postgresql_aliacta.com

[Aliacta Support]

Try the alternative global method in the attached module for your escaping.

Marc

[Garrett Kalleberg]

Changed my
sDataEscaped = App.DatabasePgSQL.pgSQLescapeBytea(sData)
to
sDataEscaped = pgSQLalternateEscapeBytea(sData)
and it seems to be working.

Thank you!

Should this be regarded as a temporary or a permanent fix?


Garrett

Post by Aliacta Support
Try the alternative global method in the attached module for your escaping.
Marc
<pgSQLalternateEscaping3.zip>
_______________________________________________
Postgresql mailing list
http://aliacta.com/mailman/listinfo/postgresql_aliacta.com

[Aliacta Support]

Post by Garrett Kalleberg
Changed my
sDataEscaped = App.DatabasePgSQL.pgSQLescapeBytea(sData)
to
sDataEscaped = pgSQLalternateEscapeBytea(sData)
and it seems to be working.
Thank you!
Should this be regarded as a temporary or a permanent fix?

This is integrated in v2 now but for v1 you should regard it as the final fix.

Marc