Python - Sqlite - OperationalError: near "s": syntax error [duplicate]

Question

I'm getting the following error when trying to add value to a table:

line 33, in insert
    c.execute("INSERT INTO {tn} (Process, Info) VALUES('{proc}', 
'{inf}')".format(tn=table_name, proc=proc, inf=content))
OperationalError: near "s": syntax error

This is happening when using certain text, if I write something regular there is no issue, but for example something like that:

#. My item number one
#. My item number two with some more content
    and it's continuing on the second line?
#. My third item::
    Oh wait, we can put code!
#. My four item::
    No way.
.. _bottom:
    Go to top_'''

It is failing.. This is the code I am using:

def insert(table_name, proc, content):

    conn = sqlite3.connect(sqlite_file)
    conn.text_factory = str
    c = conn.cursor()

    c.execute("INSERT INTO {tn} (Process, Info) VALUES('{proc}', 
              '{inf}')".format(tn=table_name, proc=proc, inf=content))

    conn.commit()
    conn.close()

Appreciate your help guys :)

Answer 1

The syntax error is caused by you interpolating data containing metacharacters into your SQL query. In your specific example, your data contains a ' character, and that signals the end of a string. The next character, s is then a syntax error.

Do not use str.format() to put your data into a query. Use SQL parameters and leave proper escaping to the database driver:

c.execute("INSERT INTO {tn} (Process, Info) VALUES(?, ?)".format(tn=table_name),
          (proc, content))

The two ? characters act as placeholders for the database driver to insert the values from the (proc, content) tuple. The driver will take care of properly escaping the values.

Because SQL parameters can only be used for values, not object names such as tables, you still would use string formatting to insert the table name. You need to make 100% certain that you don't accept arbitrary untrusted data for the table_name variable. Vet that data against a list of valid table names first, for example.