Variable as column name in select query

Question

I want to select the name contained in the column with the same name as my variable $column_id, which I pass to the function displayed below. It's passed correctly but without putting the '' around it the query results in an error. If I put them the result of the select is the value of $column_id itself, which is wrong. (the syntax equals to the one for prepared queries because that's the next step, if I can fix this issue)

$nirk2 = $this->conn->prepare("SELECT '" .$column_id. "' FROM t_values WHERE device_id='".$device_id."'");
$nirk2->execute(); 
$nirk2->bind_result($Value_Description);
$nirk2->fetch();

All I want to do is basically use my variable $column_id as name of the column to search the value in.

Don't put quotes around column names. That makes it a literal string. — Barmar, Jul 28 '20 at 23:04

score 2 · Accepted Answer · answered Jul 28 '20 at 23:00

If you want to use a string as a column name you need to ensure that this column actually exists (is whitelisted) and then wrap it using backticks.

// whitelist column name
if(!in_array($column_id, ['my_col 1', 'my_col 2'])){
    throw new \Exception('Invalid column name!');
}
//                              V    backticks   V
$nirk2 = $conn->prepare("SELECT `" .$column_id. "` FROM t_values WHERE device_id=?");
$nirk2->bind_param('s', $device_id);
$nirk2->execute(); 
$nirk2->bind_result($Value_Description);
$nirk2->fetch();

1 word: brilliant. I later checked and there was 1 column in the array which shouldn't have been there. Thank you! — Shark44, Jul 29 '20 at 20:31

Variable as column name in select query

1 Answers1