Protect Your Web Application from SQL Injection Attacks

What is SQL Injection and How is it Done?

According to OWASP top 10 application security risks dating from 2017, injection flaws (such as SQL, NoSQL, LDAP) still occupies the first place.1 The flaw occurs when untrusted data is sent to back-end to be used, more often, as a searched term inside a query, but, due to a poor application development, it ends up being interpreted as part of a command or query. The attacker’s input data can trick the interpreter into executing unintended commands or accessing data without proper authorization.

What is SQL Injection?
According to Wikipedia, “SQL injection is a code injection technique, used to attack data-driven applications, in which nefarious SQL statements are inserted into an entry field for execution (e.g. to dump the database contents to the attacker). SQL injection must exploit a security vulnerability in an application’s software, for example, when user input is either incorrectly filtered for string literal escape characters embedded in SQL statements or user input is not strongly typed and unexpectedly executed.”2

In recent years, there has been media coverage on attacks on data-driven applications, as these types of attacks are still being used to hack big companies and retrieve sensitive information.

How can it be done?
In the following article, using a mock-up web application, we will dig into some of the commands that can be used to try to retrieve sensitive information from a web application database. This information is not supposed to be visible to the user who surfs it.

1. Mock-up application structure: Below is a screenshot of the mock-up web application displaying a cars table along with some basic information about them. The URL points to localhost:8080

We can also search the cars table by year.

This type of functionality is present in most of the web applications – a page for displaying customer information data and some field(s) to do additional filtering on the data.

Let’s have a look behind the scenes and see how this page is being implemented/rendered in terms of back-end / front-end. For those of you who have used Spring before, this is just a classic Controller which calls out a DAO which retrieves all the cars by a year (the service layer is missing since it’s out of the scope of this article). We have the view which will be the actual .jsp page displayed (the “welcome” page) along with a model (the filtered cars) which we will display in the UI. Below is how we filter out the cars from the database using a wrong implementation for constructing a SQL select.

Above, we are getting from the database all the cars which match the year a user enters in the UI. We add them to a list. The piece of code which will actually permit us to try out some injections is the manual concatenation of the String year in the query (“select * from cars where year = ‘” + year + “‘”;)

Let’s have a look at the UI side:

The only thing to note here is that we have a table with 3 columns and we display the cars coming from the back-end. Therefore, nothing “hacky” is implemented in the back-end nor the front-end, except that poor constructed SQL query.

2. Exploit the application The first thing we want to try is in fact if the application is susceptible to SQL injections or not. See below.

So, we’re going to try to “search” for the following string: 2016′ order by 1; — ‘. As we can see in the picture above, it did return one result, even though the input contains a lot of weird characters. This means that, behind de scenes, the database wasn’t in fact queried by that string ( 2016′ order by 1; — ‘ ). If it were, it wouldn’t have returned any results. So, what happened? We can deduct that the string was interpreted by the SQL command processor and executed it different than it supposed to. Let’s explain this string and what it does. The 2016 value is a year that we want to search for. We saw in the first picture that the table initially displays a list of cars, among which, there is one from year 2016. The next character is a single quote which will try to close a manual concatenation of the string year in the back-end. In Java and in other languages as well, it is very easy to construct a SQL query like below:

String select = “select * from cars where year = ‘” + year + “‘”; Note the single quotes inside the double quotes.

So, if we replace year with 2016’ we will get:

String select = “select * from cars where year = ‘2016’ ‘”;

