How To Prevent SQL Injections

Being one of the top three most common web hacking techniques and the most dangerous software weaknesses worldwide, it’s vital that your organization understands the implications of SQL injections 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 ways 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 wonder 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; but that couldn’t be further from the truth.

What is SQL injection?

Simply put, SQL injection 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 or 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 new methods are found and used every day.

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 website 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 website 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

This is also known as SQL smuggling. It avoids all countermeasures and can only be detected when it reaches the database.

SQL injection works by sending invalid but similar characters forcing the database to use a different character set (homoglyphic transformation) which turns them into valid ones.

• 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.

What’s the worst that can happen with SQL injection?

It really depends on the attacker’s creativity.

Here’s a list of what an attacker can gain, and from there it’s just a matter of time before they do the greatest damage possible.

OWASP provides a list of the types of attacks:

• 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

Progress

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.

SecurityScorecard

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 around; 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. I recommend you search for the providers, third-party software, and gadgets you use daily to know their vulnerabilities.

Acunetix

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.

InfoSecWriteUps

The site InfoSecWriteUps shows a learning lab on how to send obfuscated malicious code which bypasses firewalls intended to block this type of attack.

PenTest user

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.

StackOverflow answer

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

1. Apply the least privilege principle

The first step is to apply the least privilege principle directly on the database. This must state 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.

2. Validate existing and new code

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, making allow lists the preferred ones.
• Use escaping special characters for all user supplied input.

Checklist of what to look for in the code

OWASP list

OWASP provides a list of what to look for in the source code.

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.
  • Who has access to configuration or data files?

Low level

• Limit information
  Never return more information from the database 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.
• Zero trust
  Don’t trust information sent from trusted sources (even your own), treat all input as poisonous.
• Validate all input
  Type, length, format, range, size, appropriate limits.
• Accept only expected values
  Reject binary data, escape sequences and comment characters.
• XML documents
  Validate XML documents against their schema.
• User input
  • Never build SQL statements directly from user input.
  • Never concatenate user input that is not validated.
• Implement multiple layers of validation
  Front end, back end, database, and any intermediary.
• Error messages
  Never disclose error messages which can leak information about the underlying infrastructure.
• Latest versions
  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

Take these steps from the database side:

• 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.

Vulnerable sections of T-SQL code for SQL injection

Terms to search for

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
• ServerWatch list of tools with their comparison

References

• 2023 CWE Top 25 Most Dangerous Software Weaknesses
• OWASP Top Ten
• OWASP SQL Injection
• OWASP Code Review Guide
• OWASP Review Code v2.0
• Are SQL Injections Still a Thing?
• SQL Injection Attack: How It Works, Examples and Prevention
• CWE Top 25 for 2023: Buffer overflows, XSS, SQL injection lead the pack
• What is SQL Injection (SQLi) and How to Prevent It
• SQL Injection Attacks (SQLi)
• SQL Injection (SQLI): How to Protect Against SQL Injection Attacks
• SQL Smuggling: The Attack That Wasn’t There
• SQL Injection Detection Tools