Can PHP PDO Statements accept the table or column name as parameter?
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 a switch()
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.
Table name as parameter using PDO/MySQL prepared statement
Table and Column names cannot be replaced by parameters in PDO.
see Can PHP PDO Statements accept the table or column name as parameter?
Can I use name of the column as parameter in PDO?
No, you can't use parameter replacements for any database objects (tables, columns, etc.) in MySQL.
When you think about what a prepared statement actually is, this makes complete sense. As how can MySQL prepare a query execution plan when it does not even know the database objects involved.
I certainly wish that more documentation would actually cover what a prepared statement actually does (beyond it's obvious use for parametrization).
Here is link to MySQL prepared statement documentation for more reading:
https://dev.mysql.com/doc/refman/5.6/en/sql-syntax-prepared-statements.html
Dynamically change column name in PDO statement
You would need to do something like this:
$column = 'someColumn';
$stmt = $db->prepare("UPDATE tableName SET {$column} = :columnValue WHERE ID = :recordId");
Parameterized placeholders are only for values.
I would suggest you read the comment @YourCommonSense posted on your question.
How to do parametrize the table name in a PDO statement?
You cannot parameterize table names, column names, or anything in an IN
clause (it'll have to be bound separately). See this comment on php.net.
See also: Can PHP PDO Statements accept the table or column name as parameter?
Is there a way to use PDO with a dynamic column name in an UPDATE query?
In a prepared statement, a parameter is a constant value that will be passed into the query without affecting how the query will be run. This allows the database to "prepare" the query ahead of time and figure out how it will be executed even without knowing the exact values that will be used.
Using this definition, a query like this does not have any parameters, and so the PDO and non-PDO versions of the query will look the same. Your working (first) example is as good as you're going to get. In fact, I'd claim that your first example actually is the PDO version.
To use a non-database example, a prepared statement is very much like a function in a programming language such as PHP. A function accepts parameters and uses their values, but (in normal circumstances) the parameters are not lines of code that will be run. The same code is run regardless of what the parameter values are - the function code itself is not changed by the parameters.
PDO variable is part of a table name
Prepared statements do not work that way.
The best (safest) way to do this, is to only accept a certain set of values, for example using a switch
statement.
switch ($tbl) {
case "baz":
case "foo":
$tbl = "bar".$tbl;
break;
}
PDO - passing a field name as a variable
If $_GET['section_name']
contains a column name, your query should be:
$query_list_menu = "SELECT " . $_GET['section_name'] . " from myl_menu_hide_show WHERE id=:id";
Giving:
$query_list_menu = "SELECT :section_name from myl_menu_hide_show WHERE id=:id";
$result_list_menu = $db->prepare($query_list_menu);
$result_list_menu->bindValue(':id', $_GET['id'] , PDO::PARAM_INT);
$result_list_menu->execute();
The reason is that you want the actual name of the column to be in the query - you'd changed it to be a parameter, which doesn't really make much sense.
I'll also add that using $_GET['section_name']
directly like this is a massive security risk as it allows for SQL injection. I suggest that you validate the value of $_GET['section_name']
by checking it against a list of columns before building and executing the query.
Dynamic table name setting with PDO
Do not confuse PHP and SQL.
You just used SQL quotes in PHP. While you have to use SQL quotes in SQL.
Linked answer is now fixed, so, I am closing this as a duplicate.
Related Topics
Block Direct Access to a File Over Http But Allow PHP Script Access
How to Update Stock Quantity Using MySQL in PHP
Laravel 5.5 Http Error 500 Unable to Handle This Request
How to Pass Something With Apostrophe to Json
Fatal Error: Call to Undefined Function Sqlsrv_Connect()
Laravel Storage Link Won't Work on Production
Saving Multiple Records in a Laravel Eloquent Create
Getting Data from a Textarea into a Database
How to Remove Accents from Characters in a PHP String
Mongodb Updating Fields in Nested Array
(Php, Mysql) Result Could Not Be Converted to String
Using Maatwebsite Excel 3.1 Export How to Set Background Color for Cells and Font Size for Heading
How to Get a Youtube Video Thumbnail from the Youtube API
Call PHP Function from JavaScript