doubt queries mysqli vs poo queries prepared


I have been working with the mysqli driver for several months but a question has arisen more than a question is a doubt, I would like to obtain opinions from experts on this topic. The thing is that whenever I consult the bd either to insert or whatever I always do the following example:


and then

$sql=mysqli_query($conexion,"SELECT * FROM contenido WHERE id='$id' ");

DOUBT: Is this vulnerable to sql attacks? or should I use prepared queries?

The thing is that I'm very adapted to the mysqli driver I use it very well but I'm about to change to the object-oriented style -> Note: I do not like PDO.

What do you recommend and which would be better and why? Thanks in advance guys.

asked by andy gibbs 24.08.2018 в 15:48

2 answers


Although mysqli corrected errors from the previous driver; your query is still   without being prepared query given which would be vulnerable to attacks   SQL injection; As you put it on, I show you how it should look

$sql=$conexion->prepare($conexion,"SELECT * FROM contenido WHERE id=? ");
$sql->bind_param("i", $id);

Optionally you can leave the arrival of the id with your next query



But you should include in your query that instead of executing it directly, first go to the method prepare and instead of passing the variable directly you place a placeholder with the symbol of?, then with the method bin_param () you get, for example, to identify the type of data that will arrive at the parameter, in this case it is an id must be an integer, since this also prevents you from injecting a value that does not correspond; finally when the value of the placeholder is exchanged for the bind_param method only if the data type corresponds to the line that says execute () to process said query



You can continue working with it, however it always includes the   structure of the queries prepared, to give greater security to   your development in the SQL part, PDO is sometimes recommended for advantages such as connection access to multiple database managers

ABOUT mysqli_real_escape_string

Although according to PHP page

mysqli :: real_escape_string - mysqli_real_escape_string - Escapes the special characters of a string to be used in an SQL statement, taking into account the current character set of the connection

There is a security risk because:

The character set must be set at the server level, or with the mysqli_set_charset () function of the API to affect mysqli_real_escape_string (). See the concepts section on character sets for more information.

Here more info


If you want to work the same sentence at the PDO level; the form would be the following

Now instead of using a placeholder with the symbol of?, we use the name marker; in this way :nombrevalor

To be able to indicate what type of value should receive the query we use PDO::PARAM_INT so we can establish the type of data that should be received

$id = $_GET["id"];
$query = $conexion->prepare("SELECT * FROM contenido WHRERE id = :id");
$query->bindParam(":id", $id, PDO::PARAM_INT);

Final recommendation uses PDO

answered by 24.08.2018 / 15:57

1- Security level is much better to use PDO, you use prepared statements which reduces the risk of sql injection.

2- is adaptable to several Databases, oracle, postgreSQL, sql at last

3- I think that mysqlli is part of the past and as a rule should not be used anymore.

The only disadvantage is that the performance goes down but it is almost insignificant to what you earn.

answered by 24.08.2018 в 16:01