Search code examples
pythonpypyodbc

Python - special characters in SQL statement

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.

Solution

  • 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()