SQL Injection-how to avoid problem in Python DB API
What is Python DB API?
We have few possibilities to manage the database with Python code. The recommended option is to use Object-Relational Mapping (ORM), that we can use in Python like the SQLAlchemy, but today I will write about the simplest database API, which is the Python DB API (specification available as PEP 249). This is a general purpose, low-level type interface to connect to a relational database, sending SQL queries and processing their results. Queries are created as strings by the programmer. The DB API does not see any differences between SQL dialects. You can use this API directly, but it is often rebuilt by other ORM libraries like SQLAlchemy.
When we use SQL queries in Python, we are dealing with metaprogramming. SQL code is a string from a programming language point of view. Python does not recognize SQL types, structures of tables and commands, so this is not a supporting thing for programmers. Hence, metaprogramming is sophisticated and it is easy to make errors. We should not use it for typical tasks like connecting to a database because it could lead to dangerous problems with application security.
What is SQL Injection?
As I wrote earlier, this solution can be tricky and full of ambiguities and today I will describe one of them: SQL Injection. This is one of the fairly frequent and, at the same time, dangerous web (and non-web) applications vulnerabilities. As the name suggests, the problem is the injection of an unauthorized SQL query. It is possible for one reason — the lack of proper checking (validation) of the parameter provided by the user. When dealing with SQL Injection, this parameter is often passed directly to the SQL query.
Depending on the situation, we can deal with:
- unauthorized read or write access to the entire database,
- the possibility of bypassing the authentication mechanism,
- the ability to read selected files (operating system on which the database works),
- the ability to create files in the operating system on which the database works,
- the ability to execute the code in the operating system (user rights on which the database or web server works — in the case of web applications).
As you can see, SQL Injection can access various application components, so I only describe a very simple example. It shows how an attacker can use an SQL Injection vulnerability to go around application security and authenticate as the user.
The following script is pseudocode executed on a web server. It is a simple example of authenticating with a username and a password. The example database has a table named users with the following columns: username and password.
# Define POST variables
username = request.POST['username']
password = request.POST['password']# SQL query vulnerable to SQLi
sql = “SELECT id FROM users WHERE username=’” + username+ “’ AND password=’” + password+ “’”# Execute the SQL statement
database.execute(sql)
These input fields are vulnerable to SQL Injection. An attacker could use SQL commands in the input in a way that would alter the SQL statement executed by the database server. For example, they could use a trick involving a single quote and set the password = password' OR 1=1input, as a result the database server runs the following SQL query:
SELECT id FROM users WHERE username='username' AND password='password' OR 1=1'Because of the OR 1=1 statement, the WHERE clause returns the first id from the users table no matter what the username and password are. The first user id in a database is very often the administrator. In this way, the attacker not only bypasses authentication but also gains administrator privileges. They can also comment out the rest of the SQL statement to control the execution of the SQL query further:
-- MySQL, MSSQL, Oracle, PostgreSQL, SQLite
' OR '1'='1' --
' OR '1'='1' /*
-- MySQL
' OR '1'='1' #
-- Access (using null characters)
' OR '1'='1' %00
' OR '1'='1' %16Example of SQL Injection problem using Python DB API
I have for you a short example of code, where I am using DB API. Purpose of this article is SQL Injection mistake, so I am not describe the details of using DB API. But you can see that I create AuthDatabase and then using SQL queries build table credentials and put into some data. Finally, the code simulates logging in and checking if the user has entered the correct password.
As you can see, if you put SQL Injection expression you will receive an access to database, what is more, result return all databases records.
login: alice
password: xxx' OR 1 = 1 -- ]
[('alice', 'qwerty'), ('bob', 'secret')]
Access grantedProcess finished with exit code 0
Solution for this problem is allow to execute positional and keywords arguments in cursor method: cursor.execute(sql_string, *args, **kwargs) .Then you can relay login and password as a list to query method.
Now result of use SQL Injection expression is as we expect. We need to pass the correct password to get access to the database.
login: alice
password: xxx' OR 1 = 1 -- ]
Access deniedProcess finished with exit code 0
It was, of course, a very simple case of SQL Injection. I want show you that we must pay attention about security of apps we create.
Conclusion
As you can see, SQL Injection is a significant problem and we should protect our applications against it. There are many ways to do this, sometimes frameworks already have such protections, so we don’t have to worry about it. To put it briefly, you should always appropriately verify the variables passed from the user to the application.
[1]: Acunetix. What is SQL Injection (SQLi) and How to Prevent It https://www.acunetix.com/websitesecurity/sql-injection/
[2]: Michał Sajdak. (June 25 2013). Czym jest SQL injection?https://sekurak.pl/czym-jest-sql-injection/
