How to pass mysql table field name as parameter via PDO PARAM?

Question

I have a form where the user sets some criteria for the system to do a search for data in a mysql database. Among the criteria, the user can choose two search options. These options are two fields of the table of this database.

The data is sent via POST and I am using PDO to perform the search with a query sql.

$dataini = filter_input(INPUT_POST, 'data_ini', FILTER_SANITIZE_STRING);
$dataend = filter_input(INPUT_POST, 'data_fim', FILTER_SANITIZE_STRING);

$dataInicial = DateTime::createFromFormat('d/m/Y', $dataini)->format('Y-m-d');
$dataFinal = DateTime::createFromFormat('d/m/Y', $dataend)->format('Y-m-d');
$opdata = filter_input(INPUT_POST, 'op_data', FILTER_SANITIZE_STRING);

$sql = "SELECT nome, setor FROM mytable WHERE :opdata BETWEEN :datainicial AND :datafinal ORDER BY :opdata DESC";

$res = $conecta->prepare($sql);
$res->bindParam(':datainicial', $dataInicial, PDO::PARAM_STR);
$res->bindParam(':datafinal', $dataFinal, PDO::PARAM_STR);
$res->bindParam(':opdata', $opdata, PDO::PARAM_STR);
$res->execute();

It’s not working though. What is the correct way to pass table field names as parameters in a mysql query by following the security criteria?

Answer 1

Use preg_match() to define which characters are valid for an identifier (alias name) this defaults an Injection sql attack.

Basically the regular expression says to look for one or more characters that are letters, numbers or underline.

$campo = 'campo_valido234';

if(preg_match('/^\w+$/i', $campo)){
   $sql = sprintf("SELECT nome, setor FROM mytable WHERE %s BETWEEN :datainicial AND :datafinal ORDER BY %s DESC", $campo, $campo);
}