SQL Injection Attack: How It Works, Examples and Prevention
What is a SQL Injection Attack (SQLi)?
SQL Injection attacks (or SQLi) alter SQL queries, injecting malicious code by exploiting application vulnerabilities.
Successful SQLi attacks allow attackers to modify database information, access sensitive data, execute admin tasks on the database, and recover files from the system. In some cases attackers can issue commands to the underlying database operating system.
The severe impact of these attacks makes it critical for developers to adopt practices that prevent SQL injection, such as parameterized queries, stored procedures, and rigorous input validation.
Want to know how to avoid SQL injection in your code? See our short version of the OWASP SQL injection prevention cheat sheet.
This is part of an extensive series of guides about data security.
In this article, you will learn:
- What Are SQL Queries?
- Impact of a Successful SQL Injection Attack
- How Does a SQL Injection Attack Work?
- Real-Life SQL Injection Attack Examples
- Types of SQL Injection Attacks
- SQL Injection Code Examples
- SQL Injection Prevention Cheat Sheet
- Preventing SQL Injection Attack with Bright
What Are SQL Queries?
SQL, which stands for Structured Query Language, is a language used to communicate with and manipulate databases. It is the standard language for relational database management systems and is used to perform tasks such as update data on a database, or retrieve data from a database.
SQL queries are the commands used to communicate with a database. They enable tasks like searching, updating, or retrieving data stored in a database. For instance, an eCommerce web application might use a SQL query to retrieve information about a specific product from the product database.
However, if an application is not securely designed, it might allow a user to input SQL queries directly. In that case, a malicious user can input a specially crafted SQL query that can lead to unauthorized access, data leakage, or even deletion of data. This is known as a SQL injection attack.
Impact of a Successful SQL Injection Attack
The consequences of a successful SQL injection attack can include:
- Stolen credentials—attackers can obtain credentials via SQLi and then impersonate users and use their privileges.
- Unauthorized access to databases—attackers can gain access to the sensitive data in database servers.
- Data alteration—attackers can alter or add new data to the accessed database.
- Data deletion—attackers can delete database records or drop entire tables.
Lateral movement—attackers can access database servers with operating system privileges, and use these permissions to access other sensitive systems.
How Does a SQL Injection Attack Work?
A SQL Injection attack involves inserting or “injecting” a SQL query via the input data from the client to the application. A successful attack allows an attacker to manipulate the SQL queries that an application makes to its database. It typically involves the following steps:
- Identification of vulnerable inputs: Attackers first identify inputs within the web application that are vulnerable to SQL injection. These inputs could be text fields in a form, URL parameters, or any other input mechanisms.
- Crafting the malicious SQL query: Once a vulnerable input is identified, attackers craft a SQL statement intended to be inserted into the query executed by the application. This statement is designed to modify the original SQL query to perform actions unintended by the application developers.
- Bypassing application security measures: Attackers often have to bypass security measures like input validation or escaping special characters. They achieve this through techniques like string concatenation or utilizing SQL syntax to comment out parts of the original query.
- Executing the malicious query: When the application executes the SQL query, it includes the attacker’s malicious input. This modified query can perform actions such as unauthorized viewing of data, deletion of data, or even database schema alterations.
- Extracting or manipulating data: Depending on the attack, the outcome might be the extraction of sensitive information (like user credentials), altering existing data, adding new data, or even deleting significant portions of the database.
- Exploiting database server vulnerabilities: Advanced SQL injections may exploit vulnerabilities in the database server, extending the attack beyond the database to the server level. This can include executing commands on the operating system or accessing other parts of the server’s file system.
This process leverages the dynamic execution of SQL in applications where user inputs are directly included in SQL statements without proper validation or escaping. It exploits the way SQL queries are constructed, often in a way that the developers did not anticipate.
Real-Life SQL Injection Attack Examples
Over the past 20 years, many SQL injection attacks have targeted large websites, business and social media platforms. Some of these attacks led to serious data breaches. A few notable examples are listed below.
Breaches Enabled by SQL Injection
- GhostShell attack—hackers from APT group Team GhostShell targeted 53 universities using SQL injection, stole and published 36,000 personal records belonging to students, faculty, and staff.
- Turkish government—another APT group, RedHack collective, used SQL injection to breach the Turkish government website and erase debt to government agencies.
- 7-Eleven breach—a team of attackers used SQL injection to penetrate corporate systems at several companies, primarily the 7-Eleven retail chain, stealing 130 million credit card numbers.
- HBGary breach—hackers related to the Anonymous activist group used SQL Injection to take down the IT security company’s website. The attack was a response to HBGary CEO publicizing that he had names of Anonymous organization members.
Notable SQL Injection Vulnerabilities
- Tesla vulnerability—in 2014, security researchers publicized that they were able to breach the website of Tesla using SQL injection, gain administrative privileges and steal user data.
- Cisco vulnerability—in 2018, a SQL injection vulnerability was found in Cisco Prime License Manager. The vulnerability allowed attackers to gain shell access to systems on which the license manager was deployed. Cisco has patched the vulnerability.
- Fortnite vulnerability—Fortnite is an online game with over 350 million users. In 2019, a SQL injection vulnerability was discovered which could let attackers access user accounts. The vulnerability was patched.
Types of SQL Injection Attacks
There are several types of SQL injection:
- Union-based SQL Injection – Union-based SQL Injection represents the most popular type of SQL injection and uses the UNION statement. The UNION statement represents the combination of two select statements to retrieve data from the database.
- Error Based SQL Injection – this method can only be run against MS-SQL Servers. In this attack, the malicious user causes an application to show an error. Usually, you ask the database a question and it returns an error message which also contains the data they asked for.
- Blind SQL Injection – in this attack, no error messages are received from the database; We extract the data by submitting queries to the database. Blind SQL injections can be divided into boolean-based SQL Injection and time-based SQL Injection. Learn more in our guide to Blind SQL injection.
SQLi attacks can also be classified by the method they use to inject data:
- SQL injection based on user input – web applications accept inputs through forms, which pass a user’s input to the database for processing. If the web application accepts these inputs without sanitizing them, an attacker can inject malicious SQL statements.
- SQL injection based on cookies – another approach to SQL injection is modifying cookies to “poison” database queries. Web applications often load cookies and use their data as part of database operations. A malicious user, or malware deployed on a user’s device, could modify cookies, to inject SQL in an unexpected way.
- SQL injection based on HTTP headers – server variables such HTTP headers can also be used for SQL injection. If a web application accepts inputs from HTTP headers, fake headers containing arbitrary SQL can inject code into the database.
- Second-order SQL injection – these are possibly the most complex SQL injection attacks, because they may lie dormant for a long period of time. A second-order SQL injection attack delivers poisoned data, which might be considered benign in one context, but is malicious in another context. Even if developers sanitize all application inputs, they could still be vulnerable to this type of attack.
SQL Injection Code Examples
Let’s look at two common examples of SQL injection attacks.
Example 1: Using SQLi to Authenticate as Administrator
This example shows how an attacker can use SQL injection to circumvent an application’s authentication and gain administrator privileges.
Consider a simple authentication system using a database table with usernames and passwords. A user’s POST request will provide the variables user and pass, and these are inserted into a SQL statement:
sql = "SELECT id FROM users WHERE username='" + user + "' AND password='" + pass + "'"
The problem here is that the SQL statement uses concatenation to combine data. The attacker can provide a string like this instead of the pass variable:
password' OR 5=5
The resulting SQL query will be run against the database:
SELECT id FROM users WHERE username='user' AND password='pass' OR 5=5'
Because 5=5 is a condition that always evaluates to true, the entire WHERE statement will be true, regardless of the username or password provided.
The WHERE statement will return the first ID from the users table, which is commonly the administrator. This means the attacker can access the application without authentication, and also has administrator privileges.
A more advanced form of this attack is where the attacker adds a code comment symbol at the end of the SQL statement, allowing them to further manipulate the SQL query. The following will work in most databases including MySQL, PostgreSQL, and Oracle:
' OR '5'='5' /*
Learn more in our detailed guide to sql injection test.
Example 2: Using SQLi to Access Sensitive Data
In this example, the following code obtains the current username, and searches for items matching a certain item name, where the owner is the current user.
...
string userName = ctx.getAuthenticatedUserName();
string query = "SELECT * FROM items WHERE owner = "'"
+ userName + "' AND itemname = '"
+ ItemName.Text + "'";
...
This code has the same weakness as in the previous example – the use of concatenation. After combining the username and item name, the code creates the following query:
SELECT * FROM items
WHERE owner =
AND itemname = ;
If the attacker provides the following string for itemname:
Widget' OR 5=5
The SQL statement becomes:
SELECT * FROM items
WHERE owner = 'John'
AND itemname = 'Widget' OR 5=5';
Which is the same as: SELECT * FROM items;
This means the query will return the data of the entire table, giving the attacker unauthorized access to sensitive data.
Example 3: Injecting Malicious Statements into Form Field
This is a simple SQL injection attack based on user input. The attacker uses a form that requires first name and last name as inputs. The attacker inputs:
- First name:
malicious'ex - Last name:
Smith
The attacker’s first name variable contains a malicious expression, which we denoted as ‘ex. The SQL statement that processes the form inputs looks like this:
SELECT id, firstname, lastname FROM authors
Once the attacker injects a malicious expression into the first name, the statement looks like this:
SELECT id, firstname, lastname FROM authors WHERE firstname = 'malicious'ex' and lastname ='newman'
The database identifies incorrect syntax due to the single apostrophe, and tries to execute the malicious statement.
For many more examples of malicious SQL code, see our detailed guide to SQL injection payloads.
SQL Injection Prevention Cheat Sheet
This is a summarized version of the excellent OWASP SQL injection prevention cheat sheet.
Defense Option 1: Prepared Statements (with Parameterized Queries)
Prepared statements are easy to learn and use, and eliminate the problem of SQL injection. They force you to define SQL code, and pass each parameter to the query later, making a strong distinction between code and data.
If an attacker supplies a malicious string like in the above examples, for example providing John' or 1=1 for a username, the prepared statement will evaluate this as a literal string. It will look for a user named John' or 1=1 (and fail, because no such user exists) instead of evaluating this statement as code.
Prepared statements are available in all programming languages. Here is an example in Java. To be on the safe side, OWASP recommends validating the input parameter just in case.
// Separate definition of input variable
String custname = request.getParameter("customerName");
// Separate definition of SQL statement
String query = "SELECT account_balance FROM user_data WHERE user_name = ? ";
// PreparedStatement command securely combines inputs and SQL syntax
PreparedStatement pstmt = connection.prepareStatement( query );
pstmt.setString( 1, custname);
ResultSet results = pstmt.executeQuery( );
Defense Option 2: Stored Procedures
Stored procedures are similar to prepared statements, only the SQL code for the stored procedure is defined and stored in the database, rather than in the user’s code. In most cases, stored procedures can be as secure as prepared statements, so you can decide which one fits better with your development processes.
There are two cases in which stored procedures are not secure:
- The stored procedure includes dynamic SQL generation – this is typically not done in stored procedures, but it can be done, so you must avoid it when creating stored procedures. Otherwise, ensure you validate all inputs.
- Database owner privileges – in some database setups, the administrator grants database owner permissions to enable stored procedures to run. This means that if an attacker breaches the server, they have full rights to the database. Avoid this by creating a custom role that allows storage procedures only the level of access they need.
Here is an example of a stored procedure in Java (Java calls it a CallableStatement). We assume that the sp_getAccountBalancer stored procedure implements the same logic as the prepared statement in option 1 above.
// Separate definition of user inputs
String custname = request.getParameter("customerName");
// Executing the stored procedure sp_getAccountBalancer
try {
CallableStatement cs = connection.prepareCall("{call
sp_getAccountBalance(?)}");
cs.setString(1, custname);
ResultSet results = cs.executeQuery();
// result set handling
} catch (SQLException se) {
// logging and error handling
}
Learn more in our detailed guide to union sql injection.
Defense Option 3: Allow-list Input Validation
This is another strong measure that can defend against SQL injection. The idea of allow-list validation is that user inputs are validated against a closed list of known legal values.
For example, if a user input is used to select a database table, you can use code like this to ensure that it can only match one of several, known table names:
String tableName;
switch(PARAM):
case "Value1": tableName = "fooTable";
break;
case "Value2": tableName = "barTable";
break;
...
default : throw new InputValidationException("unexpected value
Provided" + " for table name");
Another safe way to handle user inputs is to convert them to a non-string form. For example, if the user input determines whether the query should be ordered in ascending or descending order, the input can be converted to a boolean. And then this boolean value is used to determine the sort order:
public String someMethod(boolean sortOrder) {
String SQLquery = "some SQL ... order by Salary " + (sortOrder ? "ASC" :
"DESC");`
...
Defense Option 4: Escaping All User-Supplied Input
Escaping means to add an escape character that instructs the code to ignore certain control characters, evaluating them as text and not as code.
This option is the least secure of the four, and should only be used as a last resort. This is because escaping user input is only effective if the code escapes all possibilities of control characters, and attackers come up with numerous creative ways to inject them. Therefore, OWASP does not recommend this method and advises the use of options 1 or 2 above.
For example, in MySQL there are two escaping modes – ANSI_QUOTES SQL mode and MySQL mode:
- in ANSI_QUOTES mode, to escape control characters, encode all ‘ (single tick) characters with ” (two ticks)
- In MySQL mode, escape the following characters:
- [the number zero, not the letter O]
- b
- t
- n
- r
- Z
- “
- %
- ‘
- \ [escaping a single slash character]
- _
- Replace all other non-alphanumeric characters with ASCII values
- Replace numbers less than 256 with c where ‘c’ is the original number
OWASP provides the free, open source Enterprise Security API (ESAPI), which can help you implement escaping in legacy database code. It provides codecs for popular databases, which have escaping for all unsafe control patterns.
ESAPI currently supports Oracle and MySQL, and will soon support encoders for SQL Server and PostgreSQL.
Preventing SQL Injection Attack with Bright
Bright Dynamic Application Security Testing (DAST) helps automate the detection and remediation of many vulnerabilities including SQLi, early in the development process, across web applications and APIs.
By shifting DAST scans left, and integrating them into the SDLC, developers and application security professionals can detect vulnerabilities early, and remediate them before they appear in production. Bright completes scans in minutes and achieves zero false positives, by automatically validating every vulnerability. This allows developers to adopt the solution and use it throughout the development lifecycle.
Scan any web app, or REST and GraphQL APIs to prevent SQL injection vulnerabilities with Bright!
See Additional Guides on Key Data Security Topics
Together with our content partners, we have authored in-depth guides on several other topics that can also be useful as you explore the world of data security.
Incident Response
Authored by Cynet
bject storage
Authored by Cloudian
- What is Object Storage: Definition, How It Works and Use Cases
- Object Storage vs. File Storage: What’s the Difference?Â
- Object Storage vs. Block Storage: Head to Head
IoT Security
Authored by Sternum IoT
- Understanding IoT Security: Challenges, Standards & Best PracticesÂ
- Top 10 IoT Security Challenges and How to Solve Them
- Top 10 IoT Vulnerabilities and How to Mitigate ThemÂ
