Can PHP Pdo Statements Accept the Table or Column Name as Parameter

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



Leave a reply



Submit