Use field names in a query prepared with PDO

Question

Use field names in a query prepared with PDO

Navigation

#1 by (2 votes)

1

I connect with this php:

class Conexion extends PDO { 
    private $tipo_de_base = 'mysql';
    private $host = 'localhost';
    private $nombre_de_base = 'basededatos';
    private $usuario = 'user';
    private $contrasena = 'user'; 

    public function __construct() {
        //Sobreescribo el método constructor de la clase PDO
        try{

            parent::__construct($this->tipo_de_base.':host='.$this->host.';dbname='.$this->nombre_de_base, $this->usuario, $this->contrasena);

        } catch(PDOException $e){

            echo 'Ha surgido un error y no se puede conectar a la base de datos. Detalle: ' . $e->getMessage();
            exit;

        }
     } 
   }

My index is more or less like this:

<?php
require 'conexion.php';
require 'funcs_pdo.php';
?>

<!DOCTYPE html>
 <html>
 <head>
 <title></title>
 </head>
 <body>
   <br>
   <?php echo getValor('usuario', 'id', '10'); ?>
 </body>
 </html>

fucs_pdo is a php file where I plan to host several functions ... now that function getValor has this:

    function getValor($campo, $campoWhere, $valor) {
    echo $campo . '<br>'; 
    echo $campoWhere . '<br>'; 
    echo $valor . '<br>'; 

    $conexion = new Conexion();
    $stmt = $conexion -> prepare("SELECT :campo FROM usuarios WHERE :campoWhere = :valor LIMIT 1;");
    $stmt->bindParam(':campo', $campo); 
    $stmt->bindParam(':campoWhere', $campoWhere); 
    $stmt->bindParam(':valor', $valor); 
    $stmt->execute();

    $registro = $stmt->fetch(PDO::FETCH_ASSOC);

    $num = $stmt->rowCount();
    echo $num;

    if ($num > 0) {
        echo $registro;
        return $registro['nombre'];
    } else {
        echo 'NO FUNCIONA';
        return null;
    }
   }

The subject of the connection is not the problem. The problem is in the function because it returns nothing to me and the record exists. What I notice is that, just passing the value WHERE works, that is, there is something in the SELECT that does not work., I do not know if :campo and the :campoWhere take them as I am defining them. Any suggestions?

PROPOSED SOLUTION:

function getValor($campo, $campoWhere, $valor) {
    $campoOrig = $campo;
    $campoWhereOrig = $campoWhere;
    $campo = "'".str_replace("'","''",$campo)."'";
    $campoWhere = "'".str_replace("'","''",$campoWhere)."'";

    $conexion = new Conexion();
    $stmt = $conexion -> prepare("SELECT $campo FROM usuarios WHERE $campoWhere = :valor LIMIT 1;");
    $stmt->bindParam(':valor', $valor); 
    $stmt->execute();

    $registro = $stmt->fetch();

    $num = $stmt->rowCount();
    echo $num;

    if ($num > 0) {
        return $registro[$campoOrig];
    } else {
        echo 'NO FUNCIONA';
        return null;
    }
   }

mysql php pdo

asked by MNibor 20.07.2017 в 19:42

source

1 answer

Copy of pointers Matrix As a redirection to my website through my enabled ports?

score 2 · Accepted Answer

What happens is that the class PDO does not accept placeholders (placeholders, or the famous "?" or ": variable") for the identifiers (names of fields or tables) and must be done manually.

To format an identifier that can be included in the query, there are two tips to follow

Include the identifier in inverted quotes

Duplicate inverted quotes to escape them

An example, applied to your problem could be the following:

$campoWhere = "'".str_replace("'","''",$campoWhere)."'";

As you can see, we include inverted quotes and duplicate them. In this way we can directly include the variable $campoWhere in the query, something like this:

"SELECT :campo FROM usuarios WHERE $campoWhere = :valor LIMIT 1;"

For more information on why the PDO class does not have placeholders for identifiers you can see this link or the official documentation .