What is SQL injection and how can I avoid it?


I have found many questions in StackOverflow about web programs or forms that store information in a database (especially in PHP and MySQL) and that contain serious security problems related mainly to the SQL injection .

Normally I leave a comment and / or a link to an external reference, but a comment does not give much space for a lot and it would be positive if there was an internal reference in SOes on the subject so I decided to write this question / answer: What is SQL injection and how to avoid it?

asked by Alvaro Montoro 20.05.2016 в 06:13

3 answers


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

The characters -- 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?

answered by 13.04.2017 / 15:00

Try to always use parameters in the query, never concatenate the variables directly on the query, example:

string consulta = "SELECT * FROM Usuarios WHERE UserName = '"+txtUserName.Text+"';";

The option that avoids that is with parameters and depending on the language you assign the variable:

string consulta = "SELECT * FROM Usuarios WHERE UserName = @UserName;";

I hope this information helps you.

answered by 01.03.2017 в 21:27

If you use php a good way to prevent SQLinjection is by using the native preg_match function of php, you get an array with reserved sql words and some others, remember that sql injection is not the only attack you can receive a web system, or a page, whatever you want to call it, and with it all data that the user sends, no one enters in a name or address or whatever, words like select or union, which concatenates more than one select

select 1 from dual;
union all
select 2 from dual;

and also catch rare characters like / '% & amp ;, etc so that they could not happen to accidentally damage your system or your information. Here is the api of preg match link luck. greetings

answered by 27.07.2016 в 21:25