StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POis this dynamic (column & table) PHP select query safe?
text
Body
copied!<p>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. </p> <pre><code>$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(); } </code></pre> <p>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.</p> <p>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.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload