Which Tokens Can Be Parameterized in Pdo Prepared Statements

Which tokens can be parameterized in PDO prepared statements?

You cannot parameterize table names, column names, or anything in an IN clause (thanks to c0r0ner for pointing out the IN clause restriction).

See this question, and subsequently this comment in the PHP manual.

UPDATE MySQL using PDO, prepared statements and substituting tokens with bindParam; getting 'variables don't match tokens' error

Single quotes don't interpolate variables.

$stmt->bindParam(':$value', $variable);

should be

$stmt->bindParam(":$value", $variable);

PDO: Passing extra parameters to a prepared statment than needed

I got a chance to test my question, and the answer is you cannot send more parameters than the query uses. You get the following error:

I don't know a huge amount about PDO, hence my question, but I think that because :current_time is sent but not used and the error message is "Invalid parameter number: parameter was not defined" you cannot send extra parameters which are not used.

Additionally the error code HY093 is generated. Now I can't seem to find any documentation explaining PDO codes anywhere, however I came across the following two links specifically about HY093:

What is PDO Error HY093

SQLSTATE[HY093]

It seems HY093 is generated when you incorrectly bind parameters. This must be happening here because I am binding too many parameters.

Parameterized PDO statements: should trusted, constant values be parameterized?

No, there is no benefit to parameterizing constant values.

The purpose of parameterizing is to allow application data to be combined with SQL expressions safely and repeatedly. The safely part is to prevent SQL injection. The repeatedly part is so you can execute a prepared query again with different values, possibly relieving the RDBMS from having to re-parse and re-optimize the query.

Neither of these is an issue if you always use the same constant value in your query. You are not at risk of SQL injection from a hardcoded value, and you can re-execute the query if you need to, without re-parsing.

There's no caching of parameters going on. If anything, using prepared statements makes it harder for MySQL to cache the results (i.e. the query cache has limitation on caching results from prepared statements). But once you use prepared statements, it doesn't matter if it has one parameter versus two or more.

I do wonder why you put the integer in quotes. I see that a lot, but I have no idea how that got started or who thought it was necessary.

PDO prepared statement with optional parameters

Some good old dynamic SQL query cobbling-together...

$sql = sprintf('SELECT * FROM user WHERE name LIKE :name %s %s',
               !empty($_GET['city'])   ? 'AND city   = :city'   : null,
               !empty($_GET['gender']) ? 'AND gender = :gender' : null);

...

if (!empty($_GET['city'])) {
    $stmt->bindParam(':city', '%'.$_GET['city'].'%', PDO::PARAM_STR);
}

...

You can probably express this nicer and wrap it in helper functions etc. etc, but this is the basic idea.

How do you use PDO prepared statements with a BIT(1) column?

PDO::ATTR_EMULATE_PREPARES causes PDO to interact with BIT columns slightly differently. If it is set to false, you just insert the value as normal and MySQL will do any conversion necessary behind the scenes:

$stmt = $dbh->prepare("UPDATE tablename SET
    bit_col = ? ");
$stmt->bindValue(1, $_POST['bit_col']);

However, if PDO is emulating the prepared statements, you need to put a b in there somehow to indicate that it is a BIT type. If you put the b in the bound parameter, PDO will escape the single quotes and you will end up with something like 'b\'0\'' being sent to MySQL, which obviously won't work. The b therefore needs to be in the query, not in the bound parameter. Doing this with named parameters produces the "number of bound variables does not match number of tokens" error above because PDO does not recognize strings with a b followed by a : as a named parameter. However PDO does recognize it as a parameter when you use question mark parameter markers, like this:

$stmt = $dbh->prepare("UPDATE tablename SET
    bit_col = b? ");
$stmt->bindValue(1, $_POST['bit_col']);

Since we need to end up with something like b'1' being sent to MySQL, using PDO::PARAM_INT when binding the value will cause the query to fail because it will become UPDATE tablename SET bit_col = b1 (without the quotes around the number) so you must leave the data type out or use PDO::PARAM_STR.

Also note that if emulating prepared statements is disabled, this query will fail with a syntax error, so unfortunately the queries need to be done completely differently depending on whether or not you are emulating prepares or not.

PHP PDO Prepared Statements and Value Binding Gives Invalid Parameter Number Error

Did you try passing in the entire expression as the bind value?

$sql = 'INSERT INTO '.POLYGON_TABLE.' (user_id, polygon, polygon_type) VALUES (:userId,  PolygonFromText(:polygonArea), :polygonType)';

$sth = $this->pdo->prepare($sql);
$area = sprintf("POLYGON((%s))", $polygon->getPolygonAsText()); 
$sth->bindValue(':userId', $polygon->getUserId(), \PDO::PARAM_INT);
$sth->bindValue(':polygonArea', $area, \PDO::PARAM_STR);
$sth->bindValue(':polygonType', $polygon->getPolygonType(), \PDO::PARAM_STR);

php pdo prepare repetitive variables

The simple answer is: You can't. PDO uses an abstraction for prepared statements which has some limitations. Unfortunately this is one, you have to work-around using something like

$query = "UPDATE users SET firstname = :name1 WHERE firstname = :name2";
$stmt = $dbh -> prepare($query);
$stmt -> execute(array(":name1" => "Jackie", ":name2" => "Jackie"));

In certain cases, such as emulated prepared statements with some versions of the PDO/MySQL driver, repeated named parameters are supported; however, this shouldn't be relied upon, as it's brittle (it can make upgrades require more work, for example).

If you want to support multiple appearances of a named parameter, you can always extend PDO and PDOStatement (by classical inheritance or by composition), or just PDOStatement and set your class as the statement class by setting the PDO::ATTR_STATEMENT_CLASS attribute. The extended PDOStatement (or PDO::prepare) could extract the named parameters, look for repeats and automatically generate replacements. It would also record these duplicates. The bind and execute methods, when passed a named parameter, would test whether the parameter is repeated and bind the value to each replacement parameter.

Note: the following example is untested and likely has bugs (some related to statement parsing are noted in code comments).

class PDO_multiNamed extends PDO {
    function prepare($stmt) {
        $params = array_count_values($this->_extractNamedParams());
        # get just named parameters that are repeated
        $repeated = array_filter($params, function ($count) { return $count > 1; });
        # start suffixes at 0
        $suffixes = array_map(function ($x) {return 0;}, $repeated);
        /* Replace repeated named parameters. Doesn't properly parse statement,
         * so may replacement portions of the string that it shouldn't. Proper
         * implementation left as an exercise for the reader.
         *
         * $param only contains identifier characters, so no need to escape it
         */
        $stmt = preg_replace_callback(
            '/(?:' . implode('|', array_keys($repeated)) . ')(?=\W)/', 
            function ($matches) use (&$suffixes) {
                return $matches[0] . '_' . $suffixes[$matches[0]]++;
            }, $stmt);
        $this->prepare($stmt, 
                       array(
                           PDO::ATTR_STATEMENT_CLASS => array('PDOStatement_multiNamed', array($repeated)))
            );
    }

    protected function _extractNamedParams() {
        /* Not actually sufficient to parse named parameters, but it's a start.
         * Proper implementation left as an exercise.
         */
        preg_match_all('/:\w+/', $stmt, $params);
        return $params[0];
    }
}

class PDOStatement_multiNamed extends PDOStatement {
    protected $_namedRepeats;

    function __construct($repeated) {
        # PDOStatement::__construct doesn't like to be called.
        //parent::__construct();
        $this->_namedRepeats = $repeated;
    }

    /* 0 may not be an appropriate default for $length, but an examination of
     * ext/pdo/pdo_stmt.c suggests it should work. Alternatively, leave off the
     * last two arguments and rely on PHP's implicit variadic function feature.
     */
    function bindParam($param, &$var, $data_type=PDO::PARAM_STR, $length=0, $driver_options=array()) {
        return $this->_bind(__FUNCTION__, $param, func_get_args());
    }

    function bindValue($param, $var, $data_type=PDO::PARAM_STR) {
        return $this->_bind(__FUNCTION__, $param, func_get_args());
    }

    function execute($input_parameters=NULL) {
        if ($input_parameters) {
            $params = array();
            # could be replaced by array_map_concat, if it existed
            foreach ($input_parameters as $name => $val) {
                if (isset($this->_namedRepeats[$param])) {
                    for ($i=0; $i < $this->_namedRepeats[$param], ++$i) {
                        $params["{$name}_{$i}"] = $val;
                    }
                } else {
                    $params[$name] = $val;
                }
            }
            return parent::execute($params);
        } else {
            return parent::execute();
        }
    }

    protected function _bind($method, $param, $args) {
        if (isset($this->_namedRepeats[$param])) {
            $result = TRUE;
            for ($i=0; $i < $this->_namedRepeats[$param], ++$i) {
                $args[0] = "{$param}_{$i}";
                # should this return early if the call fails?
                $result &= call_user_func_array("parent::$method", $args);
            }
            return $result;
        } else {
            return call_user_func_array("parent::$method", $args);
        }
    }
}

Which Tokens Can Be Parameterized in Pdo Prepared Statements