I am connecting to MS Access using ODBC (Python ODBC module ). There is one part of the code which put some values into DB. Looks similar to this:
for item in changes:
format_str = """INSERT INTO changes (short_description) VALUES ('{short_description}');"""
sql_command = format_str.format(short_description =item.short_description)
cursor.execute(sql_command)
cursor.commit()
The problem is that it returns syttaxt error:
pypyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression
I found that it is because for one the cases I have short_description like this one:
The Service Request status is not changing to \"OPEN', once dispatched to another group
the problem is for " ' " after OPEN.
to provide you full picture here. Actually the string I see is this:
The Service Request status is not changing to "OPEN', once dispatched to another group
The string with "\" I get from the API for the application which serves the data. It adds "\" to escape string, but not everywhere.
The question is - what will be the simplest way to solve it? Theoretically I could replace\remove unwanted signs, but what in case I want to keep it as it is?
For any other cases all works fine.
You should to use parameters to avoid sql injection:
for item in changes:
sql_command = """INSERT INTO changes (short_description) VALUES (?);"""
cursor.execute(sql_command, (item.short_description,) )
cursor.commit()