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

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
>