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.
Admin
Updated on July 22, 2022Comments
-
Admin almost 2 years
I'm trying to insert strings read from a file into an
sqlite
database in Python. The strings have whitespace (newline, tab characters, and spaces) and also have appearances of single or double quotes. Here's how I try to do it: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 find that this fails with the error:
c.execute("INSERT INTO test VALUES ('%s', 'bar')" %(testfield)) sqlite3.OperationalError: near "world": syntax error
How can I achieve this? Do the strings need to be escaped before insertion in the db, and if so how? thanks.
-
abarnert about 11 yearsThis site needs a +n button, so I can +1 for the answer, +1 for explaining all of the advantages of SQL parameters, and +1 for the link to xkcd…
-
Jason Sperske about 11 yearsWhat is the significance of the trailing comma after textfield?
-
Martijn Pieters about 11 yearsIn this case, the link came with the quote. :-)
-
Martijn Pieters about 11 years@JasonSperske: I'm making sure the second parameter is a tuple with one element. Leave out the comma and it's just an expression grouping.
-
Admin about 11 yearsWhy can't this be used as
c.execute("CREATE TABLE ? (a text, b text)", ("test",))
? Is?
not allowed in creation of tables? -
Martijn Pieters about 11 years@user248237: parameters are only meant for values. Table names are identifiers, not values. Your parameter is being escaped by the database engine, so the end result is
CREATE TABLE 'test' (a text, b text)
which is not valid SQL. This is a good thing. -
Admin about 11 years@MartijnPieters: so if I want to create a table name based on a variable value, then I should use regular string formatting in Python?
-
Martijn Pieters about 11 years@user248237: Then you should rethink what you are doing and design a schema that can handle that without using dynamic DDL. Or use SQLAlchemy to generate your SQL safely.