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

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 2

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()