Page 1 of 1

Patch to 6.6.6 fixing single quote becoming \047

Posted: 10.05.2013, 09:57
by cnliou
Newer versions of PostgreSQL appears to handle escape syntax differently from older ones. Version 9.2 takes literals like these two SQL text:

INSERT INTO t1 VALUES (E'This is Jim\'s home.')

INSERT INTO t1 VALUES (E'This is Jim\047s home.')

Take special note on the prefixing character "E"!
Without the leading 'E' like the following SQL text

INSERT INTO t1 VALUES ('This is Jim\047s home.')

saves

Code: Select all

This is Jim\047s home.
in t1.c1. This is exactly the symptom reported at: http://zeos.firmos.at/viewtopic.php?t=3 ... d62046a3cc

This file simply adds a leading 'E' to literal strings containing any occurrence of characters \0, \, and '.

Only

Code: Select all

function EncodeString(CharactersetCode: TZPgCharactersetType; Value: string): string;
is modified. I leave

Code: Select all

function EncodeString(Value: string): string;
as is because I suspect it is obsolete.

Perhaps

Code: Select all

function EncodeBinaryString(Value: string): string;
in version 6.6.6 and

Code: Select all

function PGEscapeString(Handle: Pointer; const Value: ZAnsiString;
    ConSettings: PZConSettings; WasEncoded: Boolean = False): ZAnsiString;
in version 7.1 also need the similar revision.

I am not sure setting standard_conforming_strings (boolean) in postgresql.conf to off is a quick and dirty fix to this issue.

Furthuer, perhaps the best approach is revising functions EncodingString() in v 6.6.6 and PGEscapeString and v7.1 to call the new escape functions supported by newer libpq instead:

Code: Select all

char *PQescapeLiteral(PGconn *conn, const char *str, size_t length);

char *PQescapeIdentifier(PGconn *conn, const char *str, size_t length);

size_t PQescapeStringConn(PGconn *conn, char *to, const char *from, size_t length, int *error);