Re: [pgAdmin] RM6018 Encoding issue updating database - Mailing list pgadmin-hackers

Hi Team,

This is regarding RM6018 when a user sets the database encoding to SQL_ASCII and names the column with ascii characters.

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.