What better and safer way to program PDO?

Question

Good personal I am now switching to PDO in php wanted your opinion to know if the method I am programming is safe and if I am programming PDO the right way because I saw on the internet several ways to program

Example code of how I’m doing

<?php
$result_cat = $conexao->query("SELECT * FROM categorias WHERE menu='home' AND activo=1");
$row_cat = $result_cat->fetch(PDO::FETCH_OBJ);
$result_capa = $conexao->query("SELECT * FROM categorias_anexos WHERE id_mae='".$row_cat->id."' AND seccao='capa'");
$row_capa = $result_capa->fetch(PDO::FETCH_OBJ);
?>

Connection with the bank

$host   =   "localhost";
$bd     =   "sabeonde_sabeonde";
$user   =   "[USUARIO]";
$pass   =   "[SENHA]";

try {
$conexao = new PDO('mysql:host='.$host.';dbname='.$bd.';charset=utf8', ''.$user.'', ''.$pass.'');
$conexao->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    } catch (PDOException $e) {
       echo 'Error : <br>' . $e->getMessage();
}

Testing

$result_capa = $conexao->prepare("SELECT * FROM categorias_anexos WHERE id_mae = :row_cat      AND seccao='capa'");
$result_capa = bindParam(":row_cat", $row_cat->id, PDO::PARAM_INT);
$result_capa->execute();
$row_capa = $result_capa->fetch(PDO::FETCH_OBJ); 

Answer 1

Best Practice Using Prepared Statements:

$id = 5;
try {
    $conn = new PDO('mysql:host=localhost;dbname=meuBancoDeDados', $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);   

    $stmt = $conn->prepare('SELECT * FROM minhaTabela WHERE id = :id');
    $stmt->execute(array('id' => $id));

    while($row = $stmt->fetch()) {
        print_r($row);
    }
} catch(PDOException $e) {
    echo 'ERROR: ' . $e->getMessage();
}

 ?>

In this example, we are using the preparation method to literally prepare the query before the user data is attached. With this technique, SQL Injection is virtually impossible, because the data is never entered in the SQL query itself. Note that instead we use named parameters (:id) to specify placeholders.

The query was executed, when passing a matrix, which contains the data that must be linked to these placeholders.

$stmt->execute(array('id' => $id));

An alternate, but perfectly acceptable approach, would be to use the bindParam method, thus: An alternative way but that is perfectly acceptable and can be used without fear by those who want to use, is to use the bindParam method, thus staying:

$stmt->bindParam(':id', $id, PDO::PARAM_INT);
$stmt->execute();

After calling the execute method, there are different ways to receive the data: an array (the default), an object, etc. In the example above, the default answer is: PDO:: FETCH_ASSOC, which can be easily replaced if necessary.

while($row = $stmt->fetch(PDO::FETCH_OBJ)) {
    print_r($row);
}

In the above code, we specify that we want to interact with the result set in the best object-oriented way. Below I will list some of the options available for this interaction.

• PDO :: FETCH_ASSOC: Returns a matrix.
• PDO :: FETCH_BOTH: Returns a matrix, indexed by column name and 0-indexed.
• PDO :: FETCH_BOUND: Returns TRUE and assigns column values in your result set to PHP variables that were tied.
• PDO :: FETCH_CLASS: Returns a new instance of the specified class.
• PDO :: FETCH_OBJ: Returns an anonymous object, with property names that match the columns.

Answer 2

What you should immediately start being worried about and if possible change in your projects are two things:

1) do not use mysql_*, as they have been discontinued;

2) NEVER use string concatenations in your commands (query’s) as you mentioned in the example:

SELECT * FROM categorias_anexos WHERE id_mae='".$row_cat->id."' AND seccao='capa'

This is the key point for an attack via SQL Injection since the "attacker" can through requests inject commands directly into your query, will an example:

Let’s say I have a query that way:

SELECT * FROM usuario WHERE login = '".$login."' AND password = '".$passwd.'"

This way it is enough for the "attacker" to send the following value to the variable:

1' OR '1' = '1

Meaning my original command will become:

SELECT * FROM usuario WHERE login = '' AND password = '1' OR '1' = '1'

So this query will be valid and will return something of it since clause 1 = 1 is always valid, that is, the security breach is made.

The answer to the question as mentioned is the use of PreparedStatements.

About your use example, it is not clear to me what its function $conexao->prepare returns, but in case she returns a statement the next use will be wrong and should be:

$result_capa->bindParam(":row_cat", $row_cat->id, PDO::PARAM_INT);