Incorrect syntax near 's' in pyodbc

0
votes

Sometimes I get this error randomly when I try to insert data in to the database. I fetch the data with using request.get and parsing the JSON data.

This is the error that I get:

pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near 's'. (102) (SQLExecDirectW); [42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Unclosed quotation mark after the character string ')'. (105)")

This is my code:

for product in parsed['products']:
                cursor.execute("INSERT INTO dbo.producten (productid, datum_toegevoegd, naam, prijs_excl, prijs_incl, gewicht) VALUES ('%s','%s','%s','%s','%s','%s')" %(product['id'],product['created_at'], product['nl']['title'],product['price_excl'],product['price_incl'],product['weight']))
2
pythonsqljsonsql-serverpyodbc
Try constructing the query string (without executing it) and printing it so you can see what is actually being passed to the SQL server. That way you can debug what is being added in the string. - rst-2cv
Using string formatting % in sql is really really not recommended. This kind of thing opens up a whole class of security vulnerabilities called SQL injection. The database library should should provide a safe alternative to raw string formatting. - Håken Lid

2 Answers

3
votes

You must not use string interpolation for SQL queries. The db-api will do correct parameter substitution for you - replace that % with a comma.

cursor.execute('SELECT.... ', (product['id'],product['created_at'...))
#                           ^
1
votes
cursor.execute('SELECT.... product = ?', (value_for_product))

works for python 3.^