What is SQL injection?
SQL injection is a type of computer attack that consists of infiltrating intrusive code within the statements / queries to be executed in the database. This infiltration usually occurs through parameters passed by users to a program or through a web form.
The target of the intrusion is usually malicious and can cause major damage: data disclosure, content modification, identity theft, deletion and destruction of data or structures within the database ...
Many programmers assume that users will always enter valid data and that queries will therefore be reliable, and do not take special measures when putting sentences that execute in the database. That makes your applications vulnerable to this type of attack.
To find more information in Spanish about SQL injection read the Wikipedia article (with resolution examples in different languages), the official PHP documentation or the OWASP website .
EXAMPLE : Imagine that you have a web page with a form to update the user's data in your database, but you do not clean those entries before using them. In PHP you could have something like this:
$sql = "UPDATE usuarios SET nombre = '".$_POST["nombre"]."' WHERE id = ".$_POST["id"];
Now imagine that the malicious user writes that his name is
Pepito' WHERE 1=1;-- . Your SQL statement will look like this:
UPDATE usuarios SET nombre = 'Pepito' WHERE 1=1;--' WHERE id = 123
-- indicate that the following is a comment and is ignored in the database, so it is executed is simply this:
UPDATE usuarios SET nombre = 'Pepito' WHERE 1=1;
The malicious user has injected SQL code and has caused all the users in your database to be called Pepito from now on. If you also allow multiple statements at the same time, the malicious user could pass something like
'; DROP TABLE usuarios;-- and delete the entire table, as is joked (although it would not be a joke) in this classic xkcd strip :
How can I avoid SQL injection?
To avoid SQL injection, simply follow a simple series of guidelines:
ALWAYS DISCONNECT from user input . Preprocess them, heal them or check them, but never use them directly. You must always assume that the user is going to try to attack your database; we want to think that all users are good people, but a single malicious user is enough to destroy everything.
AVOID dynamic SQL . They are the most common error when executing sentences to the database and this is what malicious users take advantage of to attack your code. The solution is easy: do not concatenate the SQL query with the user's inputs, and instead
USE prepared statements (also parameterized calls). They offer a more efficient strategy and less prone to errors. In addition, the main modern database systems support statements prepared with linked variables.
LIMIT access to the database . Do not use superusers (root) but users with personalized / limited access to the database (although this is not always available to all developers).
MODIFY your code . Keep your code up to date on security, do not use obsolete or non-recommended methods. There are reasons why they are obsolete.
For example, an error related to the modernization that is frequently seen in StackOverflow is the particular case of PHP and the functions
mysql_* , which should be avoided and used MySQLi (
mysqli_* ) or PDO instead. For more information on that topic, read the question How to avoid SQL injection in PHP?