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

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.

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