How to sanitize query to accept table name as parameter PDO php

118
November 18, 2018, at 2:00 PM

I have the following code which works, however, now i'm just wondering if this will be secure, and not sql injectable etc.

I cannot take the approach of whitelisting table names as for my web application, more tables will be generated randomly in the future and will be passed through a URL parameter of table, so therefore I will never know all of the tables.

I use the first query to determine if the table which is passed through the URL parameter actually exists, otherwise I will be exiting the script which i'm yet to add.

$db = "database1";
$table = $_GET['table'];
$stmt = $auth_table->runQuery("Select table_name, table_schema from information_schema.tables where table_schema = :db and table_name = :tablename");
$stmt->execute(array(":db"=>$db,":tablename"=>$table));
$tableRow=$stmt->fetch(PDO::FETCH_ASSOC);
$table_schema = $tableRow['table_schema'].".".$tableRow['table_name'];
$stmt = $auth_table->runQuery("Select * from ".$table_schema."");
$stmt->execute();
$testing=$stmt->fetch(PDO::FETCH_ASSOC);
print_r($testing['level']);
exit();
Answer 1

Whitelist your existing tables

$db = "database1";
$table = $_GET['table'];
$dbh = new PDO('mysql:host=localhost;dbname=database1', $user, $pass);
$tableSql = "SHOW TABLES FROM " . $db;       
$tableRes = $dbh->query($tableSQL); 
$tableArr  = $tableRes->fetch(PDO::FETCH_ASSOC);
$whitelist = $tableArr[0]['Tables_in_database1']; 
if(!in_array($table, $whitelist)){
    exit(); //Or RickRoll Them
}
Rent Charter Buses Company
READ ALSO
MySQL and Laravel - checking existing record before and after

MySQL and Laravel - checking existing record before and after

What's the simplest way to check if the record before which I am looking exists? For example when i search record with ID=2 there is previous record with ID=1, but when i search record ID=1 there isn't record before (same as searching last record ID, there isn't next record)

113
How can i fix MySQL Installer stucked in “Adjusting Windows Service”

How can i fix MySQL Installer stucked in “Adjusting Windows Service”

Im trying to Install MySQL unto my PC but i kept getting stucked in "Adjusting Windows Service" in MYSQL installer, how can i fix this problem

108
Show data from multiple tables to show in Datatables through Json Data?

Show data from multiple tables to show in Datatables through Json Data?

I am currently developing some code to show, in JSON Data, in a Datatable several values from each sale madeHowever, some values are stored in other tables

123
Servlet de Inclusão de Imagem em Byte, para um cambo Blob no Banco de dados [on hold]

Servlet de Inclusão de Imagem em Byte, para um cambo Blob no Banco de dados [on hold]

Estou codificando um cadastro, onde necessito inserir uma imagem, e gravar a mesma no banco dedados, sendo que na minha tabela o atributo é do tipo LongblobEstá acusando como erro na minha servlet de inclusão,

131