Use bound parameter multiple times

I'm trying to implement a pretty basic search engine for my database where the user may include different kinds of information. The search itself consists of a couple of a union selects where the results are always merged into 3 columns.

The returning data however is being fetched from different tables.

Each query uses $term for matchmaking, and I've bound it to ":term" as a prepared parameter.

Now, the manual says:

You must include a unique parameter marker for each value you wish to pass in to the statement when you call PDOStatement::execute(). You cannot use a named parameter marker of the same name twice in a prepared statement.

I figured that instead of replacing each :term parameter with :termX (x for term = n++) there must be a be a better solution?

Or do I just have to bind X number of :termX?

Edit Posting my solution to this:

$query = "SELECT ... FROM table WHERE name LIKE :term OR number LIKE :term";

$term = "hello world";
$termX = 0;
$query = preg_replace_callback("/\:term/", function ($matches) use (&$termX) { $termX++; return $matches[0] . ($termX - 1); }, $query);

$pdo->prepare($query);

for ($i = 0; $i < $termX; $i++)
    $pdo->bindValue(":term$i", "%$term%", PDO::PARAM_STR);

Alright, here is a sample. I don't have time for sqlfiddle but I will add one later if it is necessary.

(
    SELECT
        t1.`name` AS resultText
    FROM table1 AS t1
    WHERE
        t1.parent = :userID
        AND
        (
            t1.`name` LIKE :term
            OR
            t1.`number` LIKE :term
            AND
            t1.`status` = :flagStatus
        )
)
UNION
(
    SELECT
        t2.`name` AS resultText
    FROM table2 AS t2
    WHERE
        t2.parent = :userParentID
        AND
        (
            t2.`name` LIKE :term
            OR
            t2.`ticket` LIKE :term
            AND
            t1.`state` = :flagTicket
        )
)

Solution 1:

I have ran over the same problem a couple of times now and I think i have found a pretty simple and good solution. In case i want to use parameters multiple times, I just store them to a MySQL User-Defined Variable.
This makes the code much more readable and you don't need any additional functions in PHP:

$sql = "SET @term = :term";

try
{
    $stmt = $dbh->prepare($sql);
    $stmt->bindValue(":term", "%$term%", PDO::PARAM_STR);
    $stmt->execute();
}
catch(PDOException $e)
{
    // error handling
}


$sql = "SELECT ... FROM table WHERE name LIKE @term OR number LIKE @term";

try
{
    $stmt = $dbh->prepare($sql);
    $stmt->execute();
    $stmt->fetchAll();
}
catch(PDOException $e)
{
    //error handling
}

The only downside might be that you need to do an additional MySQL query - but imho it's totally worth it.
Since User-Defined Variables are session-bound in MySQL there is also no need to worry about the variable @term causing side-effects in multi-user environments.

Solution 2:

I created two functions to solve the problem by renaming double used terms. One for renaming the SQL and one for renaming the bindings.

    /**
     * Changes double bindings to seperate ones appended with numbers in bindings array
     * example: :term will become :term_1, :term_2, .. when used multiple times.
     *
     * @param string $pstrSql
     * @param array $paBindings
     * @return array
     */
    private function prepareParamtersForMultipleBindings($pstrSql, array $paBindings = array())
    {
        foreach($paBindings as $lstrBinding => $lmValue)
        {
            // $lnTermCount= substr_count($pstrSql, ':'.$lstrBinding);
            preg_match_all("/:".$lstrBinding."\b/", $pstrSql, $laMatches);

            $lnTermCount= (isset($laMatches[0])) ? count($laMatches[0]) : 0;

            if($lnTermCount > 1)
            {
                for($lnIndex = 1; $lnIndex <= $lnTermCount; $lnIndex++)
                {
                    $paBindings[$lstrBinding.'_'.$lnIndex] = $lmValue;
                }

                unset($paBindings[$lstrBinding]);
            }
        }

        return $paBindings;
    }

    /**
     * Changes double bindings to seperate ones appended with numbers in SQL string
     * example: :term will become :term_1, :term_2, .. when used multiple times.
     *
     * @param string $pstrSql
     * @param array $paBindings
     * @return string
     */
    private function prepareSqlForMultipleBindings($pstrSql, array $paBindings = array())
    {
        foreach($paBindings as $lstrBinding => $lmValue)
        {
            // $lnTermCount= substr_count($pstrSql, ':'.$lstrBinding);
            preg_match_all("/:".$lstrBinding."\b/", $pstrSql, $laMatches);

            $lnTermCount= (isset($laMatches[0])) ? count($laMatches[0]) : 0;

            if($lnTermCount > 1)
            {
                $lnCount= 0;
                $pstrSql= preg_replace_callback('(:'.$lstrBinding.'\b)', function($paMatches) use (&$lnCount) {
                    $lnCount++;
                    return sprintf("%s_%d", $paMatches[0], $lnCount);
                } , $pstrSql, $lnLimit = -1, $lnCount);
            }
        }

        return $pstrSql;
    }

Example of usage:

$lstrSqlQuery= $this->prepareSqlForMultipleBindings($pstrSqlQuery, $paParameters);
$laParameters= $this->prepareParamtersForMultipleBindings($pstrSqlQuery, $paParameters);
$this->prepare($lstrSqlQuery)->execute($laParameters);

Explanation about the variable naming:
p: parameter, l: local in function
str: string, n: numeric, a: array, m: mixed

Solution 3:

I don't know if it's changed since the question was posted, but checking the manual now, it says:

You cannot use a named parameter marker of the same name more than once in a prepared statement, unless emulation mode is on.

http://php.net/manual/en/pdo.prepare.php -- (Emphasis mine.)

So, technically, allowing emulated prepares by using $PDO_obj->setAttribute( PDO::ATTR_EMULATE_PREPARES, true ); will work too; though it may not be a good idea (as discussed in this answer, turning off emulated prepared statements is one way to protect from certain injection attacks; though some have written to the contrary that it makes no difference to security whether prepares are emulated or not. (I don't know, but I don't think that the latter had the former-mentioned attack in mind.)

I'm adding this answer for the sake of completeness; as I turned emulate_prepares off on the site I'm working on, and it caused search to break, as it was using a similar query (SELECT ... FROM tbl WHERE (Field1 LIKE :term OR Field2 LIKE :term) ...), and it was working fine, until I explicitly set PDO::ATTR_EMULATE_PREPARES to false, then it started failing.

(PHP 5.4.38, MySQL 5.1.73 FWIW)

This question is what tipped me off that you can't use a named parameter twice in the same query (which seems counterintuitive to me, but oh well). (Somehow I missed that in the manual even though I looked at that page many times.)