How to avoid MYSQL SQL Injection in PHP?

  Tags: , , ,

Question:

Dynamic statements are SQL statements that are created as text strings (strings) and in which values ​​derived from a source (usually from the user) are inserted / concatenated, which can make them vulnerable to SQL injection if they are not sanitized the entries, such as:

$id_usuario = $_POST["id"];

mysql_query("SELECT * FROM usuarios WHERE id = $id_usuario");

This is an example of a serious vulnerability in the security of an application (web or not) because if the user entered a value as 1; DROP TABLE usuarios;--we would find that the sentence executed would be:

SELECT * FROM usuarios WHERE id = 1; DROP TABLE usuarios;--

And the Users table with all the data contained in it would be deleted.

Solution for preventing SQL injection in PHP?

DO NOT USE DYNAMIC SENTENCES OR FUNCTIONS mysql_*

Functions mysql_*mysql_connectmysql_query, etc.) are insecure by nature and its use not only is not recommended, but are considered obsolete and have been completely removed from PHP7 .

Even the native methods that exist in PHP to clean user entries (like mysql_real_escape_string) can present (rare) problems and fail in some cases, such as when encoding characters other than UTF-8 are used together with non-updated versions of MySQL (in the PHP pages for these functions warns of this risk).


Use prepared statements and parameterized queries

Although entries could be sanitized using methods such as mysqli_real_escape_string, it is more advisable to use prepared or parameterized sentences. The prepared sentences will allow you to execute the same sentence with great efficiency.

In PHP, you have two main alternatives: PDO and MySQLi. There are several differences between the two, but the main one is that PDO can be used with different types of database (depending on the driver used) while MySQLi is exclusively for MySQL databases. That is why I would recommend PDO over MySQLi.

PDO

Placeholders (which indicate where a string will be replaced by its value), can be defined either by using a question mark ( ?) or by using a name (usually starting with :). Personally I prefer to use a name, because that helps me find possible errors in case of having multiple variables.

Here I leave an example for the code of the question:

// la variable $pdo contendrá el objeto con la conexión PDO
$pdo = new PDO('mysql:host=mihost;dbname=basedatos', "usuario", "contraseña");

$id_usuario = $_POST["id"];

$sentencia = $pdo->prepare("SELECT * FROM usuarios WHERE id = :idusuario");
$sentencia=$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$sentencia->bindParam(":idusuario", $id_usuario, PDO::PARAM_INT);
$sentencia->execute();

In this case, it will :idusuariobe replaced by the value in $_POST["id"]a secure way, and when it does the bind it is indicated that the variable is of type integer ( PDO::PARAM_INT).

Note: if the variable is a text string it will be used PDO::PARAM_STRand there is no need to put the quotes in the SQL statement; by specifying PHP as a string, it will automatically add them when doing the bind .

In case there are several variables to be included in the SQL statement, a single parameter must be included for each of the values ​​used in the statement. From the previous example, it :idusuariocan be used only once in the query that is being prepared. If it is necessary to use the “userid” again in the query, another parameter must be created with the value of $id_usuario.

$pdo = new PDO('mysql:host=mihost;dbname=basedatos', "usuario", "contraseña");

$id_usuario = $_POST["id"];

$sentencia = $pdo->prepare("UPDATE usuarios SET id = :idusuario WHERE id = :idusuario1");
$sentencia=$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$sentencia->bindParam(":idusuario", $id_usuario, PDO::PARAM_INT);
$sentencia->bindParam(":idusuario1", $id_usuario, PDO::PARAM_INT);
$sentencia->execute();

MySQLi

This method has two interfaces: a procedural and an object-oriented one. The procedural interface is very similar to mysql_*, and therefore people who migrate from mysql_*may be attracted by the ease it mysqli_*offers. Although, again personally, I would opt for the OOP version.

Note: although the functions mysqli_*are usually similar to those mysql_*, in some cases they may have different input parameters or different outputs, which may lead to some confusion at the beginning.

The example of the question would be like this with MySQLi in its object-oriented interface:

// en $mysqli tendremos la conexión MySQLi
$mysqli = new mysqli("mihost", "usuario", "contraseña", "basedatos");

$id_usuario = $_POST["id"];

$sentencia = $mysqli->prepare("SELECT * FROM usuarios WHERE id = ?");
$sentencia->bind_param("i", $id_usuario );
$sentencia->execute();

As you can see, it is quite similar to PDO (it changes a bit how the type of value is specified, ifor integers and sfor strings, but the idea is similar).

In the procedural version of MySQLi, the equivalent code would be:

// en $conn tendríamos la conexión a la base de datos con MySQLi
$conn = mysqli_connect("mihost", "usuario", "contraseña", "basedatos");

$id_usuario = $_POST["id"];

$sentencia = mysqli_prepare("SELECT * FROM usuarios WHERE id = ?");
mysqli_stmt_bind_param($sentencia, "i", $id_usuario);
mysqli_stmt_execute($sentencia);

Source and bibliography for more information in Spanish:

 

LEAVE A COMMENT