DO NOT USE DYNAMIC STATEMENTS OR FUNCTIONSmysql_*

Functions mysql_*( mysql_connect, mysql_query, etc.) are insecure by nature and their use is not only not recommended, but they are considered deprecated and have been completely removed as of PHP7 .

Even the native methods that exist in PHP to sanitize user input (such as mysql_real_escape_string) can present (rare) problems and fail in some cases such as when non-UTF-8 character encodings are used together with out-of-date versions of MySQL (in which PHP pages for these functions warn of this risk).

Use prepared statements and parameterized queries

Although the inputs could be sanitized using methods such as mysqli_real_escape_string, it is more advisable to use prepared or parameterized statements. Prepared statements will allow you to execute the same statement 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 databases ( depending on the driver used ) while MySQLi is exclusively for MySQL databases. This is why I would recommend PDO over MySQLi.

PDO

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

Here is an example for the question code:

// 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, :idusuarioit will be substituted for the value of $_POST["id"]safely, 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 it is not necessary to put the quotes in the SQL statement; by specifying to PHP that it is a string, it will automatically add them when doing the bind .

In case there are several variables to include in the SQL statement, a single parameter must be included for each of the values ​​used in the statement. From the example above, the :idusuariocan be used only once in the query being prepared. If it is necessary to use "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();

MySQL i

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

Note: Although functions mysqli_*are often similar to mysql_*, in some cases they can have different input parameters or different outputs, which can lead to some confusion at first.

The example in the question would look 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's quite similar to PDO (it changes a bit how the value type 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:

• Official PHP page on prepared statements
• Official PHP page on prepared statements in PDO
• Wikipedia article on SQL injection and how to avoid it in PHP and other languages

Attention: The accepted answer is certainly good, but it is insufficient . It is true that the use of prepared queries almost prevents us from attacks known as first-degree SQL injection. But if PDO is misconfigured the prepared queries could be vulnerable, as shown with examples in the answer. At the end I have put the links that have served as the basis for the elaboration of this answer, especially Preventing SQL Injection in PHP Applications - the Easy and Definitive Guide and An SQL injection against which prepared statements won't help .

My only merit has been the translation, perhaps improvable in some points. Nothing is 100% secure, we know, but the simple use of PDO or MySQLi must be combined with other strategies, explained in point 5 , to ensure something as valuable as our data.

1. Introductory note

SQL injection is a technique for taking control of a database query that often results in a confidentiality compromise. In some cases it can result in a complete server takeover.
For example:

SELECT 'código diabólico aquí' INTO OUTFILE '/var/www/reverse_shell.php'

Since code injection (encompassing SQL, LDAP , Command OS, and XPath Injection techniques) has consistently remained at the top of OWASP 's Top 10 vulnerabilities , it's a popular topic for bloggers trying to get wet in the wild. application security field.

Unfortunately, much of the advice circulating on the Internet (especially on old blogs that rank high in search engines) is outdated, unintentionally misleading, and often dangerous .

2. A very common error: misconfigured PDO

If you are a PHP developer looking to get the most out of PDO, we recommend that you change two of the defaults:

1. Disable emulated preparations . This ensures that you get prepared statements.
2. Set the error mode to throw exceptions . This saves you from having to watch the results of PDOStatement::execute()and makes your code less redundant.

To do both:

$pdo = new PDO(/* Fill in the blank */);
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

Since it PDO::ATTR_EMULATE_PREPARESis defined as false, we are getting actual prepared statements. And because we have defined PDO::ATTR_ERRMOREas PDO::ERRMODE_EXCEPTION.

Instead of this:

