Using PDO to select when param columns are unknown/variable

Question

For simplicity's sake, let's say we have this rather contrived table:

[ID]  [Weekday]  [Weather]
 1      Sun        Cloudy
 2      Mon        Sunny
...     ...         ...
 8      Sun        Cloudy
...     ...         ...
15      Sun        Windy

And I'm hitting that table for datasets. Sometimes I want data based on the weekday, sometimes based on the weather. So I create this class:

class weather {

    public static function reportByDay($weekday) {
        return self::weatherServer('Weekday',$weekday);
    }

    public static function reportByWeather($weather) {
        return self::weatherServer('Weather', $weather)
    }

    private static function weatherServer($reportType, $value) {
        $q = "SELECT ID, Weekday, Weather
                FROM table
                WHERE $reportType = $value";
        $r = mysql_query($q);
        etc etc.
        return $results;
    }
}

So I wanted to convert this to PDO, but discovered this morning that the WHERE :field = :thing structure doesn't work... at least I can't make it work.

If I delineate the column, so WHERE Weather = :thing then it works nicely... but I've just lost the convenience of the original class structure because I'd have to type out all of those specialized queries... and there are a lot for my real dataset & table structure.

Is there a PDO way to use params for columns? or can params only be used for values?

cwallenpoole · Accepted Answer · 2011-07-21 00:46:23Z

3

It looks like you already have half the answer -- don't make PDO bind the column, do that "manually" just as you were doing:

private static function weatherServer($reportType, $value) {
    // you may want to sanitize reportType, but because this is private, you 
    // might not need to
    $q = "SELECT ID, Weekday, Weather
            FROM table
            WHERE $reportType = :value";
    // no idea where $pdo would be declared in your framework. It's up to 
    // what you feel best meets your need.
    $stmt = $pdo->prepare($q);
    $stmt->bindParam(":value",$value);
    etc etc.
    return $results;
}

answered Jul 21, 2011 at 0:46

cwallenpoole

82.4k26 gold badges132 silver badges174 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Drew Over a year ago

/face palm duh! I'm already defining columns in the methods, no reason not to keep doing so except with the values via PDO... got a little carried away with my PDO conversion it seems! Thanks for the kick in the head.

alex · Accepted Answer · 2011-07-21 00:43:32Z

3

Keep a safe list and use string concatenation or interpolation to put the column name in there.

$validColumns = array(
   'Weather',
   'Weekday'
);

if ( ! in_array($reportType, $validColumns)) {
   throw new Exception('Not a valid column name.');
}

$q = "SELECT ID, Weekday, Weather
                FROM table
                WHERE `$reportType` = :value";

answered Jul 21, 2011 at 0:43

alex

492k205 gold badges891 silver badges992 bronze badges

1 Comment

zerkms Over a year ago

Indeed. Whitelisting is the best way to put column names from outside.

Collectives™ on Stack Overflow

Using PDO to select when param columns are unknown/variable

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related