If you are developing a web-application using PHP, then you will be running SQL query using PHP code, which is vulnerable as an attacker can inject malicious script/code using SQL injection by tricking a web application in processing an attacker’s input as part of an SQL statement, this technique is known as SQL injection and we should take proper steps to increase the security of our php web-application.
By leveraging an SQL Injection vulnerability, given the right circumstances, an attacker can use it to bypass a web application’s authentication and authorization mechanisms and retrieve the contents of an entire database. SQL Injection can also be used to add, modify and delete records in a database, affecting data integrity.
How SQL Injection works
As the name suggests, an SQL injection vulnerability allows an attacker to inject malicious input into an SQL statement. To fully understand the issue, we first have to understand how server-side scripting languages handle SQL queries.
For example, let's say functionality in the web application generates a string with the following SQL statement:
$statement = "SELECT * FROM users WHERE username = 'jaya' AND password = 'mysecretpwtext'";This SQL statement is passed to a function that sends the string to the connected database where it is parsed, executed and returns a result.
Now consider the following example in which a website user is able to change the values of '$user' and '$password', from the login form submitted by the user:
$statement = "SELECT * FROM users WHERE username = '$user' AND password = '$password'";now, an attacker can change the above statement by adding it to a malicious script like below
$statement = "SELECT * FROM users WHERE username = 'admin'; -- ' AND password = 'anything'";
= 'anything'";Now what attacker is doing here, let's understand that part
- ; (semicolon) is used to instruct the SQL parser that the current statement has ended (which is not necessary in most cases & attacker doesn't intend to end statement here)
- -- (double hyphen) instructs the SQL parser that the rest of the line is a comment and should not be executed
Above SQL injection effectively removes the password verification and returns a dataset for an existing user – 'admin'. The attacker can now log in with an administrator account, without having to specify a password.
Types of SQL injection
SQL Injection can be classified into three major categories – In-band SQLi, Inferential SQLi and Out-of-band SQLi.
In-band SQLi (Classic SQLi)
In-band SQL Injection is the most common and easy-to-exploit of SQL Injection attacks. In-band SQL Injection occurs when an attacker is able to use the same communication channel to both launch the attack and gather results.
The two most common types of in-band SQL Injection are Error-based SQLi and Union-based SQLi.
Error-based SQLi
Error-based SQLi is an in-band SQL Injection technique that relies on error messages thrown by the database server to obtain information about the structure of the database. In some cases, error-based SQL injection alone is enough for an attacker to enumerate an entire database. While errors are very useful during the development phase of a web application, they should be disabled on a live site, or logged to a file with restricted access instead.
Union-based SQLi
Union-based SQLi is an in-band SQL injection technique that leverages the UNION SQL operator to combine the results of two or more SELECT statements into a single result which is then returned as part of the HTTP response.
Inferential SQLi (Blind SQLi)
Inferential SQL Injection, unlike in-band SQLi, may take longer for an attacker to exploit, however, it is just as dangerous as any other form of SQL Injection. In an inferential SQLi attack, no data is actually transferred via the web application and the attacker would not be able to see the result of an attack in-band (which is why such attacks are commonly referred to as “blind SQL Injection attacks”). Instead, an attacker is able to reconstruct the database structure by sending payloads, observing the web application’s response and the resulting behavior of the database server.
The two types of inferential SQL Injection are Blind-boolean-based SQLi and Blind-time-based SQLi.
Boolean-based (content-based) Blind SQLi
Boolean-based SQL Injection is an inferential SQL Injection technique that relies on sending an SQL query to the database which forces the application to return a different result depending on whether the query returns a TRUE or FALSE result.
Depending on the result, the content within the HTTP response will change, or remain the same. This allows an attacker to infer if the payload used returned true or false, even though no data from the database is returned. This attack is typically slow (especially on large databases) since an attacker would need to enumerate a database, character by character.
Time-based Blind SQLi
Time-based SQL Injection is an inferential SQL Injection technique that relies on sending an SQL query to the database which forces the database to wait for a specified amount of time (in seconds) before responding. The response time will indicate to the attacker whether the result of the query is TRUE or FALSE.
Depending on the result, an HTTP response will be returned with a delay or returned immediately. This allows an attacker to infer if the payload used returned true or false, even though no data from the database is returned. This attack is typically slow (especially on large databases) since an attacker would need to enumerate a database character by character.
Out-of-band SQLi
Out-of-band SQL Injection is not very common, mostly because it depends on features being enabled on the database server being used by the web application. Out-of-band SQL Injection occurs when an attacker is unable to use the same channel to launch the attack and gather results.
Out-of-band techniques offer an attacker an alternative to inferential time-based techniques, especially if the server responses are not very stable (making an inferential time-based attack unreliable).
Out-of-band SQLi techniques would rely on the database server’s ability to make DNS or HTTP requests to deliver data to an attacker. Such is the case with Microsoft SQL Server’s xp_dirtree command, which can be used to make DNS requests to a server an attacker controls; as well as Oracle Database’s UTL_HTTP package, which can be used to send HTTP requests from SQL and PL/SQL to a server an attacker controls.
Methods to prevent SQL injection
1.Using mySQLi to create Prepared Statements
The easiest way to prevent SQL Injection Attacks in PHP is to use ‘Prepared Statements’. So, here’s how we can use the prepared statements for making the database query.
Suppose you want to create login PHP script by adding prepared statements
<?php
$username = $_POST["username"];
$password = $_POST["password"];
$stmt = $mysqli->prepare("SELECT * FROM users WHERE user=? AND pass=?");
$stmt->mysqli_bind_param("ss",$username,$password);
$stmt->execute();
$stmt->close();
$mysqli->close();
?>What happens is that the SQL statement you pass to prepare is parsed and compiled by the database server. By specifying parameters (either a ? or a named parameter like :username (in case of PDO) you tell the database engine where you want to filter on. Then when you call execute, the prepared statement is combined with the parameter values you specify.
The important thing here is that the parameter values are combined with the compiled statement, not an SQL string. SQL injection works by tricking the script into including malicious strings when it creates SQL to send to the database. So by sending the actual SQL separately from the parameters, you limit the risk of ending up with something you didn’t intend. Any parameters you send when using a prepared statement will just be treated as strings (although the database engine may do some optimization so parameters may end up as numbers too, of course).
If you are using WordPress, you can also use the wpdb::prepare statement which just like prepared statements, will sanitize the query which is sent to the database.
2. Using PDO (PHP Data Objects)
PDO or PHP Data Objects are very useful – probably the most effective in preventing SQL Injection Attacks. PDO also uses prepared statements and binds values at runtime.
Example of PDO statement
$stmt = $pdo->prepare('SELECT * FROM employees WHERE name = :name');
$stmt->execute(array('name' => $name));
foreach ($stmt as $row) {
// do something with $row
}By using PDO rather than mysql_, mysqli_, and
pgsql_ functions, you make your app a little more abstracted from the database, in the rare occurrence that you have to switch database providers.
Note: When using PDO to access a MySQL database real prepared statements are not used by default. To fix this you have to disable the emulation of prepared statements. An example of creating a connection using PDO is:
$dbConnection = new PDO('mysql:dbname=dbtest;host=127.0.0.1;charset=utf8', 'user', 'pass');
$dbConnection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);The first setAttribute() line is mandatory which tells PDO to disable emulated prepared statements and use real prepared statements. This makes sure the statement and the values aren't parsed by PHP before sending it to the MySQL server (giving a possible attacker no chance to inject malicious SQL).
3. Escaping strings
Escaping string helps in removing special characters for use in SQL statements. It also takes into account current charset of the connection.
For example:
<?php
$username = mysqli_real_escape_string($conn,$_POST["username"]);
$password = mysqli_real_escape_string($conn,$_POST["password"]);
mysqli_close($conn);
?>
Buy us a coffee
Become a Patron