$stmt = $pdo->prepare("SELECT * FROM foo WHERE first_name = ? AND last_name = ?");
if ($stmt->execute([$_GET['first_name'], $_GET['last_name'])) {
    $users = $stmt->fetchAll(PDO::FETCH_ASSOC);
} else {
    // Handle error here.
}
$args = [
    json_encode($_GET)
    (new DateTime())->format('Y-m-d H:i:s')
];
$insert = $pdo->prepare("INSERT INTO foo_log (params, time) VALUES (?, ?);");
if (!$insert->execute($args)) {
    // Handle error here.
}

You can just write your code like this:

try {
    $stmt = $pdo->prepare("SELECT * FROM foo WHERE first_name = ? AND last_name = ?");
    $stmt->execute([$_GET['first_name'], $_GET['last_name']);
    $users = $stmt->fetchAll(PDO::FETCH_ASSOC);
    $args = [
        json_encode($_GET),
        (new DateTime())->format('Y-m-d H:i:s')
    ];
    $pdo->prepare("INSERT INTO foo_log (params, time) VALUES (?, ?);")
        ->execute($args);
} catch (PDOException $ex) {
    // Handle error here.
}

Greater security, brevity and better legibility. What more could you want?

3. Another mistake: not fully understanding what PDO is for

A very common mistake is not fully understanding what PDO exists for and ending up using it as a simple update helper.

While playing around with PDO , almost all PHP users end up thinking of it as some kind of enhancement to the INSERT/ query UPDATE, which accepts an associative array and creates a dynamically bookmarked query, which basically looks something like this ( actual code taken from a question on Stack Overflow ):

 $params = []; 
 $setStr = "" ; 
 foreach ( $data as $key => $value ) 
 { 
    if( $key != "id" ) 
    { 
         $setStr .= $key . " = :" . $key . ","; 
    } 
     $params[':' . $key] = $value ; 
 
 } 
 $setStr = rtrim($setStr, ",");
 $pdo->prepare("UPDATE users SET $setStr  WHERE id = :id" )->execute($params);

This looks pretty cool, as you can quickly produce a query inside a matrix or array where the keys represent column names and the values ​​represent the values ​​to be used in the query. Since the field names in HTML format are the same as the table column names, you can greatly automate form processing, allowing you to use the same code to edit information in any table. Very convenient. But catastrophically vulnerable .

"How?" - you would probably say - "placeholders are used and data is securely bound, therefore our query is safe" . Yes, the data is secure. But in fact what this code does is take the user input and add it directly to the query . Yes, it is a variable $keythat goes right into your query without any treatment.

Using PDO naively and misconfigured is an open door to SQL injection.

4. Possible injection examples with misconfigured PDO and debunking some myths about database protection

Point 4 only applies if we have a misconfigured PDO. Although it is a bit extensive, I wanted to translate it so that we can appreciate the scope of such an easy mistake to make. It also dismantles some myths such as the sanitation of our chains, erroneously recommended on many internet sites.

Here's a little proof of concept code that, as long as you have a table usuarios and a row with id = 1, will change the username to a query result SELECT. And this query could be anything:

 <form method = POST> 
 <input type = hidden name = "name=(SELECT'hacked!')WHERE`id`=1#" value = ""> 
 <input type = hidden name = "name" value = "Joe"> 
 <input type = hidden name = "id" value = "1"> 
 <input type = submit> 
 </form> 
 <? php 
 if( $_POST ) {
     $pdo = new PDO('mysql:dbname=test;host=localhost', 'root', '');
     $params = [];
     $setStr = "" ;
    foreach($_POST as $key => $value)
    { 
        if( $key != "id" )
        {
             $setStr .= $key . " = :" . $key . ","; 
        } 
        $params[$key] = $value;
    }

     $setStr = rtrim($setStr, ",");
     $pdo->prepare("UPDATE users SET $setStr WHERE id = :id")->execute($params);
 }  

This code will produce a query like this

UPDATE users 
  SET name =
  ( SELECT 'hacked!' ) 
  WHERE ` id `= 1 # = :name=(SELECT'1')
  WHERE`id`=1#,name = :name WHERE id = :id  

Where everything after #will be treated as a comment.

You can try it at home.

What happens in this code?
We're building a form, adding another field to it, and writing SQL to the attribute name. The mentioned helper function will take this forged SQL and insert it into the constructed query. As a result, instead of Joethe name will be set to hackeado!. Not too scary huh? But you have to understand that what is dangerous here is the fact of the injection. While possible, the number of malicious queries to create is infinite . And not all of them are so harmless. Next we will look at a more dangerous vulnerability.

PDO::quote() - The wrong move

Ok, we need to protect our useful code (as well as any other code that can't be protected with prepared statements).

The first thing an average PHP user would probably think of is a built-in PDO::quote() function , which apparently does what we need: protect data from SQL injection. But it will soon be clear that a function intended for formatting cadenais inapplicable for identifiers. This will add single quotes around the return value and cause our code to produce a sequence like this:

  'name' = 'joe'  

Which will result in a syntax error, since single quotes are illegal as field name delimiters in MySQL.

Never think of trimming single quotes from the resulting string , as it was suggested some time ago in a comment under the PDO::quote() entry in the PHP manual: the purpose of this function is to do a full escape format of both characters and adding quotation marks. Take out one of these two actions and you will have an injection.

In other words, if we strip the surrounding single quotes from the result of PDO::quote(), it will be like just applying the regular sting by escaping . And let's see why it's wrong:

Escape string (mysql_real_escape_string) - a mess

Another thing a PHP user might think of is a familiar escape function that "makes your data safe" as falsely stated for a long time in the PHP manual . Unfortunately, it will only help if the injection code contains single quotes or other characters with this feature. The bad news is that all those characters are unnecessary for the injection, proving this function useless for the case (and refuting the fictitious protection abilities of this function):

 <form method = POST> 
 <input type = hidden name = "name=(SELECT password from admins)WHERE`id`=1#" value = ""> 
 <input type = hidden name = "name" value = "Joe"> 
 <input type = hidden name = "id" value = "1"> 
 <input type = submit> 
 </form> 
 <? php 
 if ( $_POST ) { 
     $pdo = new PDO('mysql:dbname=test;host=localhost', 'root', ''); 
     $params = []; 
     $setStr = ""; 
     foreach( $_POST as $key => $value ) 
     { 
        if( $key != "id" ) 
        { 
            $setStr .= addslashes($key). " = :" . $key . ","; 
        } 
        $params[$key] = $value; 
    } 
    $setStr = rtrim($setStr, ","); 
    $pdo->prepare("UPDATE users SET $setStr WHERE id = :id")->execute($params); 
    var_dump("UPDATE users SET $setStr WHERE id = :id", $_POST); 
 }  
 ?>

As you can see, there is not a single quote in the malicious query and therefore neither addslashes()nor several *_escape_string()can do anything here.

This injection is much more dangerous as it will reveal the administrator password to anyone.

Adición de Operadores de ejecución o comillas invertidas `` (aún vulnerable)

Ok, como ya hemos aprendido, las comillas simples no ayudarán. ¿Quizá los operadores de ejecución o comillas invertidas (``) ayudarían? Veamos si ayudan:

  <form method = POST> 
 <input type = hidden name = "name`=(SELECT'hacked!')WHERE`id`=1#" value = ""> 
 <input type = hidden name = "name" value = "Joe"> 
 <input type = hidden name = "id" value = "1"> 
 <input type = submit> 
 </form> 

 <? php 
 if ( $_POST ) { 
     $pdo = new PDO('mysql:dbname=test;host=localhost', 'root', ''); 
     $params = []; 
     $setStr = ""; 
     foreach( $_POST as $key => $value ) 
     { 
         if( $key != "id" ) 
         { 
             $setStr .= "` $key ` = : $key ,"; 
         } 
         $params[$key] = $value;
     } 
     $setStr = rtrim($setStr, ","); 
     $pdo->prepare("UPDATE users SET $setStr WHERE id = :id")->execute($params); 
 }
 ?>  

Como se puede ver, añadir comillas invertidas no nos ayudó en absoluto: un atacante sólo añade otra comilla para cerrar prematuramente el identificador y luego proceder con la consulta malintencionada. Sucede porque agregar cualquier delimitador alrededor de algún literal es inútil si no escapamos de estos delimitadores dentro - es la lección que aprendimos duramente de las inyecciones convencionales basadas en cadenas. ¡Así que escaparemos nuestras comillas invertidas!

Escapando las comillas invertidas

Entonces, ¿qué se puede hacer para conservar una función tan útil, pero sin una violación terrible?

Como se dijo antes, escapar delimitadores ayudaría. Por lo tanto, su primer nivel de defensa debe ser escapar delimitadores (comillas invertidas) duplicándolos.

Construyendo nuestra consulta de esta manera:

 $setStr .= "`" . str_replace("`", "``", $key). "` = :" . $key . ",";  

Usted conseguirá eliminar la inyección.

Si tratamos de inyectar utilizando el método anterior resultará en un error de ejecución de la consulta, que, aunque es seguramente mejor que una inyección exitosa, todavía no es un comportamiento bastante deseable.

Esta es la razón por la que citar/escapar nombres de campo aún no es suficiente. Y esta es la razón por la que usted debe utilizar otro nivel de defensa:

Solución sólida en todo

Hay una cosa más a tener en cuenta: aunque citar/escapar nombres de campo eliminará la inyección de SQL clásica, hay otro vector de ataque posible.
Como no controlamos qué nombres de campo se utilizan para actualizar, es posible que el usuario modifique el valor al que no debería tener acceso. Por ejemplo, imagine que hay un campo en una tabla de usuarios como admin que se establece en 1 para admins y 0 para usuarios regulares. La función en cuestión permitirá a cualquier manipulador de código aumentar el nivel de privilegio de su cuenta.

Suponiendo todo lo anterior, una función tan útil debe estar siempre aceptando un parámetro adicional con una lista de nombres de campos permitidos:

        <form method = POST> 
        <input type = hidden name = "name`=(SELECT'hacked!')WHERE`id`=1#" value = ""> 
        <input type = hidden name = "name" value = "Joe"> 
        <input type = hidden name = "id" value = "1"> 
        <input type = submit> 
        </form> 
    
     <? php 
         if( $_POST ) { 
             $pdo = new PDO('mysql:dbname=test;host=localhost', root', ''); 
             $allowed = ["name", "surname", "email"]; 
     
             $params = []; 
             $setStr = "" ; 
             foreach( $allowed as $key ) 
             { 
                 if( isset($_POST[$key]) && $key != "id" ) 
                 { 
                      $setStr .= "`" . str_replace("`", "``", $key). "` = :" . $key . ","; 
                      $params[$key] = $_POST[$key]; 
                 } 
             } 

             $setStr = rtrim($setStr, ",");
             $params['id'] = $_POST['id']; 
             var_dump("UPDATE users SET $setStr WHERE id = :id", $params, $_POST); 
             $pdo->prepare("UPDATE users SET $setStr WHERE id = :id")->execute($params); 
     }
     ?>  

¡Con tal mejora nuestro código se convertirá en sólido en todo!

Por supuesto, los ejemplos anteriores (punto 4) sólo funcionarán si el modo de emulación está activado. Pero usted tiene que entender que si la inyección del SQL puede ser explotada con éxito o no es una pregunta diferente y completamente irrelevante. Usted no debe dejar una inyección en el primer lugar o la manera de explotarlo se encontrará un día u otro.

5. Entonces, las consultas preparadas sirven o no?

Sí, pero a condición de que nuestro PDO esté configurado como se indica en el apartado 2

5.1 Cómo prevenir inyección de SQL (casi) siempre* garantizado

Utilice las instrucciones preparadas , también conocidas como consultas parametrizadas. Por ejemplo:

/**
 * Note: This code is provided for demonstration purposes.
 *       In general, you want to add some application logic to validate
 *       the incoming parameters. You do not need to escape anything.
 */
$stmt = $pdo->prepare('SELECT * FROM blog_posts WHERE YEAR(created) = ? AND MONTH(created) = ?');
if ($stmt->execute([$_GET['year'], $_GET['month']])) {
    $posts = $stmt->fetchAll(\PDO::FETCH_ASSOC);
}

Las declaraciones preparadas eliminan cualquier posibilidad de inyección de SQL en su aplicación web. No importa lo que se pasa a las variables $_GET aquí, la estructura de la consulta SQL no puede ser cambiada por un atacante (a menos que, por supuesto, tenga PDO::ATTR_EMULATE_PREPARES activado, lo que significaría que no está usando auténticas declaraciones).

Nota: Si usted intenta desactivar PDO::ATTR_EMULATE_PREPARES es posible que algunas versiones de controladores de bases de datos ignoren este intento. Para tomar precauciones extras, establezca explícitamente el conjunto de caracteres en el DSN a uno que su aplicación y base de datos utilizan (por ejemplo UTF-8, el cual, si está usando MySQL, se llama confusamente utf8mb4).

Los estados preparados solucionan un problema fundamental de la seguridad de la aplicación: Separan los datos que se van a procesar de las instrucciones que operan sobre dichos datos enviándolos en paquetes completamente separados. Este es el mismo problema fundamental que hace provoca los desbordamientos de pila.

Siempre y cuando nunca concatenes las variables proporcionadas por el usuario o el entorno con la sentencia SQL (y asegúrandote de no usar preparaciones emuladas) puedes, para todos los propósitos, descartar la inyección de SQL de tu lista de preocupaciones para siempre.

Advertencia Importante y Clarificación

Las sentencias preparadas protegen las interacciones entre su aplicación web y su servidor de base de datos (si están en máquinas separadas, también deben comunicarse a través de TLS). Todavía es posible que un atacante pueda almacenar una carga útil en un campo que podría ser peligroso, por ejemplo, en un procedimiento almacenado. Llamamos a esto una inyección SQL de orden superior (la respuesta de Stack Overflow vinculada se refiere a ellos como de "segundo orden", pero cualquier cosa que se ejecute después la consulta inicial debería ser objeto de análisis).

En esta situación, nuestro consejo sería no escribir procedimientos almacenados de tal manera que creen puntos de inyección SQL de orden superior.

5.2 ¿Qué pasa con el saneamiento de las entradas (Sanitizing Input)?

Si bien es posible prevenir ataques mediante la reescritura del flujo de datos entrantes antes de enviarlo al controlador de la base de datos, esta práctica está llena de matices peligrosos y oscuros... (Ambos enlaces en la oración anterior son muy recomendables.)

A menos que desee tomar el tiempo para investigar y lograr un dominio completo sobre todos los formatos Unicode que su aplicación use o acepte, es mejor que no intente desinfectar sus entradas. Las sentencias preparadas son más eficaces en la prevención de la inyección de SQL que las cadenas de escape.

Además, alterar el flujo de datos entrantes puede causar daños en los datos, especialmente si se trata de bloques binarios crudos (por ejemplo, imágenes o mensajes cifrados).

Las sentencias preparadas son más fáciles y pueden garantizar la prevención de la inyección de SQL.

Si la entrada del usuario nunca tiene la oportunidad de alterar la cadena de consulta, nunca puede conducir a la ejecución de código. Las sentencias preparadas separan completamente el código de los datos.

5.3 La entrada debe ser validada

La validación no es lo mismo que el saneamiento.
Las sentencias preparadas pueden impedir la inyección de SQL pero no pueden guardarlos de datos incorrectos. Para la mayoría de los casos filter_var() es útil aquí.

$email = filter_var($_POST['email'], FILTER_VALIDATE_EMAIL);
if (empty($email)) {
    throw new \InvalidArgumentException('Invalid email address');
}

Nota: filter_var() valida que la cadena de correo electrónico dada se ajusta a la especificación RFC. No garantiza que haya una bandeja de entrada abierta en esa dirección ni compruebe que el nombre de dominio esté registrado. Una dirección de correo electrónico válida todavía no es segura para usar en consultas sin procesar, ni para mostrar en una página web sin filtrar para evitar ataques XSS .

5.4 ¿Qué pasa con los identificadores de columnas y tablas?

Dado que los identificadores de columna y tabla forman parte de la estructura de consulta, no es posible parametrizarlos. Por lo tanto, si la aplicación que está desarrollando requiere una estructura de consulta dinámica donde las tablas o columnas son seleccionadas por el usuario, debe optar por una lista blanca.

Una lista blanca es una estrategia de lógica de aplicación que explícitamente sólo permite unos cuantos valores aceptados y rechaza el resto o utiliza un predeterminado sano. Contrasta con una lista negra que sólo prohíbe las entradas mal conocidas. En la mayoría de los casos las listas blancas son mejores para la seguridad que las listas negras.

$qs = 'SELECT * FROM photos WHERE album = ?';
// Use switch-case for an explicit whitelist
switch ($_POST['orderby']) {
    case 'name':
    case 'exifdate':
    case 'uploaded':
       // These strings are trusted and expected
       $qs .= ' ORDER BY ' . $_POST['orderby'];
       if (!empty($_POST['asc'])) {
           $qs .= ' ASC';
       } else {
           $qs .= ' DESC';
       }
       break;
    default:
       // Some other value was passed. Let's just order by photo ID in descending order.
       $qs .= ' ORDER BY photoid DESC';
    }
$stmt = $db->prepare($qs);
if ($stmt->execute([$_POST['album_id']])) {
    $photos = $stmt->fetchAll(\PDO::FETCH_ASSOC); 
}

Si permite que el usuario final proporcione los nombres de la tabla y/o de las columnas, ya que los identificadores no pueden parametrizarse, debe recurrir a escapar. En estas situaciones, recomendamos lo siguiente:

No : Simplemente escriba los meta caracteres SQL (por ejemplo ' )
Sí : Filtre todos los caracteres que no están permitidos.

El siguiente fragmento de código sólo permitirá nombres de tablas que comiencen con una letra en mayúscula o minúscula seguida de cualquier número de caracteres alfanuméricos y subrayados.

if (!preg_match('/^[A-Za-z][A-Za-z0-9_]*$/', $table)) {
    throw new AppSpecificSecurityException("Possible SQL injection attempt.");
}
// And now you can safely use it in a query:
$stmt = $pdo->prepare("SELECT * FROM {$table}");
if ($stmt->execute()) {
    $results = $stmt->fetchAll(PDO::FETCH_ASSOC);
}

5.6 ¿Qué pasa si el uso de declaraciones preparadas parece demasiado engorroso?

La primera vez que un desarrollador encuentra sentencias preparadas puede sentirse frustrado por la perspectiva de verse forzado a escribir mucho código redundante (preparar, ejecutar, buscar, preparar, ejecutar, buscar, ad nauseam ).

Existe una biblioteca de PHP llamada EasyDB, que puede servir como alternativa al uso de PDO.

6. Enlaces

1. PDO y MySQLi nos protegen de la inyección SQL primaria solamente
2. Inyección SQL de segundo nivel
3. Muy buen artículo con algunas recomendaciones para el uso de PDO

The accepted answer is a great addition, always appreciated. Some time ago I also read an article on the internet that in my opinion is quite interesting and I would like to share it, a pity that it is in English, I am going to translate part of the article as best I can, since it is very extensive ( The Hitchhiker's Guide to SQL Injection prevention ).

A quick answer would be:

How to protect against injection of type [xxx].

You can be attacked by different types of injections - "blind" , "period delay" , "second order" and thousands of others. One has to understand that these are not all different ways of performing an injection, but just different ways of exploiting it. Although there is only one way to perform an injection: to break the integrity of the query . So if you can maintain the integrity of the query, you are safe from all the thousands of different types of injections at once.

And to maintain query integrity, simply format your query literals correctly.

Conclusion.

In summary, we can formulate two simple rules:

Even dynamically created, an SQL query must consist of 2 possible types of data only:

1. Constant parts encoded in the script
2. Placeholders for each dynamic value

When followed, these rules will guarantee 100% protection.

Let's look at a more detailed answer to SQL injection prevention:

1) What is SQL injection?

The source of the problem inyección SQLis the mix of code and data. In fact, our query SQLis a program. A legitimate and complete program, just like our PHPfamiliar scripts. And so it happens that we're building this application dynamically, adding some data as we go. Therefore, this data can interfere with our code and alter it. Such an alteration would be one's own inyección.

This can only happen if we don't format the parts of our query in an invulnerable way.

Let's look at a canonical example:

$nombre = 'Foo'; 
$sentencia = "SELECT * FROM usuario WHERE nombre='$nombre'";

Which is compiled into malicious sequence.

SELECT * FROM usuario WHERE nombre='Foo';

Do they call it inyección? Incorrect. It is an improper formatting of a string literal.

As long as it is formatted correctly, it will not harm anyone:

SELECT * FROM usuario WHERE nombre='Foo\';

Let's take another canonical example,

$id    = "1";
$id    = mysqli_real_escape_string($conexion, $id);
$query = "SELECT * FROM usuario where id = $id";

With less harmful result:

SELECT * FROM usuario WHERE id =1;

Call him inyecciónagain? Once again bad. This is a malformed numeric literal. As long as it's a proper format, an honest

SELECT * FROM usuario where id = 1;

The statement would be positively harmless.

Note: All THE danger is coming from the question statement itself: millions of PHP users still believe that the well-known purpose of the mysql_real_escape_string() function is "to protect SQL against injections" (by escaping some "dangerous characters"). "in a fictitious way). If they knew the true purpose of this honest function, there would be no injections in the world! If they just formatted their queries, instead of "protecting" them, they would have had real protection as a result.

2) What are the formatting rules?

The truth is that the formatting rules are not that easy and cannot be expressed in a single imperative. For MySQL it would be:

1. Chains

• They have to be added via the native prepare statement or the special characters have to be enclosed in quotes and have to be escaped.
• The correct client encoding must or can be encoded in Hexadecimal.
  

2. Numbers

• They must be added via native prepared statement or must be formatted to contain only numbers, a decimal delimiter, and a sign.

3. Identifiers

• They have to be enclosed in single quotes.
• Special characters (frankly - the very delimiting backticks) have to be escaped.

4. Operators and keywords

• There are no special formatting rules for keywords and operators, other than that they have to be legitimate SQL operators and keywords. So, they have to be on the white list.

As you can see, there are four different sets of rules, not just a single statement.

3) Prepared Statements

The idea of ​​a sentencia preparadanative one is smart and simple: the query and the data are sent to the server separately from each other, and therefore there is no possibility of them interfering. What makes impossible the inyección. But at the same time, the native implementation has its limitations, since it only supports two types of literals (namely strings and numbers) which makes them insufficient and unsafe for real-life use.

And here we come to the main point: the general idea of ​​creating a query SQLout of the constant part and the placeholders, to be replaced with real data, to be automatically formatted is indeed a Holy Grail that we were looking for.

The main and most essential benefit of declaraciones preparadasis the elimination of all the dangers of manual formatting:

• Prepared statement does the full formatting. All this without the intervention of the programmer!
• Prepared statement that does the proper formatting (as long as we're binding to our data using the proper type).
• Prepared statements make formatting invulnerable.
• Prepared statement is formatted in the only proper place - just before the execution of the query.

This is why manual formatting is so despised today and prepared statements are so honored.

mysqli_prepare — Prepares an SQL statement for execution:

$mi_conexion = new mysqli("servidor", "usuario", "contraseña", "basedatos");

$id = $_POST['id'] ?: '';

//la consulta y los datos se envían al servidor separados entre sí, lo que evita posible inyección.
$stmt = $mi_conexion->prepare("SELECT nombre FROM usuario WHERE id=?"); //Consulta
$stmt->bind_param("i",$id);//Datos
$stmt->execute();//Ejecutamos sentencia.

4) Some false measurements and bad practices

1. Escaping user input.

This is a king. A serious illusion, still shared by almost all PHP users (and even OWASP, as you can see). It consists of two parts: "Escaping" and "User input":

1. Escaping: As noted above, it does only part of the job, for only one literal type. And when used alone or in the wrong place it's a sure call for disaster.

2. User input: There should be no such words in the context of injection protection. Every variable is potentially dangerous - no matter the source! Or, in other words, all variables have to be correctly formatted to be put into the query - never mind the source again. It is the destination that matters. The moment a developer begins to separate the sheep from the goats, they make their first step toward disaster.

2. Data validation.

One has to understand, that input (in the meaning of user input) data validation has absolutely nothing to do with SQL. Really. No validation rule can help against SQL injection if freeform text is allowed. However, we have to format our SQL despite any validation anyway - remind Sarah O'Hara that it takes a name that is perfectly valid from the point of view of user input. Also remember that validation rules can change.

3. Htmlspecialchars (and also filter_var() , strip_tags() and the like).

Friends. It's the HTML encoding, if you haven't already noticed. It has absolutely nothing to do with SQL. It doesn't help matters at all, and should never be used in the context of SQL injection protection. It is absolutely inapplicable for SQL, and cannot protect your query even if it is used as a string escape function. Leave it for other parts of your app. Also, please understand, that the SQL format should never touch the data. So you put your jewelry in a safe, you want to keep it intact, not some modified or replaced parts! Same here. A database is meant to store your data, not "protect" it. And it's essential to store the exact data you want to reuse (means your silly base64 attempt is wrong too, btw).

Another recommendation aside from the top good answer

It is recommended to add a mask to the url and create friendly urls that perform 2 functions: 1- they hide parameters that are being sent by get, and also help the user to see the information more clearly.

Example

standard url : http://stackoverflow.com?id=100
friendly url : How to avoid SQL injection in PHP?

Note: The first url does not correspond to the friendly url and is just an example.

In the event that the attacker wanted to attack the standard url, he would already have a parameter to start "id", while in the case of the friendly url it is not so simple and he must further analyze the request made to the web server and find the url.

Basic Measurements

How do I mask the url

An example is modifying the .htaccess file on your web server, and binding it with mod_rewrite if you use Apache.

More information about this here: http://www.emenia.es/como-crear-urls-amigables-con-htaccess/

If you think it's not enough

It would be advisable to combine the use of login, cookie tracking, headers, web agent.

Also add a captcha system to avoid multiple requests generated by robots.

Mi respuesta simple, vamos al grano, lo que debes hacer ademas de un sistema de login y sesión de usuarios, es "validaciones" (especialmente en el login), crear un pattern y compararlo con lo que viene del post, (ademas de las validaciones básicas disponibles con html ) si es válido que lo deje pasar, algo así:

public function index(){
            if ($_POST) { 
               $permitidos = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789@-_.'; 
                for ($i=0; $i<strlen($_POST['username']); $i++){ 
                 if (strpos($permitidos, substr($_POST['username'],$i,1))===false){ 

                  $da = true;
                  return $da; 
                  }else{    
                 }
             }...   

Este filtro determina si la cadena de texto contiene caracteres que no están en tu lista de permitidos, después si pasa por el filtro, el resto del código que seria lo que haría, guardar actualizar etc.

luego capturas el $da(datos del return) y lo muestras en el front como variable, para que si existe algo en el return, despliegue un mensaje de error en el formulario.

Por otro lado también deshabilita que puedan copiar y pegar en los inputs del formulario.

<input type="text" class="form-control" id="username" required name="username" onpaste="return false">

En resumen: debes evitar que ingresen caracteres especiales que se usan en las consultas sql, como * ; etc.

Interesante post. Actualmente estoy desarrollando un panel de administrador para un sitio web y el formulario de la página de inicio tiene en total, hasta ahora, 46 campos para texto (incluyendo rutas a imágenes). Primero creo la conexión a la base de datos:

    $conexion = new PDO('mysql:host=localhost;dbname=basededatos;charset=utf8', 'usuario', 'contraseña');

Cuando el usuario le da click al botón "guardar cambios", se ejecuta el siguiente código:

$slogan = $_POST['slogan']; 
   if(strpos($slogan, '<script>') || strpos($slogan, 'Drop') || strpos($slogan, 'SELECT')){
     $slogan = htmlspecialchars($_POST['slogan']);
   }        
   $statement2 = $conexion->prepare("UPDATE inicio SET slogan = :slogan WHERE id=1");
   $conexion->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
   $conexion->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
   $statement2->bindParam(":slogan", $slogan, PDO::PARAM_STR);
   $statement2->execute();

Y luego tengo que realizar el mismo proceso que hice para el slogan 45 veces mas, y eso solo para la página de inicio. Mi pregunta es si estoy previniendo bien la inyección SQL y además previniendo que se puedan ejecutar scripts de Javascript (por el condicional que tiene strpos buscando por "script" para aplicar htmlspecialchars si lo encuentra).

Soy bastante novato en cuanto a PHP y MySql así que agradezco cualquier orientación que me puedan brindar. Saludos.