Re: [pgAdmin] RM6018 Encoding issue updating database

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*