SQL injection protection

SQL injection (SQL injection) is one of the most common ways to hack websites that work with databases. The method is based on injecting arbitrary SQL code into a query.
SQL injection allows a hacker to execute an arbitrary database query (read the contents of any tables, delete, modify or add data).

This type of attack is possible when input data is not sufficiently filtered when used in SQL queries.

How SQL Injection Attack Principle

Let's say our site has a page showing the history of weather observations for one city. The identifier of this city is passed in the link in the request parameter:, /weather.php?city_id=where  IDis the primary key of the city.
In a PHP script, we use this parameter to substitute it in a SQL query:

$city_id = $_GET['city_id'];
$res = mysqli_query($link, "SELECT * FROM weather_log WHERE city_id = " . $city_id);

If the city_id parameter equal to 10 ( /weather.php?city_id=10) is passed on the server , then the SQL query will be executed:

SELECT * FROM weather_log WHERE city_id = 10

But if the attacker passes a string as the id parameter -1 OR 1=1, the request will be executed:

SELECT * FROM weather_log WHERE city_id = -1 OR 1=1

Adding SQL constructs to the input parameters (instead of simple values) changes the logic of the entire SQL query!
In this example, instead of showing data for one city, it will get data for all cities, because the expression 1 = 1 is always true. Instead of an expression, there SELECT ...could be an expression to update the data, and then the consequences would be even more serious.

Failure to properly process SQL query parameters is one of the most serious vulnerabilities. Never insert data from the user into SQL queries "as is"!

Casting to an integer type

Integer values ​​obtained from the user are often substituted into SQL queries. In the examples above, the city ID obtained from the request parameters was used. This identifier can be forced to a number. So we will exclude the appearance of dangerous expressions in it. If a hacker passes in this parameter a SQL code instead of a number, then the result of the cast will be zero, and the logic of the entire SQL query will not change.

PHP is able to assign a new type to a variable. This code will force the variable to be an integer type:

$city_id = $_GET['city_id'];
settype($city_id, 'integer');

After conversion, the variable $city_idcan be safely used in SQL queries.

Escaping values

What if you need to substitute a string value in the SQL query? For example, the site has the ability to search for a city by its name. The search form will pass the search request to the GET parameter, and we use it in the SQL request:

$city_name = $_GET['search'];
$sql = "SELECT * FROM cities WHERE name LIKE('%$city_name%')";

But if the parameter city_namecontains a quotation mark, then the meaning of the request can be drastically changed. Having passed a value to search_text ')+and+(id<>'0, we will execute a query that will display a list of all cities:

SELECT * FROM cities WHERE name LIKE('%') AND (id<>'0%'))

The meaning of the query has changed because a quote from a query parameter is considered a control character: MySQL determines the end of a value by the quote character after it, so the values ​​themselves should not contain quotation marks.
Obviously, casting to a numeric type is not suitable for string values. Therefore, to make the string value safe, use the escaping operation .

Escaping adds a backslash character in the string before quotes (and other special characters) \.
This processing strips the quotes of their status — they no longer define the end of the value and cannot affect the logic of the SQL statement.

The function is responsible for escaping the values mysqli_real_escape_string().
This code will process the value from the parameter, making it safe to use in the request:

$city_name = mysqli_real_escape_string($link, $_GET['search']);
$sql = "SELECT * FROM cities WHERE name LIKE('%$city_name%')";

Prepared expressions

SQL injection attacks are possible because the values ​​(data) for the SQL query are passed along with the query itself. Since the data is not separated from the SQL code, it can affect the logic of the entire expression. Fortunately, MySQL provides a way to pass data separate from your code. This method is called prepared statements .

Execution of prepared queries consists of two stages: first, a query template is formed - an ordinary SQL expression, but without real values, and then, separately, the values ​​for this template are transferred to MySQL.
The first step is called preparation and the second is called expression. A prepared query can be executed multiple times, passing different values ​​to it.

Stage of preparation
At the stage of preparation, an SQL query is formed, where question marks - placeholders - will be located in place of the values. These placeholders will be replaced with real values ​​in the future. The query template is sent to the MySQL server for analysis and syntax checking.
Example:

$sql = "SELECT * FROM cities WHERE name = ?";
$stmt = mysqli_prepare($link, $sql);

This code will generate a prepared statement to fulfill your request.

The preparation is followed by execution. When the request is run, PHP binds real values ​​to the placeholders and sends them to the server. The function is responsible for passing values ​​to the prepared request mysqli_stmt_bind_param(). It takes a type and the variables themselves:

mysqli_stmt_bind_param($stmt, 's', $_GET['search']);

After executing the query, you can get its result in the mysqli_result format using the function mysqli_stmt_get_result():

$res = mysqli_stmt_get_result($stmt);

// чтение данных
while ($row = mysqli_fetch_assoc($res)) {
    // ассоциативный массив с очередной записью из результата
    var_dump($row);
}

The server automatically escapes the values ​​of the variables bound to the request. Bound variables are sent to the server separately from the request and cannot affect it. The server uses these values ​​immediately at the time of execution, after the expression template has been processed. Bound parameters do not need escaping because they are never substituted directly in the query string.