php – Why does PDO receive parameters without the colon when using prepared queries?

Question:

As we know, when we use PDO there are two ways to prepare our queries: using placeholders ? or using name markers :nombre .

In the SQL statement, when using name markers, each name is preceded by a colon ( : . For example:

SELECT * FROM usuario WHERE usuario_id=:id

Then, to pass a relative value to :id , the Manual shows examples similar to this :

$stmt->bindValue(":id",$id);

O well:

$stmt->bindParam(":id",$id);

or directly as an array in the execute :):

$stmt->execute([":id"=>$id]);

But it turns out that if we don't put the colon when passing the parameters, the code works exactly the same.

That is, if I pass the parameters like this:

$stmt->bindValue("id",$id);

or this way:

$stmt->bindParam("id",$id);

or this way:

$stmt->execute(["id"=>$id]);

the code works exactly the same and returns the expected results.

Here's the proof: Ver código

Why does PDO work without the colon when passing parameters to it?

It surprises me that the Manual does not mention anything about it. I have even read somewhere that it is better to pass the parameters in without using the colon.

Answer:

Why does it work?

In the PHP language , placeholders by name are expected to have a colon in the SQL statement , as indicated by the official documentation. The documentation for PDOStatement::bindParam() indicates that the parameter should be of the form :nombre when binding the parameter to the marker . But that is not necessarily true, for the following reasons.

There is no risk of ambiguity when binding the parameters or executing the query because the marker in SQL must have a colon, only once. This means that the PHP interpreter can make some assumptions without any risk. If you see pdo_sql_parser.c in the PHP source code, specifically in line 90 , you can see the list of valid characters in a marker, which would be, alphanumeric (digits and letters), underscores and colons. A short summary of this file is that only the first character can be the colon character.

In a simple example,: :nombre is a valid SQL marker, but nombre and ::nombre not.

This means that, when indicating bindParam() or execute() the parser can safely assume that the parameter called nombre should really be :nombre . So, you could just add a : before the rest of the word. In fact, that's exactly what it does, in pdo_stmt.c , starting at line 362 we see:

if (param->name) {
    if (is_param && param->name[0] != ':') {
        char *temp = emalloc(++param->namelen + 1);
        temp[0] = ':';
        memmove(temp+1, param->name, param->namelen);
        param->name = temp;
    } else {
        param->name = estrndup(param->name, param->namelen);
    }
}

In simple pseudocode, what this function does is the following:

SI el parametro tiene nombre ENTONCES
    SI el nombre del parámetro no empieza con ':' ENTONCES
        asignar una nueva cadena, 1 caractér mas largo que el nombre actual
        añadir ':' al comienzo de ese string
        copiar el resto del nombre del parámetro a la nueva cadena
        reemplazar la cadena antigua con la nueva
    EN OTRO CASO
        llamar a estrndup, que básicamente solo copia la cadena asi como esta

* Extra information about estrndup can be seen at this link .

So, nombre (in the context of bindParam() or execute() ) becomes :nombre , which matches our SQL, and the PDO requirements are satisfied.

PS The information has been obtained from this answer of the site in English .

Post Views: 3
Scroll to Top