SQL injections are a widespread type of vulnerability in websites, which have high damaging potential. This article is exactly about this type of injections and shows what they are, why they can occur and how they can be fixed.
Table of contents
What are SQL Injections?
As soon as an application needs to access a database and user input is used, e.g., to perform a search query, SQL injection is potentially possible if the query is not made securely. With such an injection, it may be possible for an attacker to gain access to the database, read data, manipulate it, or in the worst case even get an interactive command line on the database server. Despite the great potential for damage, these vulnerabilities are still common. OWASP Top 10 ranks SQL injection as the 3rd most common security risk (along with other injections): https://owasp.org/www-project-top-ten/
SQL injections can be exploited manually or by using tools. The best-known tool is SQLmap – an automatic tool for exploiting SQL injections and hijacking a database. With such a tool, most vulnerabilities can be exploited easily and quickly. If filters are in place that restrict user input to prevent SQL injections, these often have to be bypassed manually.
Here is an example where a login is bypassed to gain access to the administrator user with an SQL injection. Administrator is specified as the username (without specifying a password). The login sends the following information to the server:
administrator
The following SQL command is executed in the database in the background:
SELECT * FROM users WHERE username = 'administrator' AND password = ''
The login will not be successful, because no password was specified. Now an attempt is made to bypass the login by means of an SQL injection. One possibility is to comment out the rest of the SQL query. A comment in SQL is initiated with –. The username in the query must still be terminated with a ‘ for the comment to be executed. Together this gives the following input in the username field:
administrator'--
On the server, the query is commented out so that the password no longer matters. This is a very simple bypass for a login function, as long as no security measures have been built in. The following SQL command is executed in the background:
SELECT * FROM users WHERE username = 'administrator'--' AND password = ''
The server now interprets the request as follows (since the rest is commented out):
SELECT * FROM users WHERE username = 'administrator'
An attacker is logged in, although the password is not known and has not been entered. At Oneconsult, we test web applications for SQL injections like this one and even more complicated ones, where protection mechanisms are also implemented.
Why Do SQL Injections Occur?
SQL injections occur when user input is not cleaned up. An example is the ‘ character, which can trigger many SQL injections. Such a special character is used to inject an SQL command, which is executed by the SQL interpreter. Other characters that can trigger an SQL injection are \, ” or ;. Sometimes no special character is needed at all (if the values are not strings but numeric values). An example would be the following query:
SELECT * FROM users WHERE user_id = [ID]
They also occur when the SQL command is simply composed of strings consisting of user input. You can find them manually in the code as well as during a penetration test. More complicated SQL injections are more likely to be found during a code review. During a code review, it can be examined more closely how user input is processed.
How To Prevent SQL Injections
There are several ways to prevent SQL injections. One possibility is the so-called SQL Prepared Statements, also called parameterized queries. Instead of using concatenated strings, the SQL query is already prepared, and user input is added afterwards. Here is a possible code that is vulnerable to SQL injection:
String query = "SELECT * FROM users WHERE username = '"+ username + "'"; Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery(query);
This Java code can now be rewritten as follows to prevent injection:
PreparedStatement statement = connection.prepareStatement("SELECT * FROM users WHERE username = ?"); statement.setString(1, username); ResultSet resultSet = statement.executeQuery();
This method works only in certain parts of the query (e.g., in WHERE, INSERT and UPDATE). To protect other parts, e.g., ORDER BY or table names, a whitelist should be used to restrict the allowed inputs. A blacklist approach is not recommended, because special characters that lead to SQL injections can be overlooked.
The OWASP cheat sheet can be of enormous help in preventing SQL injections: SQL Injection Prevention Cheat Sheet
Conclusion
All applications should be checked for SQL injection. Not only do they cause damage to customers or users, but they can also harm a company. Assuming a hospital is attacked by an SQL injection and all patient data is extracted from the database and published, this will harm all patients, but also the hospital in the form of reputational damage.
Ideally, all applications should be subjected to a penetration test. If this is not possible, at least business-critical applications should be tested. This not only looks for SQL injections, but also for various other vulnerabilities.
