Security becomes more and more important. Clients will not use our products if they will not trust us. On the other hand, sensitive data are a tasty morsel for attackers who can try use this data to grant access to another website or use them for phishing and so on. In this article, I will tell you about using this kind of vulnerabilities.
One of the most popular vectors of attack is SQLInjection. Almost every website on the Internet uses some kind of database like MySQL, PostgreSQL, MSSQL and so on. SQLi is a technique where an attacker edits a request to modify an SQL query to get information to which access does not have.
Very basic example
Let’s say we have a very simple blog system. In GET request for ID parameter, we add the ID of the post we want to display. So, when your example URI is /post?ID=1 the SQL query will look like
SELECT * FROM post WHERE id = 1 AND private = 0;
If we pass directly the value of ID parameter to the query, we have SQL injection vulnerability. This happens if you do not filter your input or you do not do it good enough. I will focus on this topic later on but for, now I will try to use this mistake to get the data I want. I’ll play with it a bit. As a playground, I created a simple MySQL database with two tables and added some data to them.
CREATE TABLE `post` ( `id` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(255) DEFAULT NULL, `content` text, `private` int(11) DEFAULT '0', PRIMARY KEY (`id`) ); CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(255) DEFAULT NULL, `password` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ); INSERT INTO `post` (`id`, `title`, `content`, `private`) VALUES( 1, 'My first post', 'Hi there. It is my first post!', 0), (2, 'My secret post', 'Here is the place where I keep my secrets', 1); INSERT INTO `user` (`id`, `username`, `password`) VALUES (1, 'john', 'mypass'), (2, 'admin', 'admin password');
SQLi in action
In the example above, I showed simple query which would help us iterate over all the public posts. We have blog posts in this example but it can be invoices or more sensitive data. Let’s suppose the developer does not filter any customer’s input. How can we use it to get ANY data from the database? Let’s back to the query. I split it into two parts.
SELECT * FROM post WHERE id = 1 AND private = 0;
The yellow section is this part we will not change, and the red one will be replaced by us. Before we start, let’s think what we want to achieve. We have two posts in our database. Th first one is publicly available and the second one is not. We do not know the ID of the second post, but we can try to iterate throw the table to get what we want. To do this we have to iterate over the table one by one to get what we want. Example query can look for example like the query below.
SELECT * FROM post WHERE private = 1 LIMIT 0,1;
In this query, we fetch the very first private post in the database. To get the second one, we have to change LIMIT to 1,1. OK, now we know what we want to achieve. As you remember, we can change only the ID of the post. For now, let’s ignore what is after the ID and put our partial query instead of it. Our URI will look similar to it:
/post?ID=1 OR private = 1 LIMIT 0,1
It will produce query similar to this one:
SELECT * FROM post WHERE id = 1 OR private = 1 LIMIT 0,1 AND private = 0;
As you can see, the query has invalid syntax because after LIMIT section it has extra “AND private = 0;” part which we do not need. To remove it, actually, to dispose of it, we will add comments marks. I’ll use the double dash (–) syntax. The URI will be changed this way:
SELECT * FROM post WHERE id = 1 OR private = 1 LIMIT 0,1 -- AND private = 0;
By manipulating the LIMIT parameters we can get every post in the database. Cool, isn’t it?
More advanced usage
Getting post on from the blog is boring… Yeah, I know it. It’s time to get EVERY information from the database from every table you want! We will use UNION SELECT‘s. How does it work? Generally speaking, you create two queries. If the first one returns no record, the second one is executed. There is only one requirement – the number of the columns must be the same on the left side and on the right site. What does it mean? Le’ts take a look at the example above.
SELECT id, title, content FROM post WHERE id = -1 UNION SELECT id, username, `password` FROM `user` LIMIT 0, 1;
If there is not post with ID=-1 (and it does not because IDs generally must be positive) it will execute the query after UNION SELECT and return the first user from the database. Using this technique you can get almost any information from the database. There is one problem: how can I know how many columns does the first query have? You do not know but you can get this information by trying selecting only one NULL in the second query. If it fails, then add another NULL to the query and so on…
SELECT id, title, content FROM post WHERE id = 999 UNION SHOW TABLES, NULL, NULL LIMIT 0, 1;
If the query executes correctly but it brakes somewhere in the script you have the correct number of the columns. But… what if you do not know the name of the tables in the database? There are a
SHOW TABLES command but it will not work in subqueries. However, you can use combinations of
SELECT TABLE_NAME FROM information_schema.TABLES queries to get exactly what you want. Here is an example where I get first 3 table’s names in one query.
SELECT id, title, content FROM post WHERE id = -1 UNION SELECT (SELECT TABLE_NAME FROM information_schema.TABLES LIMIT 0,1), (SELECT TABLE_NAME FROM information_schema.TABLES LIMIT 1,1), (SELECT TABLE_NAME FROM information_schema.TABLES LIMIT 2,1) LIMIT 0, 1;
Tricky, right? When an attacker finds an SQLi vulnerability, this is the most common way to get information from the victim.
Real application example
You may think we use ORM’s this days and we are not stupid to make so similar mistakes. The truth is we all may forget to filter some data from users. It is the most common mistake we make. Do not believe me? Take a look at OWASP Top 10 Cheat Sheet.
Now, I will list you some real applications SQLi vulnerabilities in very common CMS:
How can we protect from it?
The solution is very simple – never trust anyone – even yourself. Especially don’t trust users. Another tip is always to use an ORM. They have implemented professional filtering data. Every database may have different encoding character. It may have the different encoding character in the same DB engine but in the different configuration. ORMs can detect the settings and do the dirty work for us.
Two tricks in MySQL DB
Some time ago my mate showed me one simple trick which may be used in testing security. This example will also show why you should avoid using LIKE conditions in your queries whenever you can (not only because of performance)! Suppose we have the table with users as I showed in the beginning of the article. The admin’s username is admin. Another user registered with username ad_in. Try guess what MySQL will return when you execute the following query:
SELECT * FROM user WHERE username LIKE "ad_in";
Very often we forget about this special character. Some of you may know it but the second thing surprised me a lot. It works only in MySQL (tested on MySQL 5.5 and 5.6). Let’s say we have a query similar to the query below.
SELECT * FROM user WHERE username LIKE "admin";
admin text is the user’s username got in regular form. Let’s say it is escaped correctly. It will work as we expected until… we add extra spaces at the end of the text. Need example? Take a look at the queries below
SELECT * from user where username = "admin"; SELECT * from user where username = "admin "; SELECT * from user where username = 'admin ';
All the queries will return the same result. It was surprising to me but it should work this way. I’ll tell you why. According to the standard and MySQL documentation when you compare two strings MySQL adds extra spaces to the shortest string to make their lengths equal and then compare them. The MySQL engine takes two strings
admin , add extra space on the end of the first string and compare them. It means if you may have a unique index on a column, add two records
admin into the same column, then execute the query
SELECT * from user where username = "admin ";
and you will receive two results instead of only one. Interesting, huh?
SQL Injection is a serious problem. The report shows it very clearly. As developers, we may not clearly know how dangerous maybe not correct filtering data. Today I showed you some techniques that attackers may use to get sensitive data from our apps. I hope it will help some of you with understanding how it works.
PS. If you know any other tricks in other DB engines, let me know in the comments!