How to insert quoted strings and newlines in sqlite db with Python?

Question

I am trying to insert the lines read from a file into the sqlite database in Python. Lines have spaces (newlines, tabs, and spaces), and also have single or double quotation marks. Here is how I am trying to do this:

 import sqlite3 conn = sqlite3.connect('example.db') c = conn.cursor() # Create table c.execute('''CREATE TABLE test (a text, b text)''') f = open("foo", "w") f.write("hello\n\'world\'\n") f.close() testfield = open("foo").read() # Insert a row of data c.execute("INSERT INTO test VALUES ('%s', 'bar')" %(testfield)) # Save (commit) the changes conn.commit() 

I found that this is an error with the error:

  c.execute("INSERT INTO test VALUES ('%s', 'bar')" %(testfield)) sqlite3.OperationalError: near "world": syntax error 

How can i achieve this? Do i need to escape the lines before inserting into db, and if so, how? thanks.

Answer 1

You use SQL parameters instead of string formatting:

 c.execute("INSERT INTO test VALUES (?, 'bar')", (testfield,)) 

When using SQL parameters, you allow the database library to process quotes and even better provide a database to optimize the query and reuse the optimized query plan for multiple executions of the same basic query (with different parameters).

Last but not least, you are much better at defending against SQL injection attacks, since the database library knows better how to avoid dangerous SQL values.

To submit sqlite3 documentation:

Usually your SQL operations should use values ​​from Python variables. You should not collect your request using Pythons string operations, because this is unsafe; this makes your program vulnerable to SQL injection attack (see http://xkcd.com/327/ for a humorous example of what might go wrong).

Use DB-API parameter substitution instead. Place ? as a placeholder wherever you want to use a value, and then specify a tuple of values ​​as the second argument to the execute() cursor method.