How to insert strings with quotes and newlines into sqlite db with Python?
You use SQL parameters instead of string formatting:
c.execute("INSERT INTO test VALUES (?, 'bar')", (testfield,))When using SQL parameters you let the database library handle the quoting, and even better, give the 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 defended against SQL injection attacks as the database library knows best how to escape dangerous SQL-like values.
To quote the sqlite3 documentation:
Usually your SQL operations will need to use values from Python variables. You shouldn’t assemble your query using Python’s string operations because doing so is insecure; it makes your program vulnerable to an SQL injection attack (see http://xkcd.com/327/ for humorous example of what can go wrong).
Instead, use the DB-API’s parameter substitution. Put
?as a placeholder wherever you want to use a value, and then provide a tuple of values as the second argument to the cursor’sexecute()method.