Pdo Binding Values For MySQL in Statement

PDO binding values for MySQL IN statement

This is the same thing as was asked in this question: Can I bind an array to an IN() condition?

The answer there was that, for a variable sized list in the in clause, you'll need to construct the query yourself.

However, you can use the quoted, comma-separated list using find_in_set, though for large data sets, this would have considerable performance impact, since every value in the table has to be cast to a char type.

For example:

select users.id
from users
join products
on products.user_id = users.id
where find_in_set(cast(products.id as char), :products)

Or, as a third option, you could create a user defined function that splits the comma-separated list for you (cf. http://www.slickdev.com/2008/09/15/mysql-query-real-values-from-delimiter-separated-string-ids/). This is probably the best option of the three, especially if you have a lot of queries that rely on in(...) clauses.

Binding array of values in prepared statement PDO PHP

No, don't implode the values that your going to pass inside the ->execute(), it must be an array:

$fields = implode(", ", $fields);
// $values = implode(", ", $values); // DONT IMPLODE!
$values = array_values($values);

$stmt = $con->prepare("INSERT INTO $table(ID,$fields) VALUES (NULL, ?,?,?)");
$stmt->execute($values);

Or @Augwa's suggestion:

// $fields = implode(", ", $fields); // not needed
// $values = implode(", ", $values); // DONT IMPLODE!

$placeholders = substr(str_repeat('?,', sizeOf($fields)), 0, -1);
// $placeholders = implode(', ', array_fill(0, count($values), '?'));

$stmt = $con->prepare(
    sprintf(
        "INSERT INTO %s (%s) VALUES (%s)", 
        $table, 
        implode(',', $fields), 
        $placeholders
    )
);
$stmt->execute($values);

php PDO query with several parameters to bind

I do it by passing an array, and using question marks as my place holders

I also recommend working up a set of generic functions or a db handler class that you can simply pass a query and array (or maybe a 2nd array with db connection info) and get back an array with a true or false at element 0 and either an error message at element 1 or data from element 1 on (in the case ofa select).

Here's a snippet of mine, modified to take out all the other handling, but it shows how I paramaterize and prepare the query.

    <?php
    $query="insert into tablename(first_name,last_name) values(?,?)";
    $array=array("John","Doe");

    $dbconn = new PDO('mysql:host=' . $hostname . ';port=' . $dbPort . ';dbname=' . $dbName . ';charset=utf8', $username, $password, array(PDO::MYSQL_ATTR_FOUND_ROWS => true));
    $dbconn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $result = $dbconn->prepare($query);
    $result->execute($arr);
    if (!$result) {
        $retVal[] = false;
        $retVal[] = "some sort of error on execute";
        $retVal[] = $dbconn->errorInfo();
        return;
    }
    $retVal[] = true;
    $retVal[] = $dbconn->lastInsertId();

    return;

    ?>

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.

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!

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.

In PHP PDO, how can I bind named parameters of a prepared statement without knowing their names?

There's absolutely no reason to use bindParam.

If your SQL has named placeholders then your array must be associative. You need to call it like this:

queryDB($query, ['name' => $name, 'age' => $age]);

You could then loop with foreach($params as $key => $value) and use bindParam but as I said, there's absolutely no reason to use it.

Instead, pass the array to execute.

function queryDB(PDO $dbh, string $query, ?array $param = null)
{
    $stmt = $dbh->prepare($query);
    $stmt->execute($param);
    return $stmt->fetchAll();
}

P.S. You can even remove the if statement and the call to query. This method does the same thing as prepare and execute. There's no reason to have a special case like this in your code.

---

Related Topics