I Wrote Vulnerable Code
I am a firm believer in writing tests as you develop. I can not say I am a consistent practitioner of TDD(Test Driven Development), but with the few development projects I work on, I try to make sure I write documentation and tests during the development process. Recently I was working on a project under a pressing deadline.
The project involved transforming and syncing large data sets into a database. Once I finally had a functioning product, I started syncing an extensive test data set. I received an error that sparked my interest.
“A failure occurred while executing row. Illegally formed character string was encountered during translation.”
Digging further into the issue, I see that there are some (‘) characters in a field value for some of the rows in my dataset.
So I dive back into my tests and …
I am a security engineer who wrote a tool RIDDLED with SQL (Structured Query Language) Injection. I was partially embarrassed, partially excited to mess with this application and fix it.
Now, judge me if you must, but I loved that this situation happened. Academically I am aware of SQL injection. I think using parameterized queries and prepared statements are simple rules to follow. As security practitioners, some vulnerability fixes may seem simple to us, professionals who operate daily in the context of security. I am no developer, but it's easy to understand that sometimes you are just trying to make something work, and you may not keep security in mind when building an application.
So, I went through and implemented prepared statements and added tests to my unit test package. I also stumbled upon Bandit. Bandit is a tool designed to find common security issues in python code. Not only does Bandit find common issues, but it also provides some information about the issue, tries to gauge how severe the issue is, and tells you how confident Bandit is that the issue exists in your code.
I added running Bandit as part of my linting process. Bandit caught all lines of code with potential SQLi, immediately. I would not have had this issue if I had been using Bandit throughout my development process.
What is SQLi?
SQL injection is a vulnerability that allows an attacker to manipulate queries that an application makes to a database. This vulnerability may allow an attacker to access sensitive information, compromise the application server back-end infrastructure, or perform a denial of service attack.
This funny comic demonstrates the possible impacts of SQLi:
I wrote a simple example to demonstrate this vulnerability. The code below is a simple login function.
(Disclaimer: It is not pretty. It is just a demo)
The login form below requires a valid username and password to log in.
Here is the code for this login form.
Now many of you may be seeing this and immediately identify the issue. But let’s walk through this code. The interesting part is the logic for the POST method.
In the code block below, we notice that the variables “name” and “passw” are populated directly using the data a user inputs to the log-in form.
These variables are passed directly into a SQL query. This query attempts to retrieve a username for a user with a matching username and password provided by the form.
SELECT username FROM user WHERE username =’{name}’ AND pasw = ‘{passw}’;
Suppose the query receives a matching result. The application will redirect the user to the home page with the username displayed.
Example with valid guest user login.
What would happen if a user entered something like name=admin’-- and password=’’ ?
What does our query look like with this user input?
(‘) closes the string used for username. Double (-) represents a comment, so the white part of the query below represents the commented portion of the query.
SELECT username FROM user WHERE username =’admin’--’ AND pasw = ‘{passw}’;
With this input the query has become:
SELECT username FROM user WHERE username =’admin’
Bandit
Let’s run Bandit against our code.
Install Bandit:
pip3 install bandit
Run Bandit:
Bandit <target_file/s>
Bandit was able to spot the SQLi. It gives informative links to learn about SQLi and suggestions to fix the issue. If you are interested in the Bandit provided links:
CWE SQLi:
https://cwe.mitre.org/data/definitions/89.html
SQLi Bandit docs: https://bandit.readthedocs.io/en/1.7.4/plugins/b608_hardcoded_sql_expressions.html
Parameterized Queries:
Parameterized queries help us disregard extra items in our query. We can build SQL statements and use placeholder substitution to insert our parameters into the SQL query. A SQL statement may use various kinds of placeholders for our parameters. For example, we may use sequence, qmark style, or named parameters:
Example Sequence/QMark:
SELECT username FROM user WHERE username = ? AND pasw = ?;
Parameterized queries that leverage sequence style parameters will raise an error if the number of placeholders does not match the sequence received.
Example Named:
SELECT username FROM user WHERE username = :username AND pasw = :password;
Named parameters are typically stored in a dictionary with keys for all named parameters. Parameterized queries with named placeholders will disregard all parameters without keys.
Let’s see what this looks like in practice:
We have rewritten our function using a parameterized query. Our query expects 2 parameters, “name” and “passw.” The query will disregard extra parameters and fail if both parameters are not received. When the query fails, the user is brought back to the login form and prompted to enter credentials again.
We can run Bandit against our new code.
After making these changes to our function, we no longer receive warnings for SQLi from Bandit. However, we should always write tests to confirm that we have mitigated the issue.
Summary:
Today we walked through a simple example of SQL injection and how to use parameterized queries to avoid writing such vulnerable code.
Bandit is an excellent, straightforward code analysis tool that can also be used for security linting. I will definitely add Bandit to my linting process in future python projects!
We all make mistakes. I hope you grant yourself some grace and take advantage of the great learning opportunities mistakes provide.
