public inbox for [email protected]
help / color / mirror / Atom feedFrom: Rahul Shirsat <[email protected]>
To: Akshay Joshi <[email protected]>
Cc: Khushboo Vashi <[email protected]>
Cc: pgadmin-hackers <[email protected]>
Subject: Re: [pgAdmin] RM6018 Encoding issue updating database
Date: Wed, 24 Feb 2021 17:17:44 +0530
Message-ID: <CAKtn9dNYg8dkOGp0fahi0tqVzB7UEJhaB+Cx34tAytpDc1ds3A@mail.gmail.com> (raw)
In-Reply-To: <CANxoLDdBwYv6kCBd9XumaeLd2beqZxy6tPeDop7fz8yhdp0o3g@mail.gmail.com>
References: <CAKtn9dNGV9xsxwdP2awL-JGRMt0CnaMCYC7fCAqrJ_xr7qFDxg@mail.gmail.com>
<CANxoLDdBwYv6kCBd9XumaeLd2beqZxy6tPeDop7fz8yhdp0o3g@mail.gmail.com>
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 protected]>
wrote:
> Hi Khushboo
>
> Can you please review this patch?
>
> On Wed, Feb 17, 2021 at 2:10 PM Rahul Shirsat <
> [email protected]> 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.
Attachments:
[image/png] image.png (151.5K, 3-image.png)
download | view image
[application/octet-stream] RM6018.patch (2.2K, 4-RM6018.patch)
download | inline diff:
diff --git a/web/pgadmin/tools/sqleditor/__init__.py b/web/pgadmin/tools/sqleditor/__init__.py
index 40442ff5e..0fa35ca06 100644
--- a/web/pgadmin/tools/sqleditor/__init__.py
+++ b/web/pgadmin/tools/sqleditor/__init__.py
@@ -453,7 +453,9 @@ def poll(trans_id):
# Using characters %, (, ) in the argument names is not
# supported in psycopg2
col_info['pgadmin_alias'] = \
- re.sub("[%()]+", "|", col_name)
+ re.sub("[%()]+", "|", col_name).\
+ encode('unicode_escape').decode('utf-8')
+
session_obj['columns_info'] = columns
# status of async_fetchmany_2darray is True and result is none
diff --git a/web/pgadmin/tools/sqleditor/tests/test_encoding_charset.py b/web/pgadmin/tools/sqleditor/tests/test_encoding_charset.py
index a0964cce3..c68c5fbbd 100644
--- a/web/pgadmin/tools/sqleditor/tests/test_encoding_charset.py
+++ b/web/pgadmin/tools/sqleditor/tests/test_encoding_charset.py
@@ -45,6 +45,13 @@ class TestEncodingCharset(BaseTestGenerator):
lc_collate='C',
test_str='Tif'
)),
+ (
+ 'With Encoding SQL_ASCII (additional test)',
+ dict(
+ db_encoding='SQL_ASCII',
+ lc_collate='C',
+ test_str='ü'
+ )),
(
'With Encoding LATIN1',
dict(
diff --git a/web/pgadmin/tools/sqleditor/tests/test_sql_ascii_encoding.py b/web/pgadmin/tools/sqleditor/tests/test_sql_ascii_encoding.py
index a71dfaad0..e25769a9c 100644
--- a/web/pgadmin/tools/sqleditor/tests/test_sql_ascii_encoding.py
+++ b/web/pgadmin/tools/sqleditor/tests/test_sql_ascii_encoding.py
@@ -55,6 +55,14 @@ class TestSQLASCIIEncoding(BaseTestGenerator):
lc_collate='C',
test_str='Blob: \xf4\xa5\xa3\xa5'
)),
+ (
+ 'Test SQL_ASCII data with blob string & ascii table name',
+ dict(
+ table_name='ü',
+ db_encoding='SQL_ASCII',
+ lc_collate='C',
+ test_str='Blob: \xf4\xa5\xa3\xa5'
+ )),
]
def setUp(self):
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], [email protected], [email protected]
Subject: Re: [pgAdmin] RM6018 Encoding issue updating database
In-Reply-To: <CAKtn9dNYg8dkOGp0fahi0tqVzB7UEJhaB+Cx34tAytpDc1ds3A@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