CSc 250: Lecture Notes: sqlite3 Parameter Substitution

We’ve learned about how executing SQ statements using “raw” string-building can be dangerous. Let’s learn a safer way.

Parameter Substitution

Instead of building queries with normal string concatenation, we need to use a technique called parameter substitution. Doing this is a several-step process:

• Create a SQL query string, but wherever we want to use a value from a variable, just put a ?
• Build a tuple containing all of the python values to use in the query
• Pass the query string and the tuple to the execute() function

Before showing specifically how this works, we need to talk just a bit about tuples.

Tuples

We’ve already learned about several commonly-used python types such as lists, dictionaries, and strings. A tuple is yet another type we will discuss and use in this class. A tuple is a compound type, meaning that a variable of this type can store multiple other values within it (just like lists and dictionaries).

A tuple is actually extremely similar to a list. As with a list, a tuple is an ordered sequence of zero or more values.

Declaring a list looks like:

>>> lnums = [5, 10, 20, 50, 100]
>>> lstrs = ['how', 'are', 'you']
>>> lvals = [5, 'how', 10, 'are', 20, 'you']
>>> type(lnums)
<type 'list'>
>>> type(lstrs)
<type 'list'>
>>> type(lvals)
<type 'list'>

We can easily declare tuples with the same values in them. The only difference is that with tuples, you use parentheses (()) instead of square-brackets([]).

>>> numbers = (5, 10, 20, 50, 100)
>>> strings = ('how', 'are', 'you')
>>> values = (5, 'how', 10, 'are', 20, 'you')
>>> type(numbers)
<type 'tuple'>
>>> type(strings)
<type 'tuple'>
>>> type(values)
<type 'tuple'>

We can use the same curly-bracket syntax for accessing the elements in a tuple:

>>> lnums[3]
50
>>> numbers[3]
50

>>> lvals[1]
'how'
>>> values[1]
'how'

Other than having a slightly-different syntax for declaring them, lists and tuples seem identical! So what is the point of having both?

The difference between the two is that tuples cannot be modified after they are created. We’ve learned about several ways to add elements to a list, such append and extend:

>>> print(lnums)
[5, 10, 20, 50, 100]
>>> lnums.append(200)
>>> print(lnums)
[5, 10, 20, 50, 100, 200]
>>> lnums.extend(lstrs)
>>> print(lnums)
[5, 10, 20, 50, 100, 200, 'how', 'are', 'you']

Values at indexes in a list may also be changed after inserting them:

>>> lnums[3] = 50000
>>> print(lnums)
[5, 10, 20, 50000, 100, 200, 'how', 'are', 'you']
>>> lnums[3] = 789
>>> print(lnums)
[5, 10, 20, 789, 100, 200, 'how', 'are', 'you']

All of these operations change the contents in the list. With a tuple, these opperations are illegal:

>>> numbers.append(200)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
AttributeError: 'tuple' object has no attribute 'append'

>>> numbers.extend(lstrs)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
AttributeError: 'tuple' object has no attribute 'extend'

>>> numbers[3] = 789
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
TypeError: 'tuple' object does not support item assignment`

If you already understand lists well, tuples are not hard to understand. The two differences are:

• When declaring a tuple variable, use parentheses instead of curly-braces
• Tuples cannot be modified!

That’s it!

Back to Parameter Substitution

In the injection lecture notes, we saw the following example of running a SQL query with execute() and normal string concatenation:

import sys
import sqlite3

print('Enter a city:')
city = sys.stdin.readline().strip()
print('Enter the population:')
pop = sys.stdin.readline().strip()

conn = sqlite3.connect('citydb')
query = "INSERT INTO city VALUES ('" + city + "', " + population + ")"
res = conn.execute(query)

The safer way to do this, using parameter substitution is:

import sys
import sqlite3

print('Enter a city:')
city = sys.stdin.readline().strip()
print('Enter the population:')
pop = sys.stdin.readline().strip()

conn = sqlite3.connect('citydb')
query = "INSERT INTO city VALUES (?, ?)"
params = (city, pop)
res = conn.execute(query, params)

When using parameter substitution, the sqlite3 module makes sure that the values passed to the query will not be able to execute any malicious queries.