Re: [pgAdmin] RM6018 Encoding issue updating database

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 <akshay(dot)joshi(at)enterprisedb(dot)com>
wrote:

> Hi Khushboo
>
> Can you please review this patch?
>
> On Wed, Feb 17, 2021 at 2:10 PM Rahul Shirsat <
> rahul(dot)shirsat(at)enterprisedb(dot)com> 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.
>>
>> [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.
>>
>
>
> --
> *Thanks & Regards*
> *Akshay Joshi*
> *pgAdmin Hacker | Principal Software Architect*
> *EDB Postgres <http://edbpostgres.com>*
>
> *Mobile: +91 976-788-8246*
>

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