Home / Questions / 90081
Accepted
A. Cedano
Asked: 2020-07-25 05:48:55 +0800 CST 2020-07-25 05:48:55 +0800 CST

Why does PDO receive parameters without colons when using prepared queries?

  • 772

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

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

SELECT * FROM usuario WHERE usuario_id=:id

Then, to pass a value relative 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 is the proof:Ver código

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

I am surprised that the Manual does not mention anything about it. I've even read somewhere that it's better to pass parameters in without using the colon.

php

1 Answers

  1. Best Answer

    Why does it work?

    In the PHP language , markers by name are expected to have a colon in the SQL statement , as the official documentation indicates. The documentation for PDOStatement::bindParam()indicates that the parameter must be of the form :nombrewhen the parameter is bound 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 safely make some assumptions. If you look pdo_sql_parser.cat the PHP source code, specifically at 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, :nombreis a valid SQL marker, but nombreand ::nombreare not.

    This means that, at prompt time bindParam()or execute()the parser can safely assume that the called parameter nombrereally should 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 estrndupcan be seen at this link .

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

    P.S. The information has been taken from this answer from the English site .

    • 9