• 12
name

A PHP Error was encountered

Severity: Notice

Message: Undefined index: userid

Filename: views/question.php

Line Number: 191

Backtrace:

File: /home/prodcxja/public_html/questions/application/views/question.php
Line: 191
Function: _error_handler

File: /home/prodcxja/public_html/questions/application/controllers/Questions.php
Line: 433
Function: view

File: /home/prodcxja/public_html/questions/index.php
Line: 315
Function: require_once

name Punditsdkoslkdosdkoskdo

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
        )
)
      • 2
    • First of all why must there be a better solution? (btw. you have forgotten to provide specifics what better means in your case) and secondary, why doesn't unnamed parameters work for you? (see Example #2 Prepare an SQL statement with question mark parameters php.net/pdo.prepare)
      • 1
    • @hakre Unnamed parameters impose the same issue since there must be equally as many bound values as ?? A better solution in my case would be to ->bindValue(':term', $term) and use :term multiple times instead of first building the query, and then parsing it to finally be able to prepare it. I guess ? would only make it harder to parse the final query since there are other parameter types as well.
    • So again, why must there be a better solution? Because you want it? I then get the feeling your question is off topic as looking for an off-site resource or library. I do not see the underlying programming problem asked. Sorry.
      • 2
    • @hakre Are you for real or what? The underlying programming problem is that the API is probably missing a key-feature. That is what I'm trying to figure out. I have no problem with extending PDO, but I don't feel the need to reinvent the wheel. As you can see, I've already worked around it, but felt that there must be a better way.
      • 1
    • If you have got a feature request for PDO, feel free to open an issue for that, but I doubt this is a key feature. The key feature is actually to provide bound parameters and that's it. What you are looking for is probably a library that makes it easier to formulate prepared statements, something like an SqlExpression class or a class that wraps / constitutes / represents a prepared statment. And I can not see you worked around it, where is the code? You so far have outlined your requirements, but that alone is not a programming question IMHO. And missing feature can be highly biased.

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.

  • 23
Reply Report

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

  • 10
Reply Report
      • 1
    • This code works for me. The code snippet should be a standard part of anyone's library to make PDO usage easier and more flexible. Thanks!
      • 2
    • Saw this question today, sorry for not responding... This is pretty much what I'm doing today as well. I'll flag this as the solution.
    • This is not a perfect solution. Imagine having a hard coded value in your query (not binded I mean) : description = "Airport:terminal C". Here you will replace the ":term" in the string which is not correct.

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

  • 9
Reply Report
      • 2
    • "this answer" actually says the same as another. If you skip all the scandalous stuff at the beginning, in the end it says that as long as you're using supported version of PHP and set charset through DSN, there are no known vulnerabilities for the emulation mode.

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();
  • 3
Reply Report
      • 1
    • "SELECT * FROM table WHERE name LIKE CONCAT('%', :term, '%') OR number LIKE CONCAT('%', :term, '%')"; $stmt->execute(['term' => $term]); and then there's no need to wrap the variable with %'.
    • Well, this still does not work for queries using unordered parameters. I get HY093 for Invalid parameter number: parameter was not defined. If :term is the only parameter, this will work - but unfortunately I have more parameters.
      • 2
    • I'll test this but I have got to ask: What if :flag is being used between the two :term? I'll mark your question as solved if execute() treats parameters accordingly.

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.

  • 0
Reply Report

Warm tip !!!

This article is reproduced from Stack Exchange / Stack Overflow, please click

Trending Tags

Related Questions