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.gad...@enterprisedb.com> wrote:
> @Dave Page <dave.p...@enterprisedb.com> @Akshay Joshi
> <akshay.jo...@enterprisedb.com> your input please?
>
> On Wed, Apr 15, 2020 at 3:13 PM Neel Patel <neel.pa...@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.va...@enterprisedb.com> wrote:
>>
>>>
>>>
>>> On Wed, Apr 15, 2020 at 2:48 PM navnath gadakh <
>>> navnath.gad...@enterprisedb.com> wrote:
>>>
>>>> Hello Hackers,
>>>>
>>>>
>>>> On Tue, Apr 14, 2020 at 5:14 PM Khushboo Vashi <
>>>> khushboo.va...@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.gad...@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
