Re: [pgAdmin] RM6018 Encoding issue updating database

Thanks, patch applied.

On Wed, Feb 24, 2021 at 5:18 PM Rahul Shirsat <
rahul(dot)shirsat(at)enterprisedb(dot)com> wrote:

> 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.
>

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

*Mobile: +91 976-788-8246*