Running the above statement ( select * from cars where year = ‘2016’ ‘ ) in a SQL Database will not work because we have an extra single quote in there. We will take care of this later on. The next piece of the string we entered is order by 1. This means that we try ordering the resulting information based on the first column. The reason we want to do this is to enumerate and establish how many columns, the table is queried from, has. The last piece of information is ; — ‘ . The ; means that we ended the current SQL statement (this is useful in case we want to run multiple SQL commands in the same time. For example, the original select followed by a drop table). The means, that, whatever comes after this, is interpreted as a comment. Remember how we got an extra single quote after inputting 2016’ ? Now this comment comes in handy since if we use it, the single quote will be commented out. In some databases the single line comment is different (in Oracle, for example is //. So you will need to play out and try several approaches). The final query that the SQL database will execute is: select * from cars where year = ‘2016’ order by 1; — ‘

The query will return one row containing one car from 2016. We also established that the underlying table has at least one column (which is logically). Let’s try to increase the order by value to see how many columns we end up with.

Order by 2 still displays one row, so we can conclude that we have at least 2 columns. Let’s increase more.

When we order by 4, it still displays one row, but when we order by 5, nothing is displayed. In SQL, ordering by a value that is greater than the number of columns returned, will result in an error. Since no rows are displayed in the UI, we can deduct that an error has been thrown by SQL, it was handled internally (since no error messages were displayed in the UI) and the list of cars returned in the UI was empty. That’s the reason for, when increasing from 4 to 5 in the order by clause, no rows are returned.

Now that we have established the number of columns returned by the underlying query, we can move on to the next step. We will mountain a UNION based injection attack. Since we are able to manipulate the query that brings back in the UI the list of cars, we can ADD to that query some other rows that WE control. This will be accomplished using the UNION SQL command. See below picture.

The injection is: 2016′ UNION SELECT null, version(), null, null; — ‘ The query that will be executed by the SQL database is: select * from cars where year = ‘2016’ UNION SELECT null, version(), null, null; — ‘

The version() is a MySQL function that will return the current DB version. This function name may vary from database to database and you might have to try some values until you get the correct one. The select after the UNION will ADD the version() and 3 empty columns to the result set of data returned in the UI. Remember when we established the number of columns returned by the underlying select? This is where we need it.

For a UNION to work, we need to select the same number of columns and the same column types in all the selects UNIONed. Since the data displayed in the cars table is almost all comprised of strings (make, color), except (maybe) the year which might be an INT, we will have to play around with the column position where we select the version(). Now it’s the 2nd column selected, but if the table doesn’t return any results, then an error occurred in the back-end since it tried to use a different column type than the one in the first select. To fix this, we just have to move around version() to column position 1/3/4 and see where it returns results.

As you can see in the picture above, the current DB version was returned as a new table row, with the other values having default values (INT default value is 0 and NULL comes in as empty). What is actually happening in the back-end is, since we return more data from the UNIONed selects, they are still treated as cars. So, for each row, a new Car object will be created containing now bogus information (the information that WE control) instead of the real one containing car relevant data.

Now that we’ve established the database version and were able to print it out in the table in the UI, let’s try bringing more data from the underlying database.

In the above picture, using MySQL default table for storing schemas, we retrieve all DB schemas available in the DB.

Injection is: 2016′ UNION SELECT null, table_schema,null, null FROM information_schema.columns; — ‘ SQL statement executed: select * from cars where year = ‘2016’ UNION SELECT null, table_schema,null, null FROM information_schema.columns; — ‘

This is the first step where we actually retrieved some sensitive information from the underlying database. Since we have a list of all the schemas defined, let’s pick one (article) and do some more digging.

Injection: 2016′ UNION SELECT null, table_name, null, column_name FROM information_schema.columns where table_schema = ‘article’ ; — ‘ SQL statement executed: select * from cars where year = ‘2016’ UNION SELECT null, table_name, null, column_name FROM information_schema.columns where table_schema = ‘article’ ; — ‘

In the above picture, we were able to print out all the tables from the article schema. As you can see, there is the car table which might be the one that drives the original table itself. However, there is another one called users which might contain some sensitive information. Let’s query this table and find out which columns are part of it.

Injection: 2016′ UNION SELECT null, table_name, null, column_name FROM information_schema.columns where table_schema = ‘article’ ; — ‘ SQL statement executed: select * from cars where year = ‘2016’ UNION SELECT null, table_name, null, column_name FROM information_schema.columns where table_schema = ‘article’ ; — ‘

In the picture above we can see that the user table is made of 3 columns: id, name and password. Let’s extract the actual information from the table.

Injection: 2016′ UNION SELECT null, name, null, password FROM article.users; — ‘ SQL statement executed: select * from cars where year = ‘2016’ UNION SELECT null, name, null, password FROM article.users; — ‘

Besides car information, we were able to successfully retrieve sensitive user data. You can see usernames and password displayed in the above table.

Automate the process using Havij 5,6

The purpose of all of the above was to explain, step by step, and understand how a web application can be tried to be attacked using SQL injection. Even though the process was done manually, this can also be automated using a tool like Havij. You can find the link for downloading it in the article sources.

In the pictures below you can see, step by step, how to find out the schemas, the tables, the specific columns for a table and how to retrieve sensitive information. Basically this is the same process as the one we did manually. In the target input you will have to put the URL from the web application where you search “cars by year.” Hit Analyze and that’s it. Play around with the tabs from user interface to find out the available schemas, tables, columns and data.

In order to protect your web application from SQL injection attacks, you will always have to escape the search string coming from UI. Thus, instead of the string to be eligible for being interpreted by the SQL processor, it will be treated as a plain string – meaning, in our case, the year will be that string, therefore returning no cars in the UI. In Java, instead of escaping the string yourself, you can use the PreparedStatement class instead of Statement one, to construct queries.







Background Image