2

Im not sure exactly whats happening here but it might have something to do with format in python. Running this causes an error.

x = '00000201000012EB'
sql = """ SELECT * FROM table WHERE id = {} """.format(x)
conn.execute(sql)

I get an error saying: syntax error near "EB"

however when i run the command like this:

sql = """ SELECT * FROM table WHERE id = '00000201000012EB' """
conn.execute(sql)

It works fine.

Is there something wrong with the way im formatting this sql statement?

Improve this question

2 Answers 2

Reset to default
2

Use the variable as an argument to execute():

cur.execute(""" SELECT * FROM my_table WHERE id = %s """, (x,))

If you are determined to use format(), you should add single quotes around the placeholder:

sql = """ SELECT * FROM my_table WHERE id = '{}' """.format(x)
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

i like it! The reason i'm using .format is because the input changes, i was just doing this as a simpler example. excellent answer sir
The two options are somehow similar, but generally execute() with arguments is a bit safer in context of potential SQL injection. See e.g. psycopg2 and SQL injection security.
I don't know much about SQL injection, but if you say the first option is safer, i'll make the changes to my code =)
0

Believe it or not it was fixed by adding more quotes to the string.

this finally worked.

x = '00000201000012EB'
sql = """ SELECT * FROM table WHERE id = {} """.format("'" + x + "'")

Since the sql statement required another set of quotes i just added them to ensure it was treated as its own string.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.