Use prepare statement at a constant value?

Question

$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);

A while ago I saw in a stackoverflow question here that a guy said that you don’t need to prepare constant values, I had also googled about prepared statement, but I didn’t get it right.

As that code should be?

Edit:

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

Answer 1

Prepared Statement

In short: you will be preparandoyour query protected to prevent attacks such as sql Injection and will also gain more performance in the execution of the consultation.

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 variable value is malicious. Which developer would do this?).

In that case, your query could 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 variable value $type came from outside, for example, a POST = $_POST['tipo'], to preparação consultation would be of utmost importance.

Here I leave some references for study:

Sql Injection

How an SQL Injection happens?

How to prevent SQL code injection into my PHP code?

Article about Prepared Statements

Answer 2

Assuming you know what it is SQL Injection

First you need to understand one thing, Prepared Statement has two features:

• Escaping values (if well used)
• Optimize Repeated 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 depends on whether you know what that value is, and not whether or not the value is a constant

For example imagine that you have a class that does some pagination 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);

        //...
    }
}

Then you’d 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 at the time of instantiating the class Paginacao

You don’t need to pass the itensPorPagina for bindValue because you know that its value is only an integer of value 25, the fact that it is a constant has nothing to do with

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

Completion

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

Answer 3

It must be borne in mind that Prepared Statements do not serve solely to prevent SQL Injection, the other great advantage is that SGBD caches the Statement so you don’t need to rebuild an equal query.

That said, I would use bindParam even if the value is a constant because the:

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.