Let’s Talk About Security – SQL Injection

In this post, I am going to address a method of attack that has been #1 of the OWASP top 10 security risks for web applications for several years: SQL injection. Many developers are still scratching their heads, wondering how such a simple method of attack could make it to the top of the list of security vulnerabilities. One of the reasons why SQL injections are so popular with hackers is the fact that this vulnerability is so easy and quick to exploit. This is due to the strict time constraints during the development stages of applications, where functionality is prioritized and security precautions come last.

syringe for SQL Injection

With an SQL injection, the hacker exploits the insufficient verification of data entered into the system. The standard database queries are rewritten to suit the attacker in order to gain access to sensitive data on the server. In worse cases, the attacker is even able to rewrite databases and upload their own content. The access point for such injections can be exploited by both external and registered users. That is why they say “Never trust the user!”.

To enable you to protect your systems against SQL injections, I am going to try and give you a basic understanding of how such injections work. As the term “injections” implies, they are manipulated SQL queries. Therefore, we have to take a closer look at the attack vectors. Common examples of SQL queries on websites include:

  • Authentication (login window)
  • Search boxes (retrieving relevant information from the database)
  • URL (modifying the hyperlink)

The login window provides two boxes in which the user information is to be entered. The information provided is transmitted to the server, and the database is scanned for a matching user.

User information SQL Injection
Figure 1: Login mask for user query

If no matching user is found, the login fails. At the source code level, the string of “Username” and “Password” is used to build an SQL query, which is in turn used to access the database. The code would look something like this:

    var User = getName("Tester");
    var Password = getPass("Passwort1");
    sql_cmd = 'SELECT * FROM Users WHERE Name ="' + User + '" AND Password ="' + + '"'

Resulting SQL query:
              →  SELECT * FROM Users WHERE Name = “Tester” AND Password = “Passwort1”

In this example, the user input is accepted without verification and used to build the query. The system trusts the user input. If the user is a hacker who recognizes this vulnerability, they could exploit it as follows.

User information SQL Injection
Figure 2: Login without verification

Special characters and commands are entered in the login fields to complement the SQL query. With the entries shown above, the resulting query would look as follows:

Resulting SQL query:
              →  SELECT * FROM Users WHERE Name =”” or “”=“” AND Password =”” or “”=””‘

The additional OR in the query, which in this case always returns TRUE, results in every entry from the table being displayed. If the login system only checks whether a user with the respective password exists in the database, this query will always produce a positive result. Consequently, this entry would be a universal key for all attackers in an unprotected system.

This is one example of how an SQL injection in login fields can be done. The injection can also be done via the URL of a web page. The URL consists of the address of the web page and a path representing the directory of the server. This file path also includes PHP or HTML parameters reflecting the user’s query.

Examples of URLs:

  • http://testmysql.com/report.php?id=23
  • http://www.testmysql.com/search.html?query=test&searchProfile=tester

These parameters in the URL paths can be used for injections. Similar to the first example, the string can be replaced to manipulate an SQL query to serve the attacker’s purposes.

  • Changed URL: http://testmysql.com/report.php?id=105; DROP TABLE items; —
  • Resulting query:   →  SELECT * FROM items WHERE id = 105; DROP TABLE items; —

By adding DROP TABLES, another SQL command is added to the query and executed when the connection to the database is established. First, the desired standard query is executed. The database is searched for the ID 105, but immediately afterwards, the entire table and all the data it contains is deleted.

I hope that these two examples illustrate how critical injections can be for a database. Of course, you should note that the injections shown above are very simple examples. Such injections cannot be directly called up on any page. In practice, potential attackers have to work harder. Although it is possible to find web pages with such a URL path on Google, e.g.:

  • inurl:”product.php?id=” site:.de

In most cases, they are secured. Furthermore, the attacker must first get an overview of the system. Every database is structured differently and has different names for the tables, e.g. for the user information. You can actively protect your system by using standard frameworks. They are tried and tested and continually developed. If such a framework is further customized for your own system, attacking it becomes even more difficult. The attacker has to make several attempts to find out how queries are verified and which input formats are used. Unfortunately, the error messages displayed due to these attempted injections will sometimes give the attacker the information they need for their attack. This is why it is important to check the content of the error messages that the user sees. In the worst case, the error message reveals which database table and which columns contain certain data. With this information, it is very simple to build a corresponding injection. Another important safety measure is using a user with limited rights to access the database. This way, you can prevent commands, e.g. to delete or modify data in the tables. In addition, you should use “stored procedures” to further limit the possibilities to access the database.

Within the framework of a comprehensive quality assurance process, you can also identify potentially dangerous vulnerabilities by means of tests and subsequently eliminate them.

There are several tools for testing such security vulnerabilities; they exploit common vulnerabilities to test the systems for weak points. These tools are continually developed and can be used not only by developers, but also by testers. Examples of such tools:

  • BSQL Hacker
  • SQLmap
  • SQLNinja
  • Safe3 SQL Injector

Now that the basics are clear, I am going to demonstrate a practical example using one of the tools mentioned above. The SQLmap tool enables both attackers and testers to comb through the database of a web page with just a few command lines. If, for example, it is a PHP application with a “php?id” string in the URL path, this can be used as interface for the injection.

Code SQL Injection
Figure 3: Query of related databases

By means of its standard queries, the tool checks which databases the web page is connected to. For our example, we will test the website “testing-site.com” that we ourselves created. The injection reveals that the databases INFORMATION, WALLET and TESTING-DATABASE constitute possible targets of an attack. Now that we know which databases are available, it is possible to analyze the next level.

Code SQL Injection
Figure 4: Tables of the TESTING database

The TESTING database comprises nine tables. Next, we take a closer look at the USERS table to read out the information.

Code SQL Injection
Figure 5: Analysis of the USERS table

After the in-depth analysis of the USERS table, the attacker now knows which columns need to be read out to obtain the desired data. With the information collected, it is now possible to to write a specific injection to retrieve data about the users from the database.

Code SQL Injection
Figure 6: User information in plain text

USERNAME and EMAIL are stored in plain text in the database. The PASSWORD is encrypted. In the next step, you could for example use rainbow tables to decrypt the passwords, enabling you to use one of the users.

I hope this blog post gave you some insight into SQL injections and made you aware of the respective security vulnerabilities. Unfortunately, they are exploited to access foreign systems far too often, which is why it is important to take appropriate security precautions.

This post was written by: