Preventing Highly Common Cyberattacks: SQL Injections
Written By: Nazifa Alam
If an SQL injection attack is successful, it can lead to unauthorised access to the sensitive data of the targeted website’s browsers. The data which becomes accessible can include login details, financial details and other personal identifiable information. In some instances, the goal of the attacker(s) may be to gain a backdoor to the targeted organisation’s systems for regular access leading to long term unnoticed problems.
Types of SQL Injection Attack
The manner in which an SQL injection attack is executed depends on the type of the attack.
- In Band SQL Injection
This is the classic form of a SQL injection attack which enables the attacker(s) to execute the attack and seize the results through the same communication channel.
This type of attack is carried out in two ways:
- Error-Based SQL Injection – Which retrieves information about the targeted database from the displayed error messages.
- Union-Based SQL Injection – Where the attacker(s) chain the results (UNION ALL) of all the hijacked data with the legitimate results.
Both of these methods rely on the modification of the SQL being sent by the application and the errors and the returned data displayed in the browser. The success of the attack depends on the database developer failing to confine the values they use in their queries. Both of these types of attack are executed as a trial-and-error method, with the errors being traceable.
- Blind SQL Injection
Blind SQL injection is used to detect a delay or a change in the HTTP reply to identify the in-between queries and categorise them as either TRUE or FALSE. This type of SQL injection attack is used if the result or message is invisible to the attacker.
The blind SQL injection attack will produce errors such as several syntax errors and object-not-found errors as the success of the attack is identifiable by the length of time needed between the call and the returned error.
- Out of Band Injection
This form of attack involves an attacker using SQL Server extensions such as xp_[dirtree], xp_[cmd]shell and xp_[send]mail to send an ‘exfiltration’ request. This causes the results to be sent to the attacker by HTTP or DNS. From this point, the attacker then assesses which tools they have permission to use to prevent errors from being generated.
Detecting the Errors Which Indicate an Attack
Below is a list of errors which indicate an SQL injection attack:
- Error 18456 – For failed logins
- Errors 102 and 105 – A syntax error. These errors are normal to find in servers in development or test servers however not in a production database. If the frequency of these errors increases, it is an indicator of a SQL injection attack.
- Errors 208 and 2818 – For attempted access to an invalid item or a stored procedure that does not exist. This error is a definite characteristic of an SQL injection attack.
- Error 245 – For further information about the targeted database such as the name.
- Error 205 – Which appears if the attacker uses the UNION ALL SELECT phrase for more information about the database.
- Permission errors – Which provide a record of attempts committed by an attacker to access the targeted database or system.
It is important to note that not all errors will be recorded due to their perceived lack of severity. However, these unreported errors in particular must be monitored in order to prevent a blind SQL injection attack.
For this, extended events, specifically the sqlserver[.]error_[reported] event needs to be used. Instead of any errors rated as 20 and above being recorded, this event session allows for more specific errors to be detected.
The simplest way to set up the extended events session is to use SSMS which uses both a wizard and a New Session dialogue. Both of these enable the user to extend an event session, select and configure their chosen sessions. The user can capture a session and create their own sessions.
The user can create an event session in the following order; Management, Extend Events, Sessions and then clicking ‘New Session Wizard’ or the ‘New Session’ dialogue. The Properties of the event session can be edited from the same menu.
The following image is an example of an event session being created specifically to MonitorSuspiciousErrors:
Two important terms to cover are validation and sanitisation. Given the context, validation refers to the process of determining if the input meets a set of criteria for example, a string containing no single quotation mark or any other characters that should not be there. Sanitisation refers to the process in which the input is modified to ensure that it is valid.
The type of SQL injection attack intended depends on the type of the targeted database engine. For example, entering login details will prompt a website or an email platform to submit that data to a PHP file. In most websites and email platforms, a ‘remember me’ checkbox is presented. If ‘yes’ is selected then the data will be stored in a cookie.
In many cases, successful prevention of SQL injection attacks can be as a result of parameterising queries.
An example of a vulnerable code is as presented below:
This code is susceptible to being overwritten to prevent user interaction with the query structure. Below is how the re-write would appear as:
Parameterised queries can be used for any instances in which an unrecognised input appears as data within a query. This can include the WHERE clause, the values in an INSERT statement or an UPDATE statement.
For a parameterised query to be effective, the string that is used to create the query must not contain any changeable data from any origin and must be a hard-coded constant.
Having installed a good web application firewall is also important for preventing SQLi threats. Signatures that are recognised as legitimate can be permitted access however, malicious SQL queries can be blocked with this approach.
Regular updates should also be carried out to ensure that the latest security patches are installed, especially for all of the software components for any web applications.
Statements containing conditions that will always be true needs to be carefully monitored, specifically the ‘1=1’ statement. In the case that one appears in addition to the previously mentioned errors, it is a high likelihood that the system is under threat from an SQL injection.