SQL Injection is a code injection technique that attackers use to insert malicious SQL code into input fields for execution by the backend database. A successful SQLi attack can allow attackers to view, modify, and delete data in the database or even execute administrative operations on the database, leading to full system compromise in some cases.
Below is a code example to demonstrate:
The above code sets up a SQLite database in memory:
The ‘sqlite3’ module provides a lightweight disk-based database that doesn’t require a separate server process in Python.
sqlite3.connect(‘:memory:’) uses the sqlite3 module to create a new database in RAM, which is useful for testing or for temporary databases.
conn.cursor() creates a cursor with the database created that is used to execute SQL commands and enable traversal over the records in a database.
With the cursor created, we can execute a SQL command to create a table called ‘users’. The table has three columns:
id: An integer - the primary key, so each user will have a unique ID
username: A text column that cannot be null
password: A text column that cannot be null
With this table, we can insert a sample user. The question marks in the code “INSERT INTO users (username, password) VALUES (?, ?)” are placeholders. The tuple (“admin”, “securepassword123”) will replace the placeholders, as the sample user inserts into the table.
conn.commit() will save the sample user to the database.
Below is a vulnerable ‘login’ function as it directly interpolates user inputs for ‘username’ and ‘password’ into the SQL query without sanitization.
The malicious input ‘admin’ OR ‘1’ = ‘1’ alters the logic of the SQL query to always return a true condition, allowing an attacker to log in without knowing the password.
Prevention
In this adjusted function, the ‘query’ string uses the question mark as placeholders for the ‘username’ and ‘password’ parameters. When executing the query, we pass the actual values for username and password as a tuple to the ‘execute’ method. This ensures the SQLite library always treats the provided input as data, not as executable SQL code. This is a simple example of mitigation against possible SQL injection attacks.
Below are some additional readings on SQL Injection from the Open Worldwide Application Security Project online community for those who are interested:
AIQ by Nick Polson and James Scott
If education, as Thomas Jefferson said, is the cornerstone of democracy, then when it comes to digital technology, our democratic walls are falling down. Americans have debated the limits of commercial free speech almost since our birth as a country. But today we are way beyond a conversation about ads for sugary cereals on Saturday morning cartoons - far from the only example of a dubious marketing practice made utterly quaint by our new technology. There are so many unknowns that await us down the road. At a minimum, courts and legislatures should know more about their own blind spots, and stop dismissing details they don’t understand as “gobbledygook”. And citizens should participate in these discussions from a position of knowledge, rather than fear, of the basic technical details. Put simply, smart people who care about the world simply must know more about AI.