CODEWITHSUNDEEP
phpmysqlpdomigration
Adrian Tanase
Adrian Tanase

Reputation: 700

PDO - passing a field name as a variable

I'm just migrating my code from mysql_query style commands to PDO style and I ran into a problem. THe old code looked like this : 

$query_list_menu = "SELECT ".$_GET['section_name']." from myl_menu_hide_show WHERE id='".$_GET['id']."'";

And the updated code looks like below. Apparently it's not working. I store in $_GET['section_name'] a string that represents a field name from the database. But I think there is a problem when I pass it as a variable. Is the below code valid ? Thanks.

$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(':section_name', $_GET['section_name'] , PDO::PARAM_STR);
$result_list_menu->bindValue(':id', $_GET['id'] , PDO::PARAM_INT);  
$result_list_menu->execute();

Upvotes: 4

Views: 4360

Answers (2)

Your Common Sense
Your Common Sense

Reputation: 157863

There is no good and safe way to select just one field from the record based on the user's choice. The most sensible solution would be to select the whole row and then return the only field requested

$sql = "SELECT * from myl_menu_hide_show WHERE id=?";
$stmt = $db->prepare($query_list_menu);
$stmt->execute([$_GET['id']]);
$row = $stmt->fetch();
return $row[$_GET['section_name']] ?? false;

Upvotes: 0

Phil
Phil

Reputation: 2422

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.

Upvotes: 12

Related Questions