Being one of the top three most common web hacking techniques and most dangerous software weaknesses worldwide, it’s vital that your organization knows its implications and takes appropriate actions to address them immediately. It has been listed as number three in both the Common Weakness Enumeration (CWE) in 2022 and 2023, and in the Open Worldwide Application Security Project (OWASP) in 2021, where it was the top #1 in 2017. And there’s a reason for that: it is one of the easiest to exploit vulnerabilities due to the growth of internet-facing applications and the available tools and details to exploit it. This vulnerability has existed for over 20 years, so you may think why developers haven’t done their job in avoiding it? Or you may think this was a thing of the past, a mistake only made by bad programmers; couldn’t be further from the truth.
What is SQL Injection?
Simply put, it is to send a database command from the Internet where it must not be allowed, and which can cause harm. Note the Internet can be anything: a web page, an API, a Power BI report, or anything where a user can send data. The harm can be data theft, sabotage, data held for a ransom, or allow the intruder via database-to-os commands to gain access to a broader set of accounts and privileges, compromising the entire underlying infrastructure. Also note it doesn’t make a difference if the database is relational, NoSQL, distributed, Azure managed, etc.
Types of SQL injections
There are many types of injections: based on the SQL command they use, the method to inject data, the exploit method, etc. Here we list the most common ones, but every day new methods are found being used.
Based on the SQL query language used:
- UNION query-based: the most popular, includes the UNION reserved keyword to include extra SELECT statements and get data not intended to be revealed, like names of databases, tables, columns, etc.
- Error-based: if the application returns errors to the user, the attack can include a command that is later returned as an error message and which includes data not intended to be disclosed or allows identifying the underlying systems versions, allowing the exploit of other vulnerabilities.
- Boolean-based blind: because the web site doesn’t return any indication that it is vulnerable, the attacker sends a condition that evaluates to FALSE and then one that evaluates to TRUE, and based on the response and delay, he can infer if the site is vulnerable or even allows guessing the names of databases, tables, columns, etc.
- Time-based blind: because the web site doesn’t return any indication that it is vulnerable, the attacker sends a time-intensive operation; if the site doesn’t return an immediate response, it means it is vulnerable.
- Stacked queries: multiple queries are concatenated in succession, then the result of the first one is returned but the others are executed anyway.
- Out-of-band: data is exfiltrated to an external host which is in control of the attacker.
Based on the method used to inject data:
- Based on user input: passed through input forms without sanitizing them.
- Based on cookies: modifies cookies values which are loaded and used by applications.
- Based on HTTP headers: fake headers are read and used by applications.
- Second-order: it is inserted in a valid context and lies dormant until a condition changes and turns it malicious.
Based on the exploit method is SQL smuggling, which avoids all countermeasures and can only be detected when it reaches the database. It works by sending invalid but similar characters forcing the database to use a different character set (homoglyphic transformation) which turns them into valid ones.
What’s the worst that can happen with SQL Injection?
It really depends on the attacker creativity, but here’s a list of what an attacker can gain and from there, it’s just a matter of time to do the greatest damage possible.
OWASP provides a list of strides an attacker can make:
- Spoofing: become another user or assume other user’s permissions.
- Tampering: change data before being stored or processed.
- Repudiation: cause insufficient auditing or recordkeeping to impute responsibility, causing losses due to void transactions or incorrect balances, or even transfer money to an account set up by the attacker.
- Information disclosure: obtain private or sensitive information.
- Denial of service: cause high loads on the infrastructure or render a system inoperable.
- Elevation of privilege: elevate rights to an administrative account and gain control.
SQL Injection Examples
On May 31, 2023, Progress reported a vulnerability in its MOVEit Transfer and MOVEit Cloud products that could lead to escalated privileges and potential unauthorized access to the environment. The site ProjectDiscovery details an analysis on how this could be exploited. If you read through the analysis, you will see it’s not just setting variables and sending them in plain text; attackers evaluate each and every response from the system until they find an attack vector that allows them to insert malicious code, and they use automated tools to ease this task.
The site SecurityScorecard shows a list of 1,790 vulnerabilities found in 2022. You could think these attacks are for non-IT enterprises, but it’s rather the other way; there are several IT-related known names including antivirus enterprises. This is just a list of the letter “A”: Accruent, Acer, ACL, Acme, ACS, Activision, Adobe, Advantech, Alcatel-Lucent, AlienSoftCorp, AllegroSoft, Amazon AWS, AMD, Anaconda (Python), Android, Angular, Ansible, Apache, Apex, Apple, Arcgis, Archlinux, ARM, Arpanet, Aspose, Asterisk, Astonsoft, Asus, Atanasoft, Atari, ATI, Atlassian, Atom, Atos, ATX, Aurora, Avast, AVG, Azure. You can search their vulnerabilities and learn from their resolutions, and you must be searching for your providers, third-party software, and gadgets you use daily to know their vulnerabilities.
Acunetix has a very good example of a SQL injection attack, which shows how to retrieve the admin user password hash, crack it, login to a WordPress site as admin, upload a PHP backdoor, create a reverse shell connection, and with this escalate privileges using an exploit to get root access.
The site InfoSecWriteUps shows a learning lab on how to send obfuscated malicious code which bypasses firewalls intended to block this type of attack.
The PenTest user Fahad Almulhim shows how to use multiple penetration tools to retrieve the operating system and database information, and with this information, execute commands on the operating system.
This StackOverflow answer shows how vulnerable database versions and character sets can be exploited, and even if your code is “secure” it can be circumvented.
How to prevent SQL injection attacks
The first step is to apply the least privilege principle directly on the database, which states that no account should have more access than the minimum necessary to perform their work. It’s a common practice to grant the “SysAdmin” or the “dbowner” privilege to a website service account, but this opens the door to return the schema information and have unlimited access to any table. It is the job of the database administrator to correct this security concern.
Once that is addressed, the developers must learn how to validate their existing and new code:
- Use prepared statements with parametrized queries. Learn that ad-hoc and linq queries are the most vulnerable of all.
- Learn that object-relational mappers (ORMs) like Entity Framework and NHibernate are equally vulnerable.
- Learn that stored procedures are less vulnerable but still susceptible.
- Validate input against allow or deny lists, being allow lists the preferred ones.
- Escape all user supplied input.
Checklist of what to look for in the code?
OWASP provides a list of what to look for in the source code at a high level:
- Data Flow
- Are user inputs used to directly reference business logic?
- Is there potential for data binding flaws?
- Is the execution flow correct in failure cases?
- Authentication and access control
- Does the design implement access control for all resources?
- Are sessions handled correctly?
- What functionality can be accessed without authentication?
- Existing security controls
- Are there any known weaknesses in third-party security controls?
- Is the placements of security controls correct?
- Architecture
- Are connections to external servers secure?
- Are inputs from external sources validated?
- Configuration files and data stores
- Is there any sensitive data in configuration files?
- Who has access to configuration or data files?
These translate to the following low-level ones:
- Never return from the database more information than what is going to be displayed in a web page. If it’s only to compare data, then don’t return anything. There’s no need to return the credit card number or the password hash; in fact, any sensitive information must not be displayed in a web page so you must not return it from the database in the first place.
- Don’t trust information sent from trusted sources (even your own), treat all input as poisonous: zero trust.
- Validate all input: type, length, format, range, size, appropriate limits.
- Accept only expected values: reject binary data, escape sequences and comment characters.
- Validate XML documents against their schema.
- Never build SQL statements directly from user input.
- Implement multiple layers of validation: front end, back end, database, and any intermediary.
- Never concatenate user input that is not validated.
- Never disclose error messages which can leak information about the underlying infrastructure.
- Check all components are patched to the latest version, especially when a security vulnerability is found.
If you read through the guide, it also lists several configurations that need to be made at the application server security level: Microsoft IIS, J2EE, Oracle WebLogic, JBoss AS, Jetty, Apache Tomcat, etc.
From the database side, you must:
- Check for the user privileges and determine if they have the minimum possible.
- Check if the account used for linked servers has the minimum possible privileges.
- Encrypt the communication to the database. If necessary, encrypt the database.
- Do not store passwords in plain text.
- Do not store users and passwords alongside in the same table. Where the password is stored, encrypt that and use salt to make it harder to crack.
- If you can’t avoid displaying data, mask it.
- Review the alert log in real time.
- Review the system trace file in real time.
- Patch your database to the latest version as soon as possible, and patch it immediately when a vulnerability is found.
- Implement auditing and review it periodically.
- Backup often to a remote location which is not directly available to an attacker. This allows you to recover fast from an attack.
Red9 team is versed in all above and can assess your database security and provide you with a general scorecard in terms of how well you’re protected against this type of attack.
Vulnerable sections of TSQL code for SQL injection
OWASP provides a list of terms to search in your database stored procedures, functions, and cached execution plans:
- Exec
- Xp_cmdshell
- Exec sp_
- Select from
- Insert
- Update
- Delete from where
- Delete
- Execute sp_
- Exec xp_
- Exec @
- Execute @
- ExecuteStatement
- ExecuteSQL
This task needs to be done periodically and automatically.
Tools that can help
The tools below are developed and maintained by the community and can be used for sql injection testing:
- Web Security Testing Guide
- Zed Attack Proxy
- SQLmap
- The site ServerWatch lists several tools with their comparison.
References
https://cwe.mitre.org/top25/archive/2023/2023_top25_list.html
https://owasp.org/www-project-top-ten/
https://owasp.org/www-community/attacks/SQL_Injection
https://owasp.org/www-project-code-review-guide/
https://owasp.org/www-project-code-review-guide/assets/OWASP_Code_Review_Guide_v2.pdf
https://www.code-intelligence.com/blog/sql-injections
https://brightsec.com/blog/sql-injection-attack/
https://www.acunetix.com/websitesecurity/sql-injection/
https://www.rapid7.com/fundamentals/sql-injection-attacks/
https://www.crowdstrike.com/cybersecurity-101/sql-injection/
https://www.owasp.org/images/d/d4/OWASP_IL_2007_SQL_Smuggling.pdf
