[pgAdmin] RM6018 Encoding issue updating database

Hi Team,

This is regarding RM6018 <https://redmine.postgresql.org/issues/6018> when
a user sets the database encoding to SQL_ASCII and names the column with
ascii characters.

[image: image.png]

Code is written for escaping the ascii characters and getting its escaped
value (here for È its encoding escaped value is \\xc8)

I am trying to update È column for the second row. After encoding &
decoding the column name generated so is \\xc8

The actual issue is the dictionary key as a parameter passed for query
execution.

params = { '\\xc8' : 'some_value' }

query = b'UPDATE public."\xfc" SET "\xc8" = %(\xc8)s WHERE "\xfc" = \'xyz\''

query = query.encode(self.python_encoding)

params = self.escape_params_sqlascii(params)

cur.execute(query, params)

As the cur.execute is unable to replace params in the query, it throws an
error.

The tried solutions are:

1. Tried to have \xc8 as a key, but python internally read it as È, so
fails.
2. Tried placing the key as byte b'\xc8', doesn't work.
3. Tried to change the \xc8 to \\xc8 inside the query, but risk involves
query tampering.
4. Tried including BYTES / BYTESARRAY typecasters according to psycopg
docs https://www.psycopg.org/docs/usage.html#unicode-handling, still
didn't work.

Examples given in psycopg docs for Unicode handling involve only record
values passed & not the column names itself.

Any other suggestions on how we can handle this?

--
*Rahul Shirsat*
Senior Software Engineer | EnterpriseDB Corporation.