SQL is a data management language that is used in websites/ web applications to communicate with the database to access, manage, update, retrieve, delete and manipulate data. SQL is used in websites/ web applications to provide customizable data views for each user based on user-provided parameters.
In SQLi attacks, the attacker injects malicious code/ un-sanitized inputs into the SQL statements by leveraging SQL injection vulnerabilities present in the website/ web application. By doing so, the attackers essentially override security measures such as authorization, authentication, password verification, etc., and gain access to the application’s backend database and sensitive/ confidential information contained there, that are not meant to be displayed.
Types of SQL injection vulnerability
Attackers can leverage the SQL injection vulnerabilities in a number of ways to breach security and access application databases.
Error-based SQLi:
The attackers cause the database to produce error messages and retrieve information about the database structure, tables names, and content, etc. Disabling error messages on production systems is key to stopping attackers from accessing the databases using this vulnerability.
Boolean-based SQLi:
Even when there are no visible error messages and the SQL query fails, attackers can still exfiltrate databases. Attackers can send SQL queries to the database prompting the web application to return a result. This result varies based on whether the query is true or false and based on the result, the information in the HTTP response will be modified or stay unchanged. The attacker can, thus, verify if their input led to a true or false result and if the website is vulnerable to an SQLi.
Time-based SQLi:
Here, the attacker, by sending an SQL Query, instructs the database to wait a few seconds before reacting. Based on the response (time is taken to respond), the attacker can gauge if the query is true or false. If the website is not vulnerable to SQLi, it will load instantly. Else, it will take time to respond.
Out-of-Band SQLi:
Attackers use this technique when the same channel cannot be used to launch the attack and gather information from the application database/ when the server is too slow or unstable. When certain features are enabled on the database server, this technique can be used to get the database server to directly transfer data to the attacker’s machine. If the attack is successful, the target will create a DNS or HTTP request to transfer the data.
Impact of SQLi vulnerabilities
Data loss and corruption, such attacks reveal sensitive data to the attacker and can lead to data loss and corruption. This can be further achieved by special cases of Injection like Second-order Injections.
Denial of service, cases of command injection, expression language injection may lead to the non-availability of the application server.
Access to complete database server; usually database users are provided with a basic role but if combined with privilege escalation then the role can be changed, and the database can be accessed as a higher role user. Thus, complete access to the database servers can be achieved using Injection cases.
The complete host takes over; since the application server is the main component facing the users here; the attack can be exploited to take over the host as well and data can be changed in the database/backend as per the convenience of the attacker.
As a result, SQLi attacks are costly for the business, not just financially, but in terms of loss of customer trust, brand image and loyalty, loss of employee trust, business reputation, etc.
How to identify SQLi vulnerabilities?
Being a known vulnerability, SQLi attacks can be prevented with a proactive approach and comprehensive web application security measures.
Identifying and continuously detecting SQL Injection vulnerabilities is crucial SQL injection prevention and securing web applications from potential attacks. To identify SQLi vulnerabilities, a simple automated scanner does not suffice because certain vulnerabilities may be missed and more importantly, the vulnerabilities that are found cannot be fixed immediately by a scanner. (A scanner only tells you there is a potential gap that an attacker can leverage.)
To effectively and continuously monitor traffic and requests, identify malicious payload and prevent SQLi vulnerabilities, you must have a comprehensive, intelligent and managed Web Application Firewall (WAF) in place. The best WAFs like AppTrana come with intelligent, automated scanners that will effectively and proactively identify these vulnerabilities while the WAF will patch these vulnerabilities immediately and block malicious actors from accessing the vulnerabilities or even snoop around.
Solutions like AppTrana also include pen-testing and regular security audits by certified security experts to enable businesses to proactively identify vulnerabilities that enable SQLi attacks to be orchestrated and that get missed by WAF and scanners.
How to identify a Template Injection?
Step 1:
Following is the normal flow of an application where a user tries to log in to the page and the application responds back with an HTTP status code 2000K, if the authentication requirements are correct. We can now assume that in the back end the following query may be running at the programming level:
#SQL query which will be vulnerable to SQLi
sql= SELECT id from users WHERE username=’request.getParameter(“username”)’ and password=’request.getParameter(“password”)’
Step 2:
In this scenario, the user tries some malicious inputs along with a valid input, Eg, Username = a’ or a’=’a’ and in this case application may respond back with some database error which will provide more insight to malicious user to try with other attack vectors because now the back end code will be:
sql= SELECT id from users WHERE username=’a’ or a’=’a’’ and password=’any random password’
so now the application may through a database error like,
#1064 – You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘a’ LIMIT 0, 25′ at line 1
Step 3:
Further, the attacker will try a malicious attack vector and will be able to retrieve sensitive application data. As in the above case of the SQL statement. an attacker may try with the input of username as ‘a’ and password as ‘a’ or 1=1 so the final SQL statement will look like;
sql= SELECT id from users WHERE username=’a’ and password=’a’ or 1=1
This will retrieve all the id from the user’s table in the database.
How to prevent SQL injection attacks?
SQL Injection attacks are caused mainly due to the application vulnerabilities that allow the entry of malicious code and other un-sanitized inputs in the user input fields like contact forms, submission forms, etc. So, an important SQL injection prevention measure is input validation or sanitization. This is, however, not the only or fool-proof measure to prevent these attacks as it is impossible to map out all legal and illegal inputs, not without too many false positives.
Other useful tips:
- Escape user inputs: This is the primary defense against an SQL Injection attack. Proper input sanitization should be considered when user input is taken and processed in the back end. Define business logic special characters and allow only such characters to be added from the client end. Proper sanitization of user data and commands is important in this case. Programming language provided libraries/functions should be used to sanitize data.
- Implement prepared statements with parameterized queries, prepared statements to ensure that an attacker is not able to change the intent of the query. Example. In the above shown cases of malicious vectors from attacker if he/she enters the input as a’ or 1=1 in the password field the backend will not consider the value for password parameter as a only but will consider the whole input a’ or 1=1 as single input and will search for this string in database.
- Stored procedures, Prepared statements are implemented at the programming language level and to better the security Stored procedures are defined and stored in a database and then called from the application. Stored procedures and prepared statements work only when implemented safely. ‘Implemented safely’ means the stored procedure does not include any unsafe dynamic SQL generation.
- Use a whitelist approach for server-side input validation, client-side input validations should always be backed with server-side input validation. There could be two approaches of input validation one is a blacklisting approach, and another is a whitelisting approach. In blacklisting approach special characters are mentioned which should not be accepted at server level but putting an extensive list of special characters is always difficult to be handled whereas for the better security implementation whitelisting approach is suggested as this will contain only the business allowed characters and others apart from the ones mentioned in this list will not be saved into database.
- Least privilege, least privileges should be provided to the users and only admins should be allowed to access the database as Database administrators. For better security implementation, IP access to the database should be restricted and only some set of IPs should be allowed to access the database.
Most importantly, it is indispensable to have a comprehensive, intelligent, and managed WAF that combines automation with the expertise of security professionals.
