Use bound parameter multiple times

Question

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

Answer 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.

Answer 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

Answer 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.)

Answer 4

It's possible only if you enable prepared statement emulation. You can do it by setting PDO::ATTR_EMULATE_PREPARES to true.

A working solution:

$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, TRUE);
$query = "SELECT * FROM table WHERE name LIKE :term OR number LIKE :term";
$term  = "hello world";
$stmt  = $pdo->prepare($query);
$stmt->execute(array('term' => "%$term%"));
$data  = $stmt->fetchAll();

Answer 5

User defined variables its one way to go and use a the same variable multiple times on binding values to the queries and yeah that works well.

//Setting this doesn't work at all, I tested it myself 
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, TRUE);

I didn't wanted to use user defined variables at all like one of the solutions posted here. I didn't wanted also to do param renaming like the other solution posted here. So here it's my solution that works without using user defined variables and without renaming anything in your query with less code and it doesn't care about how many times the param is used in the query. I use this on all my project and it's works well.

//Example values
var $query = "select * from test_table where param_name_1 = :parameter and param_name_2 = :parameter";
var param_name = ":parameter";
var param_value = "value";

//Wrap these lines of codes in a function as needed sending 3 params $query, $param_name and $param_value. 
//You can also use an array as I do!

//Lets check if the param is defined in the query
if (strpos($query, $param_name) !== false)
{
    //Get the number of times the param appears in the query
    $ocurrences = substr_count($query, $param_name);
    //Loop the number of times the param is defined and bind the param value as many times needed
    for ($i = 0; $i < $ocurrences; $i++) 
    {
        //Let's bind the value to the param
        $statement->bindValue($param_name, $param_value);
    }
}

And here is a simple working solution!

Hope this helps someone in the near future.