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

Question

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

Solution

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.