Re: [pgAdmin][RM5210] pgAdmin4 silently truncates text larger than underlying field size

@Dave Page <dave(dot)page(at)enterprisedb(dot)com> @Akshay Joshi
<akshay(dot)joshi(at)enterprisedb(dot)com> your input please?

On Wed, Apr 15, 2020 at 3:13 PM Neel Patel <neel(dot)patel(at)enterprisedb(dot)com>
wrote:

> Hi,
>
> I think we should remove the type cast from query during update and
> whatever error is thrown should be shown to UI as per scenario 3.
>
> Thanks,
> Neel Patel
>
> On Wed, Apr 15, 2020 at 3:06 PM Khushboo Vashi <
> khushboo(dot)vashi(at)enterprisedb(dot)com> wrote:
>
>>
>>
>> On Wed, Apr 15, 2020 at 2:48 PM navnath gadakh <
>> navnath(dot)gadakh(at)enterprisedb(dot)com> wrote:
>>
>>> Hello Hackers,
>>>
>>>
>>> On Tue, Apr 14, 2020 at 5:14 PM Khushboo Vashi <
>>> khushboo(dot)vashi(at)enterprisedb(dot)com> wrote:
>>>
>>>> Hi Navnath,
>>>>
>>>> You have compared the column's internal size with the length of the
>>>> value given by the user.
>>>> For example, column having integer would have internal size 4 and if I
>>>> give the value 12121 which is the correct input for the field will fail
>>>> here because as per your logic column internal size (4) < len(value) (5).
>>>>
>>>> I think this implementation is not correct here.
>>>>
>>> Yes, my implementations might be wrong.
>>>
>>> Below are some important findings on the parameterised query(as we are
>>> using Jinja templates for building SQL queries).
>>> Here I have created a table 'account' with some records in it.
>>> CREATE TABLE public.account
>>> (
>>> user_id integer NOT NULL,
>>> username character varying(5)
>>> )
>>>
>>> psycopg2 throws a proper error if I pass username value greater than the
>>> length of the data type(5)
>>> Now, I want to pass username value greater than data type length (5)
>>>
>>> Scenario 1: Query with data type and length
>>>
>>> import psycopg2
>>> try:
>>> conn = psycopg2.connect("dbname='postgres' user='postgres' host='XXX.XXX.XXX.XXX' password='test' port=5432")
>>> cur = conn.cursor()
>>> cur.execute("UPDATE public.account SET username = %(username)s::character varying(5) WHERE user_id = 1;", {"username": "username-test-123"})
>>> cur.execute("COMMIT;")
>>> except Exception as e:
>>> print('Exception : {0}'.format(e))
>>>
>>> *Output:*
>>>
>>> It will save the record with 5 char data without any error.
>>>
>>> *psql output:*
>>>
>>> postgres=# select * from public.account;
>>> user_id | username
>>> ---------+----------
>>> 1 | usern
>>> (1 row)
>>>
>>> Scenario 2: Query with only data type
>>>
>>> import psycopg2
>>> try:
>>> conn = psycopg2.connect("dbname='postgres' user='postgres' host='XXX.XXX.XXX.XXX' password='test' port=5432")
>>> cur = conn.cursor()
>>> cur.execute("UPDATE public.account SET username = %(username)s::character varying WHERE user_id = 1;", {"username": "username-test-123"})
>>> cur.execute("COMMIT;")
>>> except Exception as e:
>>> print('Exception : {0}'.format(e))
>>>
>>> *Output:*
>>>
>>> Exception : value too long for type character varying(5)
>>>
>>> data will not save in the table.
>>>
>>> We can consider scenario 2 as it will throw the valid exception and
>> also typecast the value in the proper format.
>>
>>> Scenario 3: Query without data type
>>>
>>> import psycopg2
>>> try:
>>> conn = psycopg2.connect("dbname='postgres' user='postgres' host='XXX.XXX.XXX.XXX' password='test' port=5432")
>>> cur = conn.cursor()
>>> cur.execute("UPDATE public.account SET username = %(username)s WHERE user_id = 1;", {"username": "username-test-123"})
>>> cur.execute("COMMIT;")
>>> except Exception as e:
>>> print('Exception : {0}'.format(e))
>>>
>>> *Output:*
>>>
>>> Exception : value too long for type character varying(5)
>>>
>>> again data will not save in the table.
>>>
>>> These are some different behaviours with psycopg2. So to complete this patch which apporach should I follow? or any new approach is also welcome.
>>>
>>> Thanks!
>>>
>>>
>>>
>>>>
>>>> Thanks,
>>>> Khushboo
>>>>
>>>>
>>>>
>>>> On Tue, Apr 14, 2020 at 4:33 PM navnath gadakh <
>>>> navnath(dot)gadakh(at)enterprisedb(dot)com> wrote:
>>>>
>>>>> Hello Hackers,
>>>>> Please find the attached patch for below fixes:
>>>>>
>>>>> - Added validation for table row data that should not be larger
>>>>> than the field size.
>>>>> - Rearrange the existing functions to add validation.
>>>>> - Added test cases.
>>>>>
>>>>> Regards,
>>>>> Navnath Gadakh
>>>>>
>>>>>
>>>
>>> --
>>> Regards,
>>> Navnath Gadakh
>>>
>>

--
Regards,
Navnath Gadakh