4

Is it possible pass a column name as parameter in a prepared MySQL statement? Take the following example:

UPDATE Images
SET :placement = :imageURL
WHERE ID = :titleID;

PDO adds ' around each parameter, so the middle line above becomes:

SET 'Homepage' = '1.jpg'

Which MySQL doesn't like. Is there a way to include parameters for fieldnames in PDO statements and have them accepted?

Otherwise I guess I'll have to write several different PDO statements, depending on what's been chosen(?).

Chuck Le Butt
  • 47,570
  • 62
  • 203
  • 289
  • 6
    [Can I use a PDO prepared statement to bind an identifier (a table or field name) or a syntax keyword?](http://stackoverflow.com/questions/15990857/reference-frequently-asked-questions-about-pdo#15991422) – Your Common Sense Jun 02 '13 at 16:59
  • @YourCommonSense - [working link here](http://stackoverflow.com/questions/23482104/can-i-use-a-pdo-prepared-statement-to-bind-an-identifier-a-table-or-field-name) – dmnc Sep 30 '14 at 12:36

2 Answers2

9

You would need to do something like this:

$column = 'someColumn';

$stmt = $db->prepare("UPDATE tableName SET {$column} = :columnValue WHERE ID = :recordId");

Parameterized placeholders are only for values.

I would suggest you read the comment @YourCommonSense posted on your question.

Phil Cross
  • 9,017
  • 12
  • 50
  • 84
0

In situations such as this, I use a different sort of replacement parameters, like so:

$unitLabel = 'store_number';
$sql = 'select * from users where [unitLabel] = :unit and level = :level;';
$sql = str_replace('[unitLabel]', $unitLabel, $sql);
$params = array(
  ':unit' => 300,
  ':level' => 'admin',
);
$stmt = $dbh->prepare($sql);
$stmt->execute($params);

The prepared SQL query ends up being processed (more or less) as:

SELECT * FROM USERS WHERE store_number = 300 AND level = 'admin';

Which works for my situation. I hope this helps. :)

Dave Morton
  • 671
  • 6
  • 16
  • Is this vulnerable to SQL injection? – GrumpyCrouton Jul 31 '17 at 19:42
  • Not in my situation because the variables involved don't use values from tainted sources. Care must be taken with this method to make sure that any user input from *any* source be both validated and sanitized prior to use, but that should be instinctive for skilled programmers. – Dave Morton Aug 02 '17 at 05:29
  • The way that I implemented this was I just used `"SELECT * FROM table WHERE {$var} = ?"` instead of using str_replace. I used an array of possible parameters and checked if `$var` existed there, if it did not, it killed the page. In your opinion, is this secure? – GrumpyCrouton Aug 02 '17 at 12:42