What is a SQL Injection Attack?
By taking advantage of program flaws, SQL Injection attacks (also known as SQLi) change SQL queries and inject malicious code.
Successful SQLi attacks enable attackers to recover files from the system, access sensitive data, modify database information, and administer the database. Attackers may occasionally be able to send commands to the database's operating system.
SQL injection's effects on your applications
- Steal credentials – Through SQLi, attackers can gain credentials, which they can then use to pose as users and access resources.
- Access databases – Attackers can access the private information stored on database servers.
- Change or upload new data to the accessed database – attackers have this option.
- Delete data – Attackers are able to dump entire tables or delete database records.
- Attackers – possess the capacity to move laterally by gaining entry to database servers with operating system privileges and then utilizing these rights to breach other crucial systems.
Real-Life SQL Injection Attack Examples
Over the past 20 years, there have been numerous SQL injection attacks on large websites, business websites, and social media platforms. Serious data breaches were caused by several of these attacks. Here are a few significant instances.
Breaches Enabled by SQL Injection
- GhostShell attack — hacking collective APT Using SQL injection, Team GhostShell targeted 53 universities, stole and published 36,000 individual student, faculty, and staff records.
- Turkish government — RedHack collective, another APT organization, broke into the Turkish government website and deleted debt owed to government institutions via SQL injection.
- 7-Eleven breach — 130 million credit card numbers were stolen by a group of criminals that used SQL injection to break into corporate systems at multiple businesses, particularly the 7-Eleven retail chain.
- HBGary breach — The website of the IT security firm was taken down by hackers associated with the activist collective Anonymous using SQL Injection. The attack was in retaliation to the CEO of HBGary making public the fact that he knew the names of members of the Anonymous group.
Notable SQL Injection Vulnerabilities
- Tesla vulnerability — Security researchers revealed in 2014 that they had used SQL injection to break into Tesla's website, obtain administrative rights, and steal user information.
- Cisco vulnerability — 2018 saw the discovery of a SQL injection flaw in Cisco Prime License Manager. Attackers were able to acquire shell access to computers where the license management was installed because to the vulnerability. Cisco has fixed up the issue.
- Fortnite vulnerability — There are more than 350 million players of the online game Fortnite. A SQL injection vulnerability that allowed attackers to access user accounts was found in 2019. The problem has been fixed.
Types of SQL Injection Attacks
There are several types of SQL injection:
- Union-based SQL Injection – The most common method of SQL injection, known as union-based SQL injection, makes use of the UNION command. In order to retrieve data from the database, two select statements are combined in the UNION statement.
- Error-Based SQL Injection – This technique is only usable with MS SQL Servers. The malicious user in this attack makes an application display an error. Typically, when you query a database, it responds with an error message that includes the information you requested.
- Blind SQL Injection – In this attack, the database doesn't send any error warnings; instead, we extract the data by sending it queries. The two types of blind SQL injections are time-based SQL injection and boolean-based SQL injection. Read more about blind SQL injection in our guide.
SQL injection attack for beginner guide
The way that SQLi attacks inject data into the system can also be used to categorize them:
- SQL injection based on user input – Web applications receive user input through forms and send it to the database for processing. If the web application accepts certain inputs without sanitizing them, an attacker could insert harmful SQL statements.
- SQL injection based on cookies – SQL injection can also be prevented by altering cookies to "poison" database requests. Frequently, cookies are loaded by web applications and their data is used for database activities. Cookies could be altered by a malicious user or malware installed on a user's device to unexpectedly inject SQL.
- SQL injection based on HTTP headers – HTTP headers and other server variables can also be utilized for SQL injection. If an HTTP header-accepting web application takes input, malicious HTTP headers with arbitrary SQL can insert code into the database.
- Second-order SQL injection – Due to their potential to remain active for a long time, these SQL injection attacks may be the most sophisticated. Data that is contaminated by a second-order SQL injection attack may be benign in one context but malicious in another. Developers may still be exposed to this kind of attack even if they clean up all application inputs.
SQL Injection Code Examples
Let's examine two typical SQL injection attack examples.
Example 1: Authenticating as an administrator with SQLi
This illustration demonstrates how an attacker can defeat application authentication using SQL injection to obtain administrative rights.
Think of a basic username-and-password database table-based authentication solution. The variables user and pass are obtained via a user's POST request, and they are added to the following SQL statement:
sql = "SELECT id FROM users WHERE username='" + user + "' AND password='" + pass + "'"
The SQL statement in question concatenates data, which is a concern. The following string may be used by the attacker in place of the pssvariable:
password' OR 5=5
The database will then be queried using the following SQL query:
SELECT id FROM users WHERE username='user' AND password='pass' OR 5=5'
The entire WHERE statement will be true no matter the login or password given because the condition 5=5 always evaluates to true.
The first ID from the users table, which is often the administrator, will be returned by the WHERE clause. This indicates that the attacker has administrator rights and can access the application without logging in.
The addition of a code comment symbol at the conclusion of the SQL statement, which enables the attacker to further alter the SQL query, is a more sophisticated variation of this attack. The majority of databases, such as PostgreSQL, Oracle, and MySQL, will support the following:
' OR '5'='5' /*
Example 2: How to Access Sensitive Data with SQLi
In this illustration, the code gets the current username before looking for objects with a certain item name whose owner is the current user.
...
string userName = ctx.getAuthenticatedUserName();
string query = "SELECT * FROM items WHERE owner = "'"
+ userName + "' AND itemname = '"
+ ItemName.Text + "'";
...
The usage of concatenation in this code is the same flaw as in the preceding example. The code generates the following query when the username and item name are combined:
SELECT * FROM items
WHERE owner =
AND itemname = ;
If the attacker provides the following string for it emname:
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;
As a result, the query will return all of the table's data, giving the attacker unauthorized access to confidential information.
Example 3: Malicious Statements Injection into Form Field
This is a straightforward user-input-based SQL injection attack. The attacker utilizes a form that asks for the user's first and last names. The assailant enters:
- First name: malicious'ex
- Last name: Smith
Using the symbol "ex," we identified the malicious expression found in the attacker's first name variable. The SQL statement that handles the form inputs has the following format:
SELECT id, firstname, lastname FROM authors
The statement appears as follows after the attacker inserts a malicious expression into the first name:
SELECT id, firstname, lastname FROM authors WHERE firstname = 'malicious'ex' and lastname ='newman'
The database attempts to execute the malicious statement after recognizing the erroneous syntax caused by the lone apostrophe.
See our thorough overview of SQL injection payloads for numerous further examples of malicious SQL code.
SQL Injection Prevention Cheat Sheet
This is a condensed version of the fantastic OWASP cheat sheet for preventing SQL injection.
Option 1 for Defense: Prepared Statements (with Parameterized Queries)
Prepared statements avoid the issue of SQL injection and are simple to learn and utilize. They make a clear difference between code and data by requiring you to specify SQL code and then pass each argument to the query subsequently.
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. Instead of evaluating this statement as code, the system will search for a user by the name of John' or 1=1 (and fail because no such user exists).
Every programming language has prepared statements. Here is a Java illustration. OWASP advises checking the input parameter just in case, just to be safe.
// 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( );
How to work on sql injection attack
Defense Option 2: Stored Procedures
Preparation statements and stored procedures are similar, with the exception that the SQL code for the stored procedure is specified and saved in the database as opposed to the user's code. You can choose which option works best with your development processes by keeping in mind that stored procedures are frequently just as secure as prepared statements.
In the following two scenarios, stored procedures are not secure:
- Dynamic SQL generation is present in the stored procedure; while this is not commonly done in stored procedures, it is possible, thus you must prevent it when writing stored procedures. If not, be sure to validate every input.
- Database owner rights - in some database configurations, the administrator gives database owner rights to allow the execution of stored procedures. This implies that an attacker who compromises the server has complete access to the database. Create a custom role that only grants storage procedures the degree of access they require to avoid this.
Here is an illustration of a Java stored procedure, also known as a CallableStatement in Java. We presume that the prepared statement in option 1 above uses the same logic as the stored method sp_getAccountBalancer.
// 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
}
Defense Option 3: Allow-list Input Validation
This is yet another effective safeguard against SQL injection. Allow-list validation checks user inputs against a closed list of recognized lawful values.
For instance, you can use code like this to guarantee that a user input can only match one of few, well-known table names if a user input is used to choose a database table:
String tableName;
switch(PARAM):
case "Value1": tableName = "fooTable";
break;
case "Value2": tableName = "barTable" ;
break;
...
default : throw new InputValidationException("unexpected value
Provided" + " for table name");
Converting user inputs into a non-string form is an additional secure method of handling them. The user input can be transformed into a boolean, for instance, if it specifies whether the query should be sorted in ascending or descending order. Then the sort order is decided using this boolean value:
public String someMethod(boolean sortOrder) {
String SQLquery = "some SQL ... order by Salary " + (sortOrder ? "ASC" :
"DESC");`
...
Defense Option 4: Escaping All User-Supplied Input
Adding an escape character tells the code to ignore specific control characters so that it will treat them as text rather than code, which is what escape means.
This choice should only be utilized as a last resort because it is the least secure of the four. This is because attackers come up with countless inventive ways to inject control characters, and escaping user input is only effective if the code escapes all potential control characters. As a result, OWASP advocates using alternatives 1 or 2 above instead of this strategy.
For example, There are two escape modes in MySQL: MySQL mode and ANSI_QUOTES SQL mode.
- To escape control characters in ANSI_QUOTES mode, encode all'(single tick) characters with " (two ticks).
- Escape the following characters in MySQL mode:
- \0 [the number zero, not the letter O]
- \b
- \t
- \n
- \r
- \Z
- \”
- \%
- \’
- \\ [escaping a single slash character]
- \_
- ASCII values should be used in place of all other non-alphanumeric characters.
- Replace any values lower than 256 with c, where 'c' is the original number.
To implement escape in legacy database code, you can use the free, open-source Enterprise Security API (ESAPI), which is offered by OWASP. It offers codecs for well-known databases with all dangerous control pattern escape.
The encoders for SQL Server and PostgreSQL will soon be supported by ESAPI, which currently supports Oracle and MySQL.
Preventing SQL Injection Attack with Bright
Early in the development phase, Bright Dynamic Application Security Testing (DAST) assists in automating the identification and mitigation of various vulnerabilities, including SQLi, across online apps and APIs.
Developers and application security professionals can identify vulnerabilities early and fix them before they emerge in production by shifting DAST scans to the left and integrating them into the SDLC. Bright validates every vulnerability automatically, completing scans in a matter of minutes with 0% false positives. Developers can then adopt the solution and utilize it across the entire development lifecycle in this way.
Check any online application for SQL injection vulnerabilities, as well as REST, SOAP, and GraphQL APIs – try Bright free.
View Our Other Guides on Important Data Security Topics
We have written comprehensive tutorials on a number of other subjects with the help of our content partners, which can be helpful as you learn more about data security.
Data Privacy
written by Imperva
Learn about the laws and governance procedures that can help you comply with data privacy requirements.
- HIPAA Security Requirements: HIPAA Privacy Rule
- Data Governance Definition: Frameworks, Tools, and Best Practices
- Requirements, Controls, and Audits for SOX Compliance
Zero Trust Network Access
written by Cato
Learn about the zero trust network access (ZTNA) solutions that are used to achieve the zero trust security paradigm.
- Zero Trust Guidelines Zero Trust: What Is It?
- Use zero trust access to protect remote workers
- The ZTNA Market Guide by Gartner
DLP
written by Exabeam
Discover how data loss protection (DLP) solutions help guard against the loss, thievery, and leakage of sensitive data.
- Tools for Preventing Data Loss
- Data Loss Prevention Policy Template Recognizing the Features and Best Practices of Cloud DLP