Subject: [pgAdmin] RM6018 Encoding issue updating database

From: Rahul Shirsat <[email-masked]>
Date: Wed, 17 Feb 2021 14:09:29 +0530

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.

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.

---

From: Akshay Joshi <[email-masked]>
Date: Thu, 18 Feb 2021 11:46:07 +0530

Hi Khushboo

Can you please review this patch?

On Wed, Feb 17, 2021 at 2:10 PM Rahul Shirsat <
[email-masked]> wrote:

> 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.
>
>
>    3. Tried to change the \xc8 to \\xc8 inside the query, but risk
>    involves query tampering.

-- 
*Thanks & Regards*
*Akshay Joshi*
*pgAdmin Hacker | Principal Software Architect*
*EDB Postgres <http://edbpostgres.com>*

*Mobile: +91 976-788-8246*

---

From: Rahul Shirsat <[email-masked]>
Date: Wed, 24 Feb 2021 17:17:44 +0530

Hi Hackers,

Please find the attached patch which resolves the encoding issue updating
database.

On Thu, Feb 18, 2021 at 11:46 AM Akshay Joshi <[email-masked]>
wrote:

>>    1. Tried to have \xc8 as a key, but python internally read it as È,
>>    so fails.
>>    4. Tried including BYTES / BYTESARRAY typecasters according to
>>    psycopg docs https://www.psycopg.org/docs/usage.html#unicode-handling,
>>    still didn't work.
>>

---

From: Akshay Joshi <[email-masked]>
Date: Fri, 26 Feb 2021 13:23:55 +0530

Thanks, patch applied.

On Wed, Feb 24, 2021 at 5:18 PM Rahul Shirsat <
[email-masked]> wrote:

> On Thu, Feb 18, 2021 at 11:46 AM Akshay Joshi <
> [email-masked]> wrote:
>
>> Hi Khushboo
>>
>>> Code is written for escaping the ascii characters and getting its
>>> escaped value (here for È its encoding escaped value is \\xc8)
>>>
>>> As the cur.execute is unable to replace params in the query, it throws
>>> an error.
>>>
>>> The tried solutions are:
>>>