public inbox for [email protected]
help / color / mirror / Atom feed[pgAdmin] RM6018 Encoding issue updating database
4+ messages / 2 participants
[nested] [flat]
* [pgAdmin] RM6018 Encoding issue updating database
@ 2021-02-17 08:39 Rahul Shirsat <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Rahul Shirsat @ 2021-02-17 08:39 UTC (permalink / raw)
To: pgadmin-hackers
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [pgAdmin] RM6018 Encoding issue updating database
@ 2021-02-18 06:16 Akshay Joshi <[email protected]>
parent: Rahul Shirsat <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Akshay Joshi @ 2021-02-18 06:16 UTC (permalink / raw)
To: Khushboo Vashi <[email protected]>; +Cc: pgadmin-hackers
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*
Attachments:
[image/png] image.png (151.5K, 3-image.png)
download | view image
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [pgAdmin] RM6018 Encoding issue updating database
@ 2021-02-24 11:47 Rahul Shirsat <[email protected]>
parent: Akshay Joshi <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Rahul Shirsat @ 2021-02-24 11:47 UTC (permalink / raw)
To: Akshay Joshi <[email protected]>; +Cc: Khushboo Vashi <[email protected]>; pgadmin-hackers
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):
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [pgAdmin] RM6018 Encoding issue updating database
@ 2021-02-26 07:53 Akshay Joshi <[email protected]>
parent: Rahul Shirsat <[email protected]>
0 siblings, 0 replies; 4+ messages in thread
From: Akshay Joshi @ 2021-02-26 07:53 UTC (permalink / raw)
To: Rahul Shirsat <[email protected]>; +Cc: Khushboo Vashi <[email protected]>; pgadmin-hackers
Thanks, patch applied.
On Wed, Feb 24, 2021 at 5:18 PM Rahul Shirsat <
[email protected]> 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 <
> [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.
>
--
*Thanks & Regards*
*Akshay Joshi*
*pgAdmin Hacker | Principal Software Architect*
*EDB Postgres <http://edbpostgres.com>*
*Mobile: +91 976-788-8246*
Attachments:
[image/png] image.png (151.5K, 3-image.png)
download | view image
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2021-02-26 07:53 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-02-17 08:39 [pgAdmin] RM6018 Encoding issue updating database Rahul Shirsat <[email protected]>
2021-02-18 06:16 ` Akshay Joshi <[email protected]>
2021-02-24 11:47 ` Rahul Shirsat <[email protected]>
2021-02-26 07:53 ` Akshay Joshi <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox