Blog

Avoid sql injection in PHP with PDO class

If we develop complex web applications, or at least with a constant use of database queries, we must be concerned about the security of our information, and therefore ensure the proper functioning of our website. We must avoid making it easy for malicious use of our site, with the aim of harming it, destroying it or obtaining information that we have stored.

Content

  • What is SQL Injection?
  • What can I do to avoid SQL Injection?
  • PDO class methods that filter values
    • 1. PDOStatement::bindParam by variable position
    • 2.PDOStatement::bindParam by variable name
  • Extra: bindParam or bindValue

What is SQL Injection?

SQL Injection is a vulnerability in our sites that allows any attacker to make queries to a database, using incorrect filtering of the information that is passed through the fields and/or variables of a website. These are generally used to extract credentials and perform illegitimate access. A flaw of this type can allow the execution of commands on the server, uploading and reading of files, or even worse, the total alteration of the stored data. A SQL Injection type vulnerability can be exploited both through the GET method and of the POST method of an HTML form. The most common practice is to do it through the GET, however doing it through the POST method can return the same results.

Extended information can be found at this link: SQL Injection.

What can I do to avoid SQL Injection?

To improve the security of our web applications, and avoid different forms of vulnerability, we can use different strategies, although php natively includes different solutions for this purpose. It is then a matter of knowing the problem, being methodical in our developments, and trying to use the best tools for these purposes.

Among other of my many praises to the PDO class, I am going to add one more, the one that I will deal with next and that allows us in a simple way to drastically improve the security of our queries.

PDO class methods that filter values

Specifically, for the class PDOStatement We have the bindParam method. This method allows us to validate values ​​that come directly or indirectly from form variables and that we later use in the text of our queries. There are 2 possible uses for that method bindparamit will be our choice to choose which one we like best.

1. PDOStatement::bindParam by variable position

The first way of use that I am going to explain is by position of the variable. We will achieve this by inserting the question mark “?” where we would concatenate our php variables. Depending on the number of questions, it will be the position of the value to be entered. The first question mark will be position 1, and so on with the rest.

In the following example I will show how to carry out the entire process from the creation of the PDO object to an update query for a table user with columns name and id.

#php
$new_name = $_GET[‘nom’];
$id_user = $_GET[‘user’];
$PDOobject = new PDO(‘mysql:host=localhost;dbname=test’,’root’, ”);
$query = “UPDATE user SET name = ? WHERE id = ?”;
$PDOStatObject = $PDOObject->prepare($query);
$PDOStatobject->bindParam(1, $new_name, PDO::PARAM_STR);
$PDOStatobject->bindParam(2, $id_user, PDO::PARAM_INT);
$success = $PDOStatObject->execute();
if($success){
$rows_result = $objectPDOStat->fetchAll();
}
#/php

In the example code you can see how I collect two variables that come from a GET form for clarity and assign them to two php variables, one of type text $new_name and the other an integer that is the identifier of the table $id_user. Next, after creating the connection object, I create my query by placing symbols “?” where you would normally put the variables directly. When I use the method bindParam of the object PDOStatement You can see how I indicate the position of the question (1 in the case of the name and 2 for the id), and in the third parameter of the method I introduce the PDO constant the type of value that corresponds to it. In this way bindParam will validate that I am entering a value of the indicated type and may give an error in case of erroneous or fraudulent use.

You can see the types of value to enter in the bindParam that correspond to the types of the database in the following link: data_type bindParam.

2.PDOStatement::bindParam by variable name

The second way to use bindParam It is as valid as the first and it only depends on your taste to choose between one and the other. Unlike the previous one, this implementation is more graphic, probably more intuitive, although I think you can easily make a mistake in a letter and go crazy looking for the problem. With the following example you will understand it:

#php
$new_name = $_GET[‘nom’];
$id_user = $_GET[‘user’];
$PDOobject = new PDO(‘mysql:host=localhost;dbname=test’,’root’, ”);
$query = “UPDATE user SET name = :name WHERE id = :id”;
$PDOStatObject = $PDOObject->prepare($query);
$PDOStatobject->bindParam(‘:name’, $new_name, PDO::PARAM_STR);
$objectPDOStat->bindParam(‘:id’, $id_user, PDO::PARAM_INT);
$success = $PDOStatObject->execute();
if($success){
$rows_result = $objectPDOStat->fetchAll();
}
#/php

As you can see, the difference lies in those dummy variable names with the “:” in front of. The operation is identical to the previous method, and the type constants are exactly the same. I do recommend that you use a good source code editor like Sublime Text to avoid errors when writing variables.

Extra: bindParam or bindValue

Although in this publication I have not dealt with the other alternative offered by the method bindValue of PDOStatement, I would like to name it and comment on the main differences and uses.

The method bindValue is used exactly the same as bindParamthe difference is that bindParam collects variables by reference, while bindValue collect them by value. In a use like the one in my examples, its functionality would not change at all, but when we develop more complex code or encapsulate the operation of this object PDOStatement in our own class or through functions, errors may occur to us derived from its misuse.

#php
function execute_query_test(){
$new_name = $_GET[‘nom’];
$id_user = $_GET[‘user’];
$PDOobject = new PDO(‘mysql:host=localhost;dbname=test’,’root’, ”);
$query = “UPDATE user SET name = :name WHERE id = :id”;
$PDOStatObject = $PDOObject->prepare($query);
$PDOStatobject->bindParam(‘:name’, $new_name, PDO::PARAM_STR);
$objectPDOStat->bindParam(‘:id’, $id_user, PDO::PARAM_INT);
return $PDOStatObject;
//MISTAKE!!! the variables $new_name and $id_user are local and therefore are destroyed when the function exits.
}
#/php

As I said bindParam It is susceptible to producing errors if we encapsulate it in functions or classes, since when receiving the variables by reference, if they are of ambit local and if the execute is not performed in the same scope, the reference to those variables passed as value would be lost. In the above example you can see that I return the object $PDOStatObject with the query prepared with the variables $id_user either $new_name before performing the execute and retrieving the result, therefore in this case or any other similar, they would be lost in the object $PDOStatObject and therefore the query would give an error in the code place that we perform the execute(). If it happens to you, do not incur in anger and madness and remember this advice: D.

So why not use bindValue instead of bindParam?

Well well, bindParam It allows you to launch and relaunch the same query having validated it only once. How is this possible? Well, thanks to its use of variables by reference, we can change the value of the “binded” variables and execute the query again and obtain values ​​later. We better see it with this last example:

#php
$new_name = $_GET[‘nom’];
$id_user = $_GET[‘user’];
$PDOobject = new PDO(‘mysql:host=localhost;dbname=test’,’root’, ”);
$query = “UPDATE user SET name = :name WHERE id = :id”;
$PDOStatObject = $PDOObject->prepare($query);
$PDOStatobject->bindParam(‘:name’, $new_name, PDO::PARAM_STR);
$objectPDOStat->bindParam(‘:id’, $id_user, PDO::PARAM_INT);
$success = $PDOStatObject->execute();
if($success){
$rows_result = $objectPDOStat->fetchAll();
//change the values ​​of the referenced variables and run again
$id_user = 24;
$new_name = ‘test’;
$success = $PDOStatObject->execute(); //second query with new values
}
#/php

Leave a Reply