Re: [pgAdmin][RM5210] pgAdmin4 silently truncates text larger thanunderlying field size - Mailing list pgadmin-hackers

Removing the typecast will almost certainly lead to other problems. I think we should just remove the length from it.

On Wed, Apr 15, 2020 at 1:33 PM navnath gadakh <navnath.gadakh@enterprisedb.com> wrote:

@Dave Page  @Akshay Joshi your input please?

On Wed, Apr 15, 2020 at 3:13 PM Neel Patel <neel.patel@enterprisedb.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.vashi@enterprisedb.com> wrote:

On Wed, Apr 15, 2020 at 2:48 PM navnath gadakh <navnath.gadakh@enterprisedb.com> wrote:

Hello Hackers,

On Tue, Apr 14, 2020 at 5:14 PM Khushboo Vashi <khushboo.vashi@enterprisedb.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.gadakh@enterprisedb.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

--

Dave Page
VP & Chief Architect, Database Infrastructure
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake