What Is SQL Injection? Identification & Prevention Tips

SQL injection is among the top 10 open web application security project (OWASP) vulnerabilities. Applications tend to be at risk of high-profile vulnerabilities like SQL injection attacks. The results of a SQL injection attack vary, ranging from retrieving confidential data to altering an application’s logic.

In this article, we'll cover a basic overview of SQL injections, identify who's most vulnerable, and give tips for identifying and preventing attacks.

• Overview: What is SQL?
• What is SQL injection (SQLI)?
• What does an SQL injection attack do?
• Who is vulnerable to SQLI attacks?
• SQLI examples
• How to identify an SQL injection vulnerability
• SQL injection cheat sheet: How to prevent SQLI
• SQL injection attack prevention checklist

Overview: What is SQL?

SQL is a structured query language that functions to manipulate, store, and retrieve data concerning the database. All relational database systems use SQL, including:

• MySQL
• Microsoft Access
• Oracle
• Postgres
• Informix
• Sybase
• SQL servers

SQL is subject to commands like “create,” “select,” “insert,” and “delete” and is needed in building a database-interactive website. 

What is SQL injection (SQLI)?

A SQL injection is a vulnerability that affects applications by using malicious SQL codes to manipulate the database. This allows attackers to modify the ways applications use queries to the database.

In a SQL injection, attackers can:

• View and modify personal data
• Perform a denial-of-service
• Compromise back-end infrastructure
• Alter the application's behavior

What does an SQL injection attack do?

A successful SQL injection grants the attacker database administrative rights resulting in unauthorized access to confidential data such as passwords, usernames, financial credentials, and addresses. SQL injections have resulted in some of the most high-profile data breaches in history, including The Pirate Bay. 

A SQL injection can grant the attacker a persistent back door to the vulnerable systems of the organization, resulting in long-term malicious attacks that can go unnoticed for an extended period of time.

Retrieves hidden data

If an attacker modifies SQL queries to return additional results, the result could be retrieval of hidden data. 

Subverts application logic

Attackers can change the SQL queries to modify the application's logic. 

Retrieves data from other database tables

When SQL query results can be channeled within the application's responses, a successful SQL injection can retrieve data from other database tables. This result is made possible by UNION SQL injection, which we’ll cover in more detail below.

Who is vulnerable to SQLI attacks?

Attackers use SQL injections to perform destructive acts such as stealing credentials, accessing databases, altering data, deleting data, accessing networks, and escalating privileges. SQL injections can target:

• Financial institutions
• Government infrastructure
• Social media platforms
• Businesses
• School infrastructure
• Websites
• iOS and Android apps
• Network equipment such as switches and routers

SQLI examples

SQL injection attacks can come in all shapes and sizes. Examples of SQL injections include:

• Retrieval of hidden data
• Subverting application logic
• UNION attacks
• Database examining
• Blind SQL injection
• Stored SQL injection

1. Retrieval of hidden data

Attackers can modify SQL queries to return additional, confidential data, and then download that data from the database.

Consider a vulnerable event page that only displays confirmed and published events but also contains a draft of upcoming events being planned. When selecting a published event, the query appears as:

SELECT * FROM planned_events WHERE category = ‘party1’ AND event_status = ‘published’

 An attacker can craft an attack that will retrieve hidden data (events in the draft) by the use of SQL comment sequence — hence the query:

SELECT * FROM planned_events WHERE category = ‘party1’--’

2. Subverting application logic

To subvert an application's logic, an attacker alters the interface query. Consider a vulnerable application that authorizes users to log in using passwords and usernames by implementing the query:

SELECT * FROM users WHERE username = ‘Alvin’ AND password = ‘abc123’

Attackers can craft queries to log in without the use of a password just by applying SQL comment sequence eliminating a password check, as shown below:

SELECT * FROM users WHERE username = ‘Alvin’--’ AND password = ‘’

3. UNION attacks

These attacks involve using the keyword “UNION” to retrieve data from other database tables. This keyword allows attackers to execute additional or single SELECT queries, and actual query results are appended:

SELECT a, b FROM table1 UNION SELECT c, d FROM table2

4. Database reconnaissance 

In a reconnaissance attack, hackers gain access to information about the version and database structure. This lets them exploit version or database-specific bugs or gain insight into what other data is held in the database.

In a vulnerable web application, an attacker can access a database version by using queries like:

SELECT * FROM @@version

An attacker can also query the list of database tables or schemas that exist:

SELECT * FROM information_schema.tables

5. Blind SQL injection

When web applications are vulnerable to SQL injections, but results of SQL queries and database errors are not contained in the HTTP responses, this is known as a blind SQL injection. These vulnerabilities can compromise web applications in ways other than a data breach. 

Consider an attack like:

UPDATE users SET password = ‘password’ WHERE name = ‘admin’

