Python SQLite3 SQL Injection Vulnerable Code

Question

I know that the code snippets below are vulnerable to SQL Injection because of the .format, but i do not know why. Does anyone understand why this code is vulnerable and where i would start to fix it? I am aware that these code snippets leave the input fields open to execute other malicious commands via SQL Injection but don't know why

cursor.execute("insert into user(username, password)"
         "  values('{0}', '{1}')".format(username, password))

handle[0].execute("insert into auditlog(userid, event)"
                  "  values({0}, ‘{1}')".format(handle[2],event))

audit((cursor, connection, 0), 
    "registeration error for {0}”.format(username))

 sql="""insert into activitylog(userid, activity, start, stop)
           values({0}, '{1}', '{2}', '{3}')
      """.format(handle[2], activity, start, stop)

Answer 1

From the docs:

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’s execute() method. (Other database modules may use a different placeholder, such as %s or :1.) For example:

# Never do this -- insecure!
symbol = 'RHAT'
c.execute("SELECT * FROM stocks WHERE symbol = '%s'" % symbol)

# Do this instead
t = ('RHAT',)
c.execute('SELECT * FROM stocks WHERE symbol=?', t)
print c.fetchone()

Answer 2

An example SQL injection using your first SQL statement:

cursor.execute("insert into user(username, password) values('{0}', '{1}')".format(username, password))

If username and password are "blah" the resulting SQL statement is:

insert into user(username, password) values('blah', 'blah')

and there is no problem with this particular statement.

However, if a user is able to enter a value for password, perhaps from a HTML form, of:

blah'); drop table user; --

the resulting SQL statement will be:

insert into user(username, password) values('blah', 'blah'); drop table user; --

which is actually 3 statements separated by a semicolon: an insert, a drop table, and then a comment. Some databases, e.g. Postgres will execute all of these statements which results in the user table being dropped. Experimenting with SQLite, however, it seems that SQLite will not allow multiple statements at a time to be executed. Nevertheless there might be other ways to inject SQL. OWASP has a good reference on the topic.

Fixing this is easy, use parameterised queries like this:

cursor.execute("insert into user(username, password) values(?, ?)", (username, password))

Placeholders are added to the query using ? and the db engine will properly escape these values to avoid SQL injections. The resultant query will be:

insert into user(username, password) values('blah', 'blah''); drop table users; --')

where the terminating ' in 'blah\'' has been properly escaped. The value

blah'); drop table users; --

will be present in the password field for the inserted record.

Answer 3

Whenever you construct SQL statements using string operations and data supplied by an outside user you open yourself up to two kinds of vulnerability:

1. Malicious intent, in which the user types in a value using some combination of line-separator characters, comment characters, and SQL UPDATE or DELETE statements that will have a negative effect on your database (see mhawke's answer).
2. Innocent intent in which the user types in legitimate text but it contains characters your program is not expecting. An example would be someone with the user name O'Reilly. When that's interpolated into the string, the spare apostrophe will make the resulting SQL invalid.