Tables and Columns names cannot be bind using PDO ->bindParam(), but I am sure more than one would love to be able to. It is a little late, but I wrote this earlier and so far it works. I am kind of new to php, and would like to know what you think and if it is safe.
$type = "defaultTableName";
$sortBy = "defaultColumnName";
$orderBy = "ASC";
//whitelisting unsafe input
if(isset($_GET['orderBy'])&&($_GET['orderBy']=="ASC"||$_GET['orderBy']=="DESC"))
$orderBy = $_GET['orderBy'];
$tableNames = array("defaultTableName", "tableName2", "tableName3");
$unsafeType= $_GET['type']; <---unsafe input
$unsafeSortBy = $_GET['sortBy']; <---unsafe input
try {
$pdo = new PDO("mysql:host=$hostname;dbname=$database", $username, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
//if input is not valid this will use default table to render a table in html.
$stmt = $pdo->prepare("DESCRIBE $type");
$stmt->execute();
$table_fields = $stmt->fetchAll(PDO::FETCH_COLUMN);
//Whitelisting user input against table names (will require table names updates)
if (in_array($unsafeType, $tableNames)) {
$stmt = $pdo->prepare("DESCRIBE $unsafeType");
$stmt->execute();
$table_fields = $stmt->fetchAll(PDO::FETCH_COLUMN);
///Whitelisting the column name to sort by against the description of the table.
if (in_array($unsafeSortBy, $table_fields)) {
$stmt = $pdo->prepare("SELECT * FROM $unsafeType ORDER BY $unsafeSortBy $orderBy");
}
else {
$stmt = $pdo->prepare("SELECT * FROM $type ORDER BY $sortBy $orderBy");
}
} else {
$stmt = $pdo->prepare("SELECT * FROM $type ORDER BY $sortBy $orderBy");
}
$stmt->execute();
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
} catch(PDOException $e) {
echo 'ERROR: ' . $e->getMessage();
}
Only problem I see is that you will need to add/delete/change the table name array when you change the tables. I have a small/medium application in mind, not very complex.
Note: I am also terrible editing in stackoverflow, so if you know a way to make it nicer go ahead and edit or let me know.