SQL databases are used in many places to store a wide range of information, from product information to customer details. In such circumstances, users may be required to enter information that is then formed into SQL queries. In a poorly implemented system, a malicious user may be able to include additional SQL syntax in their response, allowing them to compromise the SQL database (perhaps by accessing sensitive information, altering it, or simply deleting it).

For example, when asking for a username within a web page, the user could enter the following text:

John; DELETE FROM Orders  

If this was used directly to construct the SQL query, we would end up with the following:

SELECT * FROM Users WHERE UserName = John; DELETE FROM CurrentOrders  

We have just allowed the attacker to delete everything in the CurrentOrders table!

Using user input to form part of SQL queries means we have to be careful what commands we allow to be executed. In this example, the user may be able to wipe out potentially important information, which could be very costly for a company and its reputation.

This technique is called SQL injection, and is easily protected against by using the parameters option of the SQLite execute() function. We can replace our Python SQLite query with a safer version, as follows:

sqlquery = "INSERT INTO %s (itm_name, itm_value) VALUES(?, ?)" %(TABLE) 
cursor.execute(sqlquery, (str(dataName), str(data[i])) 

Instead of blindly building the SQL query, the SQLite module will first check that the provided parameters are valid values to enter into the database. Then, it will ensure that no additional SQL actions will result from inserting them into the command. Finally, the value of the dataName and data[i] parameters will be used to replace the ? characters to generate the final safe SQLite query.