SQL injection, also known as SQLi, SQLI or SQL*, is a code injection technique that allows an attacker to execute SQL statements (or queries) of their own on SQL databases.
These attacks often result in the criminal retrieving data from a database, modifying data in a database or gaining access to the underlying server running a database.
What Are the 3 Main Types of SQL Injection Attacks?
- In-band
- Inferential
- Out-of-band
Types of SQL Injection
In-Band SQL Injections
In-band SQL injection attacks occur when an attacker is able to use the same call or communication mechanism to both perform the attack and gain the results.
This usually means the SQL attack is passed as an API call and the results of the injected query are returned in the response. It may also occur, however, by triggering an error and having the results return as part of the error.
Inferential SQL Injections
Inferential SQL injections are when an attacker is able to perform the injection but unable to see the results of the query directly.
Typically, this type of attack requires a lot more work and effort on the attacker’s part to get meaningful data. Attackers usually make use of boolean conditions or time-based delays in order to have an interpretable effect.
With boolean conditions, the attacker can guarantee the presence or lack of results from the response to determine if the injection performed or found anything meaningful. Contrarily, with time-based delays, the attacker can get the same answer based on how long it takes for a response to be returned.
Out-of-Band Injections
Out-of-band SQL injection attacks are when the attacker is either unable to use the same call or communication mechanism to both perform the attack and gain the results, or uses a feature or mechanism of the database to send the data over another mechanism (saving results to a file, the database sending the results over a different network call, etc.).
SQL Injection Examples
Imagine a website’s user login page that asks for a username and password. Once the user has submitted the information, the web server takes this information and checks if the details provided match a legitimate entry in its SQL database.
The web server does this by taking the information provided by the user and placing it into a SQL query.
SELECT * FROM users WHERE username = ‘<USERNAME>’ AND password = ‘<PASSWORD>’;If the query returns a value, then the user is considered legitimate and they’re allowed to log in. This web server is invulnerable to SQL injection.
Now, imagine the attacker gave these values.
username: admin’ OR 1=1--
password: --
This would result in the SQL query looking like this.
SELECT * FROM users WHERE username = ‘admin’ OR 1=1--' AND password = ‘--’;
In this case, a result would be returned as long as there’s a row in the database with the username of admin, or if one is equal to one, which is always true, so the attacker would be able to log in.
The rest of the query would not be executed, because the attacker placed a comment to prevent the rest of the query from being used. In addition, if the user changed the “OR” in their attack to “AND,” and there is an admin user in the database, the attacker would be able to log in as the admin user.
Finally, consider what would happen if an attacker were to provide these values.
username: admin’ OR 1=1--; DROP TABLE users; --
password: --
This would result in an SQL query that looks like this.
SELECT * FROM user WHERE username = ‘admin’ OR 1=1--; DROP TABLE users; -- AND password = ‘--’;
The database would then drop the users table, likely rendering the web server unusable.
How Do SQL Injection Attacks Work?
SQL injection attacks involve an attacker directly inserting malicious SQL code into user-input variables, such as username or password fields, on websites or variables in API calls, which are then executed against the SQL database.
Criminals are usually able to do this when the values of the user-input fields are placed directly into an SQL query and run. This means the attacker is able to execute any SQL code they wish by adding it into the user-input field and, as long as it results in a valid SQL query, the database will treat it as valid and execute it.
How to Prevent SQL Injection Attacks
User Prepared Statements
You can prevent most SQL injection attacks by using prepared statements (aka parameterized queries) instead of directly placing user input into the query.
By using this coding style, the database distinguishes between what is intended to be SQL code and what is intended to be data regardless of whatever the user has supplied. This helps ensure that even if an attacker were to provide a value that contains valid SQL code, the database would treat it simply as user data and not allow it to modify or affect the query in any way.
Escape User Supplied Input
A less effective way of preventing SQL injection attacks is to escape user supplied input, particularly input that has a special meaning in SQL such as: *, ‘, --, ; etc.
Escaping means using a special character, typically \, to inform the database that the character should be treated as part of the data and not a modification of the query.
While this technique can prevent some SQL injection, it won’t prevent all SQL injection attacks. There are potential workarounds and bypasses, and it’s extremely database specific.
Common Security Best Practices
Do Not Trust User-Supplied Data
If your service receives data or input from sources outside of your control, such as user-supplied information, be sure to not place inherent trust in the authenticity of the data. Be careful not to immediately process or use the data without sanitizing or formatting it, or in some way confirming it matches the expected format and contains nothing disallowed.
Do Not Return Errors As-Is
If your service encounters an error during an operation, ensure that it doesn’t return the error message as-is back to the user. Otherwise, the error message may leak information an attacker could find useful, such as the type of database used, query used, data returned in a query, etc.
When an error does occur, send the user a generic error message that doesn’t provide the specifics of the error.
