Binding Parameters for Where in Clause with Pdo

Binding parameters for WHERE IN clause with PDO

You can't bind a parameter for the IN clause like that. The $myArray string will only count as one value, like if you did this:

SELECT foo FROM bar WHERE ids IN ('1,2,3')

Even though there are three comma delimited values, the database reads them as only one string value.

You need to manually insert the IN list into the query, the old-school way.

'SELECT foo FROM bar WHERE ids IN (' . $myArray .')'

There is unfortunately no other way. At least for now.

Can't bind parameters to WHERE clause PDO

As you can see on this link you the PDO's execute method returns boolean by definition.

I would recommend this approach:

$rec = $dbh->prepare('SELECT clearedbal FROM bankrec WHERE bankno = :bankno ');
if($rec->execute(['bankno'=>$bankno])){
  $result=$rec->fetch(PDO::FETCH_ASSOC);
  //do another stuff there
} else {
  //Query failed handle error
}

As you can see you can use the execute in order to determine if query sucessfully executed or not. Also if you need to fetch more than one line of result you should use the fetchAll method: http://php.net/manual/en/pdostatement.fetchall.php

PHP - Using PDO with IN clause array

PDO is not good with such things. You need to create a string with placeholders dynamically and insert it into the query, while binding array values the usual way. With positional placeholders it would be like this:

$in  = str_repeat('?,', count($in_array) - 1) . '?';
$sql = "SELECT * FROM my_table WHERE my_value IN ($in)";
$stm = $db->prepare($sql);
$stm->execute($in_array);
$data = $stm->fetchAll();

In case there are other placeholders in the query, you could use the following approach (the code is taken from my PDO tutorial):

You could use array_merge() function to join all the variables into a single array, adding your other variables in the form of arrays, in the order they appear in your query:

$arr = [1,2,3];
$in  = str_repeat('?,', count($arr) - 1) . '?';
$sql = "SELECT * FROM table WHERE foo=? AND column IN ($in) AND bar=? AND baz=?";
$stm = $db->prepare($sql);
$params = array_merge([$foo], $arr, [$bar, $baz]);
$stm->execute($params);
$data = $stm->fetchAll();

In case you are using named placeholders, the code would be a little more complex, as you have to create a sequence of the named placeholders, e.g. :id0,:id1,:id2. So the code would be:

// other parameters that are going into query
$params = ["foo" => "foo", "bar" => "bar"];

$ids = [1,2,3];
$in = "";
$i = 0; // we are using an external counter 
        // because the actual array keys could be dangerous
foreach ($ids as $item)
{
    $key = ":id".$i++;
    $in .= ($in ? "," : "") . $key; // :id0,:id1,:id2
    $in_params[$key] = $item; // collecting values into a key-value array
}

$sql = "SELECT * FROM table WHERE foo=:foo AND id IN ($in) AND bar=:bar";
$stm = $db->prepare($sql);
$stm->execute(array_merge($params,$in_params)); // just merge two arrays
$data = $stm->fetchAll();

Luckily, for the named placeholders we don't have to follow the strict order, so we can merge our arrays in any order.

PDO dynamic parameter binding where clause AND between dates

I found the solution.

Unset the date variables (e.g. unset($searchTerms['fromDate'], $searchTerms['toDate']);) and add them again at the end of the query.

if (isset($_GET['fromDate']) && !empty($_GET['fromDate'])) {
    $sql .= " AND Date >=:from_date";
}

Hope this can help other people with dynamic parameter binding issues.

Bind parameters with PDO in PHP

When building a dynamic query you need to separate those parts of the query that are static from those that are dynamic.

You can see that the following code is static.

"SELECT * FROM faq ";

The rest of the code is dynamic. When filtering records the WHERE clause is used and the AND & OR operators are used to filter records based on more than one condition. The AND operator displays a record if both the first condition AND the second condition are true. The OR operator displays a record if either the first condition OR the second condition is true. so for the first condition WHERE is used but after that AND or OR must be used(using OR in your example)

// Static code
sql = "SELECT * FROM `faq`"
// Set initial condition to WHERE       
clause = "WHERE";       
if( !empty( filter )){
    Add clause to sql 
    Add condition to sql
    change clause to OR or AND as required
}

Repeat for each filter
Note the filter is not changed until a filter is applied and remains changed once changed.
The remaining static code, if any,is added after all the filters have been handled.

I have used Switch Case to apply filters and unnamed parameters ?.

Use "lazy" binding when possible - passing data into execute will dramatically shorten your code. See PDO info.

//Test $POST[] remove after testing
$_POST['keyword'] ="one two three four five six";
$keyword = ($_POST['keyword']);
$keyword_array = split(' ',$keyword);
$words = count($keyword_array);
echo $words;
//You need an array to store parameters
 $paramArray =array();
//Initial clause
$clause = "WHERE";
//Start with a basic stub
$sql = "SELECT * FROM faq ";
switch (true) {
    case $words <= 3:
        $sql .= " $clause question LIKE ?";
        $keyword = "%$keyword%";
        array_push($paramArray,$keyword);
        $limit = " LIMIT 14";
        break;

    case $words < 6:
        for($i = 0 ; $i<count($keyword_array); $i++){
            $sql .= " $clause question LIKE ?";
            $keyword = "%$keyword_array[$i]%";
            array_push($paramArray,$keyword);
            $clause = "OR";
            $limit = " ORDER BY question ASC LIMIT 0, 8";
        }
        break;

    case $words >=6:
        $clause = "";
        for($i = 0 ; $i<count($keyword_array); $i++){
            $sql.=" $clause MATCH (answer) AGAINST (? in natural language mode) ";
            array_push($paramArray,$keyword_array[$i]);
            $clause = "OR";
            $limit = "  limit 0,5";
        }
        break;   
}
//echo query and parameter array remove after testing
echo $sql;
echo "<br>";  
print_r($paramArray);

//Prepare and execute query 
$execute_faq_query = $conn->prepare($sql);
$execute_faq_query->execute($paramArray);
$execute_faq_query->setFetchMode(PDO::FETCH_ASSOC);
 while ($list = $execute_faq_query->fetch()){
 }

Dynamic PDO parameter binding issue with WHERE clause

When you iterate through an array to bind values to the PDO statement you should use bindValue instead of bindParam.

When you say $stmt->bindparam(':'.$key, $value), the query will use the value of the variable $value as it is at the time of the query execution. Value of $value will be the last element of the array.

http://php.net/manual/en/pdostatement.bindvalue.php

I hope this helps.

Understanding PDO Prepared Statements and Binding Parameters

You're correct that the first case is insecure. It's important to understand though, that preparing a statement only has value if you are using variable data, and/or executing the same query repeatedly. If you are executing plain statements with no variables, you could simply do this:

$sql = "SELECT * from myTable WHERE this_column IS NOT NULL";
$result = $conn->query($sql);

And end up with a PDOStatement object to work with, just like when you use PDO::exec().

For your second case, again, you're largely correct. What's happening is the variable passed to the database is escaped and quoted (unless you specify otherwise with the third argument to PDOStatement::bindParam(), it's sent as a string which is fine for most cases.) So, the query won't "fail" if bad data is sent. It behaves exactly as if you had passed a valid number that didn't exist as an ID in the database. There are, of course, some edge cases where you are still vulnerable even with a correctly prepared statement.

Also, to make life easier, you can use prepared statements like this, to do implicit binding:

$sql = "SELECT * FROM myTable WHERE id = :id";
$stmt = $conn->prepare($sql);
$stmt->execute([":id"=>$id]);

Or even like this, with un-named parameters:

$sql = "SELECT * FROM myTable WHERE id = ?";
$stmt = $conn->prepare($sql);
$stmt->execute([$id]);

Naturally, most of this has been explained in the comments while I was typing up the answer!

PDO bind array to Where IN

You could use some string manipulation.

You can count the number of ? you'd need by using str_repeat("?", count(explode(",", $refIdsPartial))). This will create your placeholders.

$totalCount = 
"SELECT referral, COUNT(username) AS cnt FROM accounts
WHERE referral IN (". str_repeat("?,", count(explode(",", $refIdsPartial))-1) . "?) GROUP BY referral";

Now that the placeholders are in place, you can explode the , from the string and execute

$ps_totalCounts->execute( explode(",", $refIdsPartial) );

---

Related Topics