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.
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
