public inbox for [email protected]  

help / color / mirror / Atom feed

From: Rahul Shirsat <[email protected]>
To: pgadmin-hackers <[email protected]>
Subject: [pgAdmin] RM6018 Encoding issue updating database
Date: Wed, 17 Feb 2021 14:09:29 +0530
Message-ID: <CAKtn9dNGV9xsxwdP2awL-JGRMt0CnaMCYC7fCAqrJ_xr7qFDxg@mail.gmail.com> (raw)

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.


Attachments:

  [image/png] image.png (151.5K, 3-image.png)
  download | view image

view thread (4+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected]
  Subject: Re: [pgAdmin] RM6018 Encoding issue updating database
  In-Reply-To: <CAKtn9dNGV9xsxwdP2awL-JGRMt0CnaMCYC7fCAqrJ_xr7qFDxg@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox