PDO Statement Dúvidas

Question

I wonder if when I use the queries for SQL commands with PDO I need to use bind():

$SQL->bindValue(1, $email, PDO::PARAM_STR); // Seria algo assim?

For example in this case:

$searchSQL = $pdo->prepare('SELECT email,senha FROM tbl_usuario WHERE email = ?');
$searchSQL->execute(array($email));

I must insert that line before the execute() or not:

  $searchSQL = $pdo->prepare('SELECT email,senha FROM tbl_usuario WHERE email = ?');   
  $searchSQL->bindValue(1, $email, PDO::PARAM_STR); Eu uso ela?
  $searchSQL->execute(array($email));

Because searching the internet I saw that I can do SQL queries using queries or psedonimos:

$searchSQL = $pdo->prepare('SELECT email,senha FROM tbl_usuario WHERE email = ?');
$searchSQL = $pdo->prepare('SELECT email,senha FROM tbl_usuario WHERE email = :email');

But in video class, the boy only uses bind() when he wore like this:

$searchSQL = $pdo->prepare('SELECT email,senha FROM tbl_usuario WHERE email = :email');
$searchSQL->bindValue(1, $email, PDO::PARAM_STR);

Is there a problem with using the question marks? And taking advantage of the topic, the third parameter PDO::PARAM, it is specific to the type of the variable?
To String i use PDO::PARAM_STR, To INT me PDO::PARAM_INT and so on or off?

Answer 1

Considering the query:

SELECT email, senha FROM tbl_usuario WHERE email = ?

Utilise bindValue:

$searchSQL->bindValue(1, $email, PDO::PARAM_STR);

Or pass the value through execute:

$searchSQL->execute(array($email));

It has exactly the same effect, since the documentation of execute tells us that the call of bindValue or bindParam for each item passed on array by the parameter. That is, within the execute, passing the parameter, there would be the call of:

$searchSQL->bindValue(1, $email, PDO::PARAM_STR);

Therefore, using both forms concomitantly is unnecessary. However, it is important to point out that with execute, all values will be considered strings, using the third argument equal to PDO::PARAM_STR. If for some reason you need another type of variable, you will need to make the explicit call to bindValue.

Complementary readings:

• What is the difference between bindParam and bindValue?
• What is the advantage of passing the parameter type in bindValue?

Answer 2

Basically the bind adds an extra layer of security at the time of the query, limiting or excluding the chances of SQL injections, should be used after the prepare();

In the case of Binds with queries it is recommended to use the numerical sequence imposed in the example SQL query

$sql = "SELECT * FROM tab_usuario where email = ? and senha = ?";
        $stm = $conexao->prepare($sql);
        $stm->bindValue(1, $email);
        $stm->bindValue(2, $senha);
        $stm->execute();
        $retorno = $stm->fetchAll(PDO::FETCH_OBJ); // instancia o resultado em objetos

Pseudonyms are recommended for example UPDATE and DELETE queries

$sql = 'DELETE FROM tab_usuario WHERE id_usuario = :id and id_fotos = :id';
        $stm = $conexao->prepare($sql);
        $stm->bindValue(':id', $id);
        $retorno = $stm->execute();

Note that in this type of query binds do not follow a chronological order

PDO::PARAM_STR is the 3 parameter that bind object to receive as parameter, serves to define the scope where each data will be executed, example strings with commas and integers without commas. logical that it does not deem itself obligatory in most cases