Use prepare statement in a constant value?

3
$type = 'post';
$stmtPG = $conn->prepare("SELECT count(*) FROM myTable WHERE entry_type = :type");
$stmtPG->bindParam(':type', $type);
$stmtPG->execute();
$total = $stmtPG->fetchColumn();
$total_paginas = ceil($total/$maximo);

There is a time ago I saw in a question here of the stackoverflow that a guy said that does not need to prepare constant values, I had also given a google search about prepared statement , but I did not get it right.

How should this code be?

Edit:

$stmtPG = $conn->query("SELECT count(*) FROM myTable WHERE entry_type = 'post'");
$total = $stmtPG->fetchColumn();
$total_paginas = ceil($total/$maximo);
    
asked by anonymous 24.09.2018 / 17:24

3 answers

2

Prepared Statement

In short: you will be preparando its query in a protected way to avoid attacks like sql injection and will also gain more performance in query execution.

We must always protect the data that comes from front , that is, that are sent by the user. In your case, the variable that is part of the query is already directly in the code, so it is not necessary to perform the preparation (unless the value of the variable is malicious.What developer would do this?).

In this case, your query might look like this:

$type = 'post';
$stmtPG = $conn->query("SELECT count(*) FROM myTable WHERE entry_type = " .$type);    
$total = $stmtPG->fetchColumn();
$total_paginas = ceil($total/$maximo);

If the value of the variable $type came from outside, for example, a POST = $_POST['tipo'] , the preparação of the query would be extremely important.

Here are some references to study:

Sql Injection

How does SQL injection work?

#

Prepared Statements article

    
24.09.2018 / 18:45
1

Based on the principle that you know what SQL Injection is SQL Injection

First you need to understand something, the Prepared Statement has two functionalities:

  • Escape values (if well used)
  • Optimize repeat searches

So you should use it whenever possible, not necessarily for the first but for the second. Now if you will pass the values by bindParam it depends if you know what value this is, and not whether or not the value is a constant

For example, imagine that you have a class that does some paging queries:

class Paginacao {
    private $itensPorPagina;

    public function __construct($itensPorPagina) {
        $this->itensPorPagina = $itensPorPagina;
    }

    public function buscarPorNome($nome) {
        $stmtPG = $conn->prepare("SELECT * FROM usuarios WHERE nome = :nome LIMIT $this->itensPorPagina");
        $stmtPG->bindParam(':nome', $name, PDO::PARAM_STR);

        //...
    }

    public function buscarPorTelefone($telefone) {
        $stmtPG = $conn->prepare("SELECT * FROM usuarios WHERE telefone = :telefone LIMIT $this->itensPorPagina");
        $stmtPG->bindParam(':telefone', $telefone, PDO::PARAM_STR);

        //...
    }
}

So you would use it that way

$paginacao = new Paginacao(25);

$paginacao->buscarPorNome($_GET["nome"]);

Note that itensPorPagina is not a constant, but who defines its value is the programmer when instantiating the class Paginacao

You do not need to pass itensPorPagina to bindValue because you know its value is just an integer of value 25, being a constant has nothing to do

Now if it was a variable that came from the user, such as the name and phone, then yes, you need to pass bindParam to avoid SQL Injection

Conclusion

You do not need to pass variables as a parameter in SQL statements if you know what their value is. Just pass on what is doubtful (user input)

    
24.09.2018 / 19:45
1

It is necessary to take into account that Prepared Statements do not serve exclusively to avoid SQL Injection , the other great advantage is that the DBMS < in> so you do not have to compile an equal query again.

Having said that, I'd use% w / o% even if the value is a constant because the queries:

SELECT count(*) FROM myTable WHERE entry_type = 'post';
SELECT count(*) FROM myTable WHERE entry_type = 'page';

Theoretically they would be compiled twice by the server, where a Prepared Statement would only be compiled once and the client would only send the parameters to the server.

    
24.09.2018 / 22:34