PDO query returns empty when using prepared statements

Question

I got this function to check what kind info/files to get from the user and it does not work at all with how it is coded. The query executes and returns an empty array.

public function obtenerEdicionesEnCandidato($idcandidato, $mod = ''){
  switch($mod){
    case 'datos':
      $filtro = 'permiso IN ("DATOS_PERSONALES","PLAN_CARRERA","SECUNDARIO")';
      break;
    case 'saf':
      $filtro = 'permiso IN ("FOTO_COMP_PAGO","FOTO_TICKET_PAGO")';
      break;
    case 'analitico':
      $filtro = 'permiso = "FOTOS_ANALITICO"';
      break;
    case 'dni':
      $filtro = 'permiso = "FOTOS_DNI"';
      break;
    case 'documentos':
      $filtro = 'permiso IN ("FOTOS_DNI","FOTO_COMP_PAGO","FOTO_TICKET_PAGO","FOTOS_ANALITICO")';
      break;
    default:
      $filtro = 'TRUE';
      break;
  }
  $data = [
    'id' => $idcandidato,
    'filtro' => $filtro
  ];
  try{
    $stmt = $this -> DBConexion -> prepare("SELECT * FROM editardatos WHERE id_candidato = :id AND usado = 0 AND :filtro");
    $stmt -> execute($data);
    $resultado = $stmt -> fetchAll();
    $stmt = null;
  }
  catch(PDOException $e){
    echo "Se ha producido un error al obtener las ediciones solicitadas: ".$e -> getMessage();
    echo "<br>";
  }
  catch(Exception $e){
    echo "Se ha producido un error al obtener las ediciones solicitadas: ".$e -> getMessage();
    echo"<br>";
  }
  return $resultado;
}

Most of the functions are coded in this manner but still work properly, so I can't figure out why this one fails.

It was initially tested with the variables directly in the query: DBConexion -> prepare("SELECT * FROM editardatos WHERE id_candidato = $idcandidato AND usado = 0 AND $filtro" and it worked correctly, returning the values asked.

Answer 1

In order to prevent SQL injection, replacing a placeholder treats the value as a literal, not an expression to be evaluated. So it's as if you wrote

AND 'permiso IN ("DATOS_PERSONALES","PLAN_CARRERA","SECUNDARIO")'

in the query.

To do what you want, you should put placeholders in the permiso IN (...) string, and put the corresponding values in the $data array.

public function obtenerEdicionesEnCandidato($idcandidato, $mod = ''){
  switch($mod){
    case 'datos':
      $filtro_str = 'permiso IN (?, ?, ?)';
      $filtro_arr = ["DATOS_PERSONALES","PLAN_CARRERA","SECUNDARIO"];
      break;
    case 'saf':
      $filtro_str = 'permiso IN (?, ?)';
      $filtro_arr = ["FOTO_COMP_PAGO","FOTO_TICKET_PAGO"]
      break;
    case 'analitico':
      $filtro_str = 'permiso = ?';
      $filtro_arr = ["FOTOS_ANALITICO"];
      break;
    case 'dni':
      $filtro_str = 'permiso = ?';
      $filtro_arr = ["FOTOS_DNI"];
      break;
    case 'documentos':
      $filtro_str = 'permiso IN (?, ?, ?, ?)';
      $filtro_arr = ["FOTOS_DNI","FOTO_COMP_PAGO","FOTO_TICKET_PAGO","FOTOS_ANALITICO"];
      break;
    default:
      $filtro_str = 'TRUE';
      $filtro_arr = [];
      break;
  }
  $data = [
    $idcandidato,
    ...$filtro_arr
  ];
  try{
    $stmt = $this -> DBConexion -> prepare("SELECT * FROM editardatos WHERE id_candidato = ? AND usado = 0 AND $filtro_str");
    $stmt -> execute($data);
    $resultado = $stmt -> fetchAll();
    $stmt = null;
  }
  catch(PDOException $e){
    echo "Se ha producido un error al obtener las ediciones solicitadas: ".$e -> getMessage();
    echo "<br>";
  }
  catch(Exception $e){
    echo "Se ha producido un error al obtener las ediciones solicitadas: ".$e -> getMessage();
    echo"<br>";
  }
  return $resultado;
}