Which would then allow an attacker to log in via the admin account.

6. Stored SQL injection (second-order)

In first-order SQL injection, applications use HTTP requests to receive user input. During processing, information is included in the SQL query in an unprotected manner.

In second-order SQL injection, applications use HTTP requests to receive user input and store it for later use, resulting in stored/second-order SQL injection. 

The next time an application handles another HTTP request, stored data is retrieved and incorporated into the SQL query harmfully.

How to identify a SQL injection vulnerability

There are different ways to identify SQL injection bugs in applications depending on the type of SQL injection itself. This includes the use of automation tools and manual detection of SQL injection. In automation, you can use the following tools to identify SQL injection vulnerabilities:

• SQLMap
• Burp Scanner
• SQL Injection
• BBQSQL
• NoSQLMap
• Damn Small SQLi Scanner
• explo
• Blisqy
• Leviathan

Automated tooling typically identifies that an application is vulnerable to SQL injection. 

This type of detection involves numerous tests at each possible SQL injection point in an application, including:

• Looking for anomalies and errors by submission of a single quote character
• Submission of time delay payloads and determining response time differences
• Submission of out-of-band application security testing (OAST) payloads and checking for out-of-band network interactions

Once it’s determined that an attack is possible manual queries are written specifically to the application’s data structures, database, and version to launch attacks or exfiltrate data.

SQL injection cheat sheet: How to prevent SQLI

The impacts of SQL injections are varied and require different preventive measures, including:

1. Using parameterized and reusable SQL queries or prepared statements. This forces developers to craft SQL commands and user-rendered data independently, resulting in SQL injection mitigation.
2. Input validation, which involves processes of testing inputs received by the applications for compliance against a standard defined within the application.
3. Using stored procedures, which prevents some instances of SQL injection vulnerabilities. This is achieved by limiting statements that are passed toward the stored procedures.
4. Developers using object-relational mappers like Hibernate to develop database queries in safe ways, preventing SQL injection bugs.
5. Using Web Application Firewalls (WAF) to help in filtering out SQL injection attacks.
6. Using web frameworks, which can prevent SQL injection vulnerabilities. In most cases, web frameworks come with safe coding measures. Web frameworks also avoid the injection of SQL vulnerabilities using parameterized queries and stored procedures in the database. Web frameworks include:

• ASP.NET
• Laravel
• Django
• Rails
• NodeJS                

Using web servers to prevent SQL injection vulnerabilities

Web servers such as the ones below play an essential role in preventing SQL injection vulnerabilities:

Apache web server

ModSecurity gives a default set of rules essential in filtering basic SQL injection attacks on the involved infrastructure.

Nginx

Nginx does not natively prevent SQL injection attacks. However the module, Naxsi, prevents attacks by blocking different SQL injection characteristics. It filters injection keywords in the primary URL and disables error messages, preventing the attack. 

Internet information server (IIS)

IIS has the ability to prevent SQL injections by filtering inbound HTTP requests. Make sure to use the latest version of IIS.

NoSQL and SQL injection

NoSQL refers to nonrelational data stores that support columnar or document models of data management.

While NoSQL databases like Redis, Cassandra, or MongoDB don't technically use “SQL queries”, they still do queries based upon the input, making them vulnerable to injection attacks if they lack proper input sanitization. 

NoSQL injection allows hackers to insert malicious code into commands for datastores and can affect web applications built on the following stacks:

• MongoDB
• Angular
• Node
• Express

SQL and NoSQL injections are similar as both attacks take advantage of poorly-sanitized user input when building queries. Preventing NoSQL injections involves similar measures to preventing SQL.

This includes the use of:

• Prepared statements
• Input validation
• Web Application Firewalls
• Web frameworks

SQL injection attack prevention checklist

Identifying a SQL injection attack depends on having the proper measures in place ahead of time.

This checklist will help you trace the attack execution and point out places you need to add a security layer.

• Database
  • Enable database logging
  • Have a database backup
  • Disable unused features in the database like full text search, system calls, etc.
  • Update database drivers regularly.
  • Enable database connection filtering procedures.
  • Set appropriate and granular user permissions

• Application
  • Manually check for SQL injection vulnerable endpoints
  • Use filtering options
  • Use parameterization options
  • Code review to identify SQL injection points
  • Enable application logging
  • Use database calls only when needed

• Web application server/firewall
  • Update your web servers and firewalls regularly
  • Use rate limit measures to avoid repeated SQL injection attempts
  • Set alerts on SQL injection patterns
  • Use SQL injection WAF pre-filters

Final thoughts

SQL injection attacks are a severe cybersecurity threat and companies and organizations need to take various measures to prevent these attacks.

The Varonis Data Security Platform offers data protection, threat detection and response, and highly skilled data privacy and compliance services, which can help reduce the risk of SQL injection attacks.