psycopg2.ProgrammingError: syntax error at or near "st"\r,

2
votes

Here i need insert a some values into postgresql table in python.

I tried below code, but a error which is "psycopg2.ProgrammingError: syntax error at or near "st"\r, referer: http://localhost:8080/"

conn = psycopg2.connect(database="Test", user="dev", password="123456", host="192.168.1.104", port="5432")
cursor = conn.cursor()

cursor.execute('''INSERT INTO signup (id, name, email, dob, address, mobile, password) VALUES (1,%s,%s,%s,%s,%s,%s)''' % (name,email,dob,address,mobile,password))
conn.commit()

Please solve this issue, Thanks in advance.....

2
pythoninsertpsycopg2
Little Bobby Tables would like a word with you. - Peter Wood
Their has to be a comma (,) between ,%s,%s)''', % (name,em - Prashant Shukla

2 Answers

4
votes

You have a carriage return in one or more parameters. And because you are using parameter interpolation, this breaks the query string. But the bigger problem with parameter interpolation, is that this code is vulnerable to SQL injection.

First, read this: http://initd.org/psycopg/docs/usage.html#the-problem-with-the-query-parameters Then this: http://initd.org/psycopg/docs/usage.html#passing-parameters-to-sql-queries and then, rewrite your code to:

cursor.execute('''INSERT INTO signup (id, name, email, dob, address, mobile, password) VALUES (1,%s,%s,%s,%s,%s,%s)''',  (name,email,dob,address,mobile,password))

now you can pass "\r" to the database if you like, and you are also safe from SQL injection.

0
votes

Check the values of all of these variables. Some or all of them probably contain '\r'.

Edit: given that the error says " syntax error at or near “st”\r," it is probably the address variable.