PDO - Prepared statement doesn't handle variable right

Question

I have a variable ($q=$_GET["q"];) that I want to run my prepared statement with. It contains a column name of my database table.

The value of the variable comes from a dropdown list. When I run the following code the output is the exact value that was chosen in the list. So it is just a column name.

$q=$_GET["q"];

$dsn = "mysql:host=$host;port=$port;dbname=$database";

$db = new PDO($dsn, $username, $password);

$sql = "SELECT DISTINCT ? FROM repertoire";
$stmt = $db->prepare($sql);
$stmt->execute(array($q));

    echo "<select>";
    while ( $row = $stmt->fetchObject() ) {
        echo "<option>";
        echo "{$row->{$q}}";
        echo "</option>";
        }
    echo "</select>";

However, When I change this line $sql = "SELECT DISTINCT ? FROM repertoire";

to $sql = "SELECT DISTINCT ".$q." FROM repertoire"; the I get the desired rows from the database...

I'm not so good with PHP, so I guess that my syntax is wrong somewhere.

Thank you in advance for your help.

possible duplicate of [Can PHP PDO Statements accept the table name as parameter?](http://stackoverflow.com/questions/182287/can-php-pdo-statements-accept-the-table-name-as-parameter) — hakre, Jul 08 '12 at 23:48
Can't help but wonder what kind of application needs to get tables and/or column names from user input whenever I see these kind of questions. — Mahn, Jul 09 '12 at 00:00

score 2 · Accepted Answer · edited May 23 '17 at 11:48

In PDO, prepared statements prepare the values, not the tables.

You'll need to handle the user input and quote directly.

$q=$_GET["q"];

// Make sure you sanitize your user inputs using filter_inputs() or similar.

$dsn = "mysql:host=$host;port=$port;dbname=$database";

$colNames = new PDO($dsn, $username, $password); // Create object for getting column names.
$sql = "DESC repertoire Field"; // SQL for getting column names.
$stmt = $colNames->prepare($sql);
$stmt->execute();

$colList = $stmt->fetchAll(PDO::FETCH_COLUMN, 0); // Fetch the results into $colList array.

if (in_array($q, $colList)) { // If the value of $q is inside array $colList, then run.

    $db = new PDO($dsn, $username, $password);

    $sql = "SELECT DISTINCT $q FROM repertoire";
    $stmt = $db->prepare($sql);
    $stmt->execute(array($q));

        echo "<select>";
        while ( $row = $stmt->fetchObject() ) {
            echo "<option>";
            echo "{$row->{$q}}";
            echo "</option>";
            }
        echo "</select>";
}

Also read: Can PHP PDO Statements accept the table or column name as parameter?

Edit: I've added a way to check to make sure $q is a valid column by basically doing a SQL desc in order to get all the column names out of table repertoire.

Alright, no fancy question mark here :) Thanks for your help! — sldk, Jul 08 '12 at 23:30
Err, one reason you should use prepared statements (parametrized queries) is to prevent SQL injection. In this suggested answer, this is completely missing. Very bad example. — hakre, Jul 08 '12 at 23:49
@hakre I had added a comment that the user input should be checked but didn't describe how. I've added a way to ensure that the input matches a valid table name. Good comment on the fact that user inputs should always be sanitized and checked. — Stegrex, Jul 08 '12 at 23:56

PDO - Prepared statement doesn't handle variable right

1 Answers1