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

Question

*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?

have you tried any of the answers here? https://stackoverflow.com/questions/12344741/binding-multiple-values-in-pdo -- looks promising — Westwick, Feb 20 '18 at 22:55
almost @Drew This answer: https://stackoverflow.com/a/23298742/1194525 — bato3, Feb 20 '18 at 23:00
Since you are using PDO, you don't need to use `bindParam` or `bindValue`: just pass the array of values for the markers to the `execute` method. — , Feb 20 '18 at 23:07
@aendeerei Dwarf has a problem with the template for a variable number of parameters, not binding. — bato3, Feb 20 '18 at 23:16
@bato3 Yes, I know. It was just a suggestion, based on the fact that he wanted to use one of the binding methods, upon sql statement building. — , Feb 20 '18 at 23:22
Related:[An SQL injection against which prepared statements won't help](https://phpdelusions.net/pdo/sql_injection_example) — Your Common Sense, Feb 21 '18 at 07:37

bato3 · Accepted Answer · 2018-02-21T08:13:48.167

2

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

edited Feb 21 '18 at 08:13

answered Feb 20 '18 at 22:57

bato3

2,695
1
18
26

I tried to do something like this, but your solution is more elegant. Thank you for the answer. My question is that, doesn't this compromise security of this PDO because the $key variable won't be escaped? Of course, it should technically come from the developer soit should be trusted, but I sort of assumed everything needstobe secure. Unless we escape it manually. It sounds funny not to ttrust your own code, but what if this was aninterface prototype, for example? – Dwarf Vader Feb 21 '18 at 07:04
1

@DwarfVader just make sure it is coming from the developer, or, rather, from the code. Taking keys from the list predefined in the code considered safe enough. though you still can format identifiers as well. In case when keys are essentially defined by the user, like, a conditional UPDATE or WHERE, just filter them out, again, [against a predefined and hardcoded list](https://phpdelusions.net/pdo_examples/dynamical_update) – Your Common Sense Feb 21 '18 at 07:19
But that's still feeling a bit disappointing. Have you ever think about using ORM? Then the code could look like this: `Model::factory('Workers', ['employeeId' => 518])->values($arguments)->save()` (Example from Kohana https://docs.koseven.ga/guide/orm/examples/validation ) – bato3 Feb 21 '18 at 08:22

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

1 Answers1