In the digital age, where data fuels our everyday transactions and interactions, the security of our databases stands as one of the main concerns. SQL Injection, common and vicious attack vector, exemplifies the critical importance of guarding databases against potential bad actors. The attack may result in a breach or loss of data, so that is why we should take a close look at this attack.
If you want the detailed definition please see OWASP definition. It is a well known attack and I couldn’t add more. I focus more on the practical examples and that is why I will give you a short example to understand how it works. Imagine this SQL query SELECT * FROM table WHERE name='%{name}%'. Imagine also that we store it as a normal text, and we replace the {name} with whatever is inputted by the user. Then we passthe query to the engine and display returned data to the user. At first glance it seems fine and until users will provide standard values it will be fine, but this implementation will result in SQL Injection vulnerability. Try to think what will happen if user inputs such value: abc'; DROP DATABASE databasename;--. In our scenario it will result in the following query SELECT * FROM table WHERE name='%abc'; DROP DATABASE databasename;--%'. Kaboom, your database is removed. You can argue that user need to know database name, but further in the demonstration section I will show you that it won’t be the problem to find the database name.
SQL Injection attack works when we pass SQL query built with user input as a normal text. This way whatever is inputted by the user is used to construct SQL query. This is a naive approach, because, as shown in previous section, user can exploit that to completely modify the query. Modern database engines provide a way to pass query and parameters independently, so we are always sure that the value inputted by user will be treated as sql query parameter and not as part of the query. For example in PostgreSQL we can prepare parametrized statement that implements this solution. To make SQL query from previous section safe, in PostgreSQL we would need to write it in the following way: PREPARE query_table_by_name (text) AS SELECT * FROM table WHERE name=%$1%. User can input whatever, and we are always sure that it is treated only as a parameter. It cannot escape being a parameter and modify the query. Prevention is as simple as that. What’s more, it is implemented in most of the modern frameworks or object-oriented mappings (ORMs). Anyway it is crucial to understand this attack, because there is always a way to execute raw SQL query (even with ORM) and sometimes it may be tempting to do so.
Let’s leave theory behind and get our hands on real live example. Go to the demo site. It is a simple mockup site of fake carrier that provides user with view to search for parcel stores by address. The site itself is primitive, but what is most important it provides a way for user to input search query which is later used in SQL query to filter parcel stores. In the navbar you can also specify whether you want to use secure version of the application (with SQL Injection protection) or whether you want to use insecure version (without SQL Injection protection).
Please open demo site without SQL Injection protection. Let’s try to recreate the reasoning and actions attacker performs when he wants to exploit potential SQL Injection vulnerability. Let’s check that the search mechanism works by inputting Warsaw. It should display two parcel stores as expected. With the knowledge we have from previous sections let’s see whether form is vulnerable to SQL Injection by using UNION keyword. The idea behind it is that it should “union” two query results into one under two conditions: number of returned values must match and values in the same column of different tables must have the same type, unless it is NULL value. We will use this NULL value exception to check how many values are returned in the query. In the search output we can see three values per parcel store, so it is logical to conclude that query returns three values. Let’s try abc' UNION SELECT NULL, NULL, NULL;-- then. It does not work. Maybe some fields are not displayed (many times also primary key is returned from query etc.). Attacker have to try many combinations, but we can skip that and we can already try to input NULL five times: abc' UNION SELECT NULL, NULL, NULL, NULL, NULL;--. Great (not great for fake carrier), that is working. We are now sure that site is really vulnerable.
Now it may be useful to know on what database we are operating. Most of the modern database provides some kind of command to check it. Let’s skip part of guessing and with the knowledge that this app is using SQLite let’s search for abc' UNION SELECT NULL, NULL, sqlite_version(), NULL, NULL; --.
That is very useful information. In SQLite table names are stored in the table called <DB_NAME>.sqlite_master. Default database name is main. We can search for all tables with this input abc' UNION SELECT NULL, NULL, name, NULL, NULL FROM main.sqlite_master WHERE type='table'; --.
Once we find interesting table we can also get SQL which was used to create the table from the same <DB_NAME>.sqlite_master table. Let’s check it with following input abc' UNION SELECT NULL, NULL, sql, NULL, NULL FROM main.sqlite_master WHERE name='parcelstore'; --.
That is interesting, we found access_code column that is not shown in the search result. Probably that is the code that if provided will open locked doors of parcel store! (let’s assume that such access code really exists and is stored on the same table for the sake of this example :D). Now all we need to do to get all access codes is to input this abc' UNION SELECT NULL, name, address, access_code, NULL FROM parcelstore;-- and we now know all access codes with the exact locations of parcel stores.
In real life scenario using SQL Injection you could also add data to the tables (for example users with admin privileges), drop tables or drop whole database, but because I expose this vulnerability online I ensured that only UNION SELECT can be injected (protection in pewwee library to run only one raw query at once), so only read queries are allowed.There is (hopefully) no way of dropping the database. I also use different database for this demo than for other demos, so there is also no risk of breaking my other demo sites.
You can switch the site to the secure version and check all previous inputs that helped us to get access codes. It won’t work, because now SQL queries are parametrized. For database operations I use peewee package which provides ORM which parametrize the query under the hood. Using framework recommended way of communicating with database is always the safest option. For most of the moderns languages there is always some ORM available (for example peewee or SQLAlchemy for Python) which should be used. Anyway, as you can see in the code, ORM also provides a mechanism to execute bare SQL queries. It is not recommended, but you can do this. There is mechanism for parametrizing raw SQL queries in peewee, but you are not forced to use it. That is why developers should be aware of SQL Injection. Not knowing this can result in introducing major vulnerabilities to the applications.