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

Question

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

Navigation

#1 by (2 votes)

2

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

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

$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();

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

mysql pdo

asked by anonymous 14.11.2018 / 02:44

1 answer

How to get total sales per month with mysql Save input data without submitting

score 2 · Accepted Answer

Use preg_match() to define which characters are valid for an identifier (common name). This will prevent an attack from sql injection.

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);
}