Table and Column names CANNOT be replaced by parameters in PDO.
In that case you will simply want to filter and sanitize the data manually. One way to do this is to pass in shorthand parameters to the function that will execute the query dynamically and then use aswitch()
statement to create a white list of valid values to be used for the table name or column name. That way no user input ever goes directly into the query. So for example:
function buildQuery( $get_var )
{
switch($get_var)
{
case 1:
$tbl = 'users';
break;
}
$sql = "SELECT * FROM $tbl";
}
By leaving no default case or using a default case that returns an error message you ensure that only values that you want used get used.
To understand why binding a table (or column) name doesn't work, you have to understand how the placeholders in prepared statements work: they are not simply substituted in as (suitably escaped) strings, and the resulting SQL executed. Instead, a DBMS asked to "prepare" a statement comes up with a complete query plan for how it would execute that query, including which tables and indexes it would use, which will be the same regardless of how you fill in the placeholders.
The plan forSELECT name FROM my_table WHERE id = :value
will be the same whatever you substitute for:value
, but the seemingly similarSELECT name FROM :table WHERE id = :value
cannot be planned, because the DBMS has no idea what table you're actually going to select from.
This is not something an abstraction library like PDO can or should work around, either, since it would defeat the 2 key purposes of prepared statements: 1) to allow the database to decide in advance how a query will be run, and use the same plan multiple times; and 2) to prevent security issues by separating the logic of the query from the variable input.
I see this is an old post, but I found it useful and thought I'd share a solution similar to what @kzqai suggested:
I have a function that receives two parameters like...
function getTableInfo($inTableName, $inColumnName) {
....
}
Inside I check against arrays I've set up to make sure only tables and columns with "blessed" tables are accessible:
$allowed_tables_array = array('tblTheTable');
$allowed_columns_array['tblTheTable'] = array('the_col_to_check');
Then the PHP check before running PDO looks like...
if(in_array($inTableName, $allowed_tables_array) && in_array($inColumnName,$allowed_columns_array[$inTableName]))
{
$sql = "SELECT $inColumnName AS columnInfo
FROM $inTableName";
$stmt = $pdo->prepare($sql);
$stmt->execute();
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
}
Using the former isn't inherently more safe than the latter, you need to sanitize the input whether it's part of a parameter array or a simple variable. So I don't see anything wrong with using the latter form with$table
, provided you make sure that the content of$table
is safe (alphanum plus underscores?) before using it.
(Late answer, consult my side note).
The same rule applies when trying to create a "database".
You cannot use a prepared statement to bind a database.
I.e.:
CREATE DATABASE IF NOT EXISTS :database
will not work. Use a safelist instead.
Side note: I added this answer (as a community wiki) because it often used to close questions with, where some people posted questions similar to this in trying to bind a database and not a table and/or column.
Part of me wonders if you could provide your own custom sanitizing function as simple as this:
$value = preg_replace('/[^a-zA-Z_]*/', '', $value);
I haven't really thought through it, but it seems like removing anything except characters and underscores might work.
As for the main question in this thread, the other posts made it clear why we can't bind values to column names when preparing statements, so here is one solution:
class myPdo{
private $user = 'dbuser';
private $pass = 'dbpass';
private $host = 'dbhost';
private $db = 'dbname';
private $pdo;
private $dbInfo;
public function __construct($type){
$this->pdo = new PDO('mysql:host='.$this->host.';dbname='.$this->db.';charset=utf8',$this->user,$this->pass);
if(isset($type)){
//when class is called upon, it stores column names and column types from the table of you choice in $this->dbInfo;
$stmt = "select distinct column_name,column_type from information_schema.columns where table_name='sometable';";
$stmt = $this->pdo->prepare($stmt);//not really necessary since this stmt doesn't contain any dynamic values;
$stmt->execute();
$this->dbInfo = $stmt->fetchAll(PDO::FETCH_ASSOC);
}
}
public function pdo_param($col){
$param_type = PDO::PARAM_STR;
foreach($this->dbInfo as $k => $arr){
if($arr['column_name'] == $col){
if(strstr($arr['column_type'],'int')){
$param_type = PDO::PARAM_INT;
break;
}
}
}//for testing purposes i only used INT and VARCHAR column types. Adjust to your needs...
return $param_type;
}
public function columnIsAllowed($col){
$colisAllowed = false;
foreach($this->dbInfo as $k => $arr){
if($arr['column_name'] === $col){
$colisAllowed = true;
break;
}
}
return $colisAllowed;
}
public function q($data){
//$data is received by post as a JSON object and looks like this
//{"data":{"column_a":"value","column_b":"value","column_c":"value"},"get":"column_x"}
$data = json_decode($data,TRUE);
$continue = true;
foreach($data['data'] as $column_name => $value){
if(!$this->columnIsAllowed($column_name)){
$continue = false;
//means that someone possibly messed with the post and tried to get data from a column that does not exist in the current table, or the column name is a sql injection string and so on...
break;
}
}
//since $data['get'] is also a column, check if its allowed as well
if(isset($data['get']) && !$this->columnIsAllowed($data['get'])){
$continue = false;
}
if(!$continue){
exit('possible injection attempt');
}
//continue with the rest of the func, as you normally would
$stmt = "SELECT DISTINCT ".$data['get']." from sometable WHERE ";
foreach($data['data'] as $k => $v){
$stmt .= $k.' LIKE :'.$k.'_val AND ';
}
$stmt = substr($stmt,0,-5)." order by ".$data['get'];
//$stmt should look like this
//SELECT DISTINCT column_x from sometable WHERE column_a LIKE :column_a_val AND column_b LIKE :column_b_val AND column_c LIKE :column_c_val order by column_x
$stmt = $this->pdo->prepare($stmt);
//obviously now i have to bindValue()
foreach($data['data'] as $k => $v){
$stmt->bindValue(':'.$k.'_val','%'.$v.'%',$this->pdo_param($k));
//setting PDO::PARAM... type based on column_type from $this->dbInfo
}
$stmt->execute();
return $stmt->fetchAll(PDO::FETCH_ASSOC);//or whatever
}
}
$pdo = new myPdo('anything');//anything so that isset() evaluates to TRUE.
var_dump($pdo->q($some_json_object_as_described_above));
The above is just an example, so needless to say, copy->paste won't work. Adjust for your needs. Now this may not provide 100% security, but it allows some control over the column names when they "come in" as dynamic strings and may be changed on users end. Furthermore, there is no need to build some array with your table column names and types since they are extracted from the information_schema.
Short answer is NO you cannot use dynamic table name, field names, etc in the Prepared execute statement with PDO because it adds quotes to them which will break the query. But if you can sanitize them, then you can safely plop them right in the query itself just like you would with MySQLi anyway.
The correct way to do this is with mysqli's mysqli_real_escape_string() function because the mysql_real_escape_string was removed from PHP hastily without any consideration into how that affects dynamic structure applications.
$unsanitized_table_name = "users' OR '1'='1"; //SQL Injection attempt
$sanitized_table_name = sanitize_input($unsanitized_table_name);
$stmt = $dbh->prepare("SELECT * FROM {$unsanitized_table_name} WHERE 1"); //<--- REALLY bad idea
$stmt = $dbh->prepare("SELECT * FROM {$sanitized_table_name} WHERE 1"); //<--- Not ideal but hey, at least you're safe.
//PDO Cant sanitize everything so we limp along with mysqli instead
function sanitize_input($string)
{
$mysqli = new mysqli("localhost","UsahName","Passerrrd");
$string = $mysqli->real_escape_string($string);
return $string;
}
Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.
Find the answer in similar questions on our website.
Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.
PHP (from the English Hypertext Preprocessor - hypertext preprocessor) is a scripting programming language for developing web applications. Supported by most hosting providers, it is one of the most popular tools for creating dynamic websites.
The PHP scripting language has gained wide popularity due to its processing speed, simplicity, cross-platform, functionality and distribution of source codes under its own license.
https://www.php.net/
DBMS is a database management system. It is designed to change, search, add and delete information in the database. There are many DBMSs designed for similar purposes with different features. One of the most popular is MySQL.
It is a software tool designed to work with relational SQL databases. It is easy to learn even for site owners who are not professional programmers or administrators. MySQL DBMS also allows you to export and import data, which is convenient when moving large amounts of information.
https://www.mysql.com/
Welcome to the Q&A site for web developers. Here you can ask a question about the problem you are facing and get answers from other experts. We have created a user-friendly interface so that you can quickly and free of charge ask a question about a web programming problem. We also invite other experts to join our community and help other members who ask questions. In addition, you can use our search for questions with a solution.
Ask about the real problem you are facing. Describe in detail what you are doing and what you want to achieve.
Our goal is to create a strong community in which everyone will support each other. If you find a question and know the answer to it, help others with your knowledge.