0

What is the proper way to escape input using pyodbc or pypyodbc?

I have two examples using ? and using %s. Which is preferred, and why don't both examples work?

First example using ?

cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=127.0.0.1;DATABASE=db;UID=sa;PWD=password')
cursor = cnxn.cursor()
sql = "insert into table(col1,col2) values(?,?)"
cursor.execute(sql,(val1,val2))

Second example using %s

cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=127.0.0.1;DATABASE=db;UID=sa;PWD=password')
cursor = cnxn.cursor()
sql = "insert into table(col1,col2) values(%s,%s)" % (val1,val2)
cursor.execute(sql)
Bryan
  • 17,112
  • 7
  • 57
  • 80
dev
  • 1,119
  • 1
  • 11
  • 34
  • 1
    The first one should work, and it is the only one you should use. Don't use string formatting to create sql queries, you _will_ introduce vulnerabilities. You should post what errors you get when executing the first query. – mata Oct 05 '16 at 08:53
  • "does not work"... how does it not work? Is there an exception or error message? The first example using the parameterised query should work - so the exception details is important. The second example using string interpolation will fail if the values need to be quoted, e.g. a string. Anyway, don't use string interpolation as it is vulnerable to SQL injection. Post the traceback and/or error message. – mhawke Oct 05 '16 at 08:58
  • Thank you for your answer. I will use parametrized queries a.k.a ?. Error was in german, so I didnt want to post it. I figured in out. BIG THANKS!!! – dev Oct 05 '16 at 09:25

0 Answers0