PDO Select form MYSQL db

Question

There are many hints for this topic, I tried this: How to create a secure mysql prepared statement in php? and many others, but nothing is working. If I want to select something from the database and query without parameters, it's ok. But if I want data for a column and table with parameters, it doesn't work, it returns empty array. Any hints?

There is my code:

function getDataByColumn($column, $table) {
    try {
        $connection = new PDO("mysql:dbname=vydap;charset=utf8;host=127.0.0.1", "...","...");
    } catch (PDOException $e) {
        echo 'Connection failed: ' . $e->getMessage();
    }
    $query = "SELECT ? FROM ?";
//  $query = "SELECT :column FROM :table";
    $stmt = $connection->prepare($query);
//  $stmt->bindParam(':column', $column);
//  $stmt->bindParam(':table', $table);    
    $stmt->bindParam(1, $column);
    $stmt->bindParam(2, $table);
    $stmt->execute();
    $result = $stmt->fetchAll();
    var_dump($result);
}

Right, it won't work that way. This is asked often. Placeholders are not intended for building arbitrary SQL statements. Their purpose is to replace scalar values in a prepared statement. To use dynamic table or column names securely, you have to check the input table and column names against an array list of acceptable possible values. Like `if (in_array($_POST['table'], array('table1','table2')) {// ok to proceed...}` — Michael Berkowski, Dec 12 '14 at 14:22

score 2 · Accepted Answer · answered Dec 12 '14 at 14:23

This is flat-out wrong:

$query = "SELECT ? FROM ?";

placeholders can represent only VALUES. You cannot use placeholders for field/table/db names - those aren't values - they're idenfifiers.

SELECT foo FROM bar WHERE foo = 'baz'
   a    b   c    d   e    f   g   h

a- sql keyword
b- field identifier
c- sql keyword
d- table identifier
e- sql keyword
f- field identifier
g- operator
h- value

Of that entire query, only the h portion is a candidate for using a placeholder.

New kind of layout, I like it. Plus numero uno. – Funk Forty Niner Dec 12 '14 at 14:59 — Funk Forty Niner, Dec 12 '14 at 14:59

score 0 · Answer 2 · answered Dec 12 '14 at 14:23

0

You can't use PDO placeholders on table or columns names. Those are only used for values:

$query = "SELECT * FROM yourTable WHERE someCol = ?";

$stmt->bindParam(1, $value);

answered Dec 12 '14 at 14:23

xlecoustillier

16,183
14
60
85

PDO Select form MYSQL db

2 Answers2