CODEWITHSUNDEEP
phpmysqlsqlpdo
Dwarf Vader
Dwarf Vader

Reputation: 429

PHP - secure PDO prepared statement with an unknown number of parameters

*I saw question about this topic for mysqli or fringe cases for PDO, but didn't find this specific question (which surprised me, but maybe I don't know how to search)

Before trying to use PDO, I used simple, unprotected queries. My prepare function looked like this, simplified:

static function prepareUpdate($table, array $arguments, array $filter) {
    $argumentsList = self::associateArguments($arguments);
    $filterList = self::associateArguments($filter);

    $query = "UPDATE `$table` SET $argumentsList WHERE $filterList;";
    return $query;
}

$arguments and $filter are associative arrays: ['name' => 'John', 'occupation' => 'Carpenter'] for $arguments, ['employeeId' => 518] for filter, for example. Let's say $table is 'workers'. Through some simple functions, it becomes:

argumentsList becomes

`name` = 'John', `occupation` = 'Carpenter'

filterList becomes

`employeeId` = 518

So the final string becomes:

UPDATE `workers` SET `name` = 'John', `occupation` = 'Carpenter' WHERE `employeeId` = 518;

That seemed simple enough for me. Then, I tried to use similar logic in PDO's prepare statements. But I ran into trouble. Maybe because I don't understand the intended philosophy behind it? Or just technicalities.

PDO needs to use the bindValue() method and such after prepare() to be secure against injections, if I understand correctly. And, I have to use placeholders (:name, :occupation, etc).

The question is, how do I do that if I don't know what columns I will be looking for, or even how many?

Most examples I see include a very pre-defined statement: UPDATE tableX SET `name` = :name WHERE `id` = :id - or something along those lines.

So what if I want to use a variable amount of parameters? My understanding is that I can only substitute one :placeholder with one property, not more (hence why the quotes aren't needed), so I can't exactly write something like:

UPDATE :table SET :allParametersHere ;

How would I go about this? I don't have a definite table that I want to use this function on, much less a reasonable list of valid columns to check against. How would I create a wrapper that would work with different argument amounts?

Upvotes: 0

Views: 668

Answers (1)

bato3
bato3

Reputation: 2815

For such problems, the database query builder solutions would be better.

Returning to the question: you know the number of parameters, so you can create query template.

<?php
$wq = $pq = [];
$allowed_keys = ['name', 'occupation', '...'];

foreach ($arguments AS $key => $value) {
  if(in_array($key, $allowed_keys))
    $pq[] = "$key = :$key" ;
  else throw new Exception('Go away hacker!');
}
foreach ($filter AS $key => $value)
  if(in_array($key, $allowed_keys))
    $wq[] = "$key = :$key" ;

$q = "UPDATE $table SET ". join(', ', $pq);
if(!empty($wq))
  $q .= 'WHERE '. join(' AND ', $wq);

In results you get query pattern like:

UPDATE workers SET name = :name, occupation = :occupation WHERE employeeId = :employeeId;

Such folding is allowed (if you have "secure" keys to these tables)

$allowed_keys or better $allowed_column_names

Upvotes: 2

Related Questions