SQL Injection (SQLI) is when untrusted data is used to construct an SQL query. The data is inserted (or "injected") into the SQL query string.
SQLI has been ranked #1 on Top 10 security threats by OWASP.
It is a top threat for a few reasons. It is easy for attackers to detect and exploit. It is a powerful attack because it give access to application databases, and most modern web applications rely heavily on databases. It is successful because it circumvents the normal access controls to database data. Web applications usually have full or near full access and SQLI attacks are able to use access to their advantage.
SQLI attacks can have many different goals.
Probe application or database structure
Authentication
Steal data
Alter data
Destroy data
Imagine a login form. When the form is submitted, the application code constructs an SQL query to search for a matching username and password in the users table. (Assume the code is encrypting the password. It should never be stored in plain text!)
<?php
$username = "marymartin";
$password = "love74bug";
$sql = "SELECT * FROM users ";
$sql .= "WHERE username='{$username}' ";
$sql .= "AND password='{$encrypted_pwd}'";
// SELECT * FROM users WHERE username='marymartin' AND password='(encrypted)'
?>A hacker visits the login form and, instead of providing a username, submits an carefully-crafted string in its place.
<?php
$username = "sqli' OR 1=1; --";
$password = "";
// SELECT * FROM users WHERE username='sqli' OR 1=1; --' AND password=''
?>Read the resulting SQL carefully. Notice that the string closes the single-quote in order to "break out" of the intended input value. OR 1=1 will always return true and -- indicates the start of a comment in SQL. The result is that the query will match all users and the password clause will be ignored. Potentially, this SQLI attack could bypass the login page and grant access.
Attacks which can communicate with the SQL database are not limited to simple commands like in the previous example. Here are a few other examples.
<?php
// The application code
$sql = "SELECT * FROM products WHERE name='{$name}'";
// Possible values for $name:
// Uses UNION to join the results to a new query
$name = "Robert' UNION SELECT username, password FROM users; --";
// Piggybacks an INSERT statement to add an admin record
$name = "Robert'; INSERT INTO admins (username, password)
VALUES ('hacker', 'secret'); --";
// Piggybacks a DROP TABLE statement to destroy data
$name = "Robert'; DROP TABLE students; --";
?>Any query which utilizes user input is vulnerable to SQLI.
The most frequent area of attack are WHERE clauses. They receive the most dynamic data as the application searches for results matching a URL or a user requests searches using various parameters.
Other query types are equally vulnerable, just not as frequently used. INSERT, UPDATE, DELETE statement must be considered, as well as other SQL clauses like SELECT and ORDER BY.
If dynamic data contributes to the SQL in anyway, then it must be considered vulnerable.
Often is possible to see the results of SQL Injection immediately. This allows an attacker to know right away when they have found an exploit. This is useful if the goal is to steal data.
However, it is possible for an SQLI exploit to exist which does not return visible proof. Imagine that there is a page which will accept an SQL Injection and send it to the database, but which will display the same standard error page to a user regardless of whether the SQL Injection succeeded or not. Because an attacker cannot see the results this is referred to as a "Blind SQL Injection".
One common technique is to inject an SQL query which will cause the database to pause or return a slow response if the injection works.
The easiest version is to use SLEEP() to create a slow response. If it works, the server will pause for 5 seconds. If it does not work, it won't.
$id = ' AND sleep(5);--';Here is a more elaborate example which, when injected, will use IF() to test if the first character the username for user_id=1 is CHAR(97) (the letter 'a'). If true, then the BENCHMARK() function executes a slow process 5 million times. If false, it finishes the query quickly. An attacker could go through the entire alphabet in a loop to determine the full username.
$id = '1 UNION SELECT IF(
SUBSTRING(username,1,1) = CHAR(97),
BENCHMARK(5000000, MD5('MD5 is relatively slow'),
null
) FROM users WHERE user_id = 1;--';This technique can be used to examine the structure of the database (table names, column names) as well as the values in the table rows.
Sanitizing input is the best way to prevent SQL Injection. However, there is something easier which can serve as a first line of defense. The application never needs full database privileges. This follows the principle of Least Privilege. The application should have the least amount of privilege necessary to do its job. Frequently, developers are lazy and grant broad privileges without taking time to consider which ones are actually necessary.
Of course, the application needs to be able to read and write to most tables. But some tables might be privileged and could be restricted. The application's database user could be forbidden from creating, truncating, or dropping any of the tables. SQL has a special permission (the GRANT OPTION) which allows granting permissions to other database users. It is a powerful privilege and unlikely that the application needs it.
Most importantly, never let the application connect as root user. The root user is the most privileged user in SQL. It is easy to create a new user exclusively for the application's use--one command to create, one command to grant access. There is no reason to give attackers such a gift.
The best defense against SQLI is sanitizing the incoming data. This means escaping characters in the data which have special meaning to SQL.
In simple terms, this means putting a backslash (\) before any single quotes. However, it would be naive to simply add slashes before single quotes because there are many tricks to circumvent detection. Instead, use a well-tested sanitization library which will account for all possibilities.
In PHP, the function mysqli_real_escape_string() is a helpful tool for escaping strings for use with MySQL databases.
In addition to sanitizing, whitelisting and validating data expectations can be useful tools in preventing SQLI. For example, if a text input field is expecting a postal zip code, then it is reasonable to restrict the value to letters, numbers, spaces, and dashes, and to limit its length.
Prepared statements are an effective prevention measure against SQLI attacks. An SQL statement is prepared with placeholders for any dynamic data. Data must match a data type specified (string, integer, etc.) and must act exclusively as data. It cannot add any code to the SQL query.
It is important to recognize that there are other injection types besides SQL Injection. SQLI is most common because of its popularity and easy access from web pages. But any data passed to an "interpreter" can be injected with additional content.
Some other examples include:
