Php PDO bind both terms of a comparison

Question

I'm trying to bind two parameters received with AJAX into a prepared statement, using both of them as terms for a comparison. When I try to use both of them the query goes wrong, while if I remove the one obtained by a select it works. Is the select a possible reason of the failure? Because in the page in which I perform the query, the string arrives correctly, with its correct value.

$.ajax({
        url:"ricerca.php",
        type: "GET",
        data: { azione:"ricerca",
                tcr1:$("#testoPrimoCampo").val(),
                cr1:$("#primoCampoRicerca").val(),
                tcr2:$("#testoSecondoCampo").val(),
                cr2:$("#secondoCampoRicerca").val(),
                ordinamento: ord},
        success:function(result){
            alert(result);
                $("#risRicerca").html(result);
          },
              error: function(richiesta,stato,errori){
                 $("#divElenco").html("<strong>Chiamata fallita:</strong>"+stato+" "+errori);
          }
      });


<form action="ricerca.php" method="GET">
<fieldset>
<legend>Ricerca calciatori</legend>
    <select id="primoCampoRicerca" name="primoCampoRicerca">
      <option value="codicecalciatore">Codice</option>
      <option value="cognomenome">Cognome e nome</option>
      <option value="squadra">Squadra</option>
      <option value="ruolo">Tipo</option>
      <option value="valore">Valore</option>
    </select>   
</fieldset>
</form>

<div id = "risRicerca"></div>

ricerca.php

<?php
/*** mysql hostname ***/
$hostname = 'localhost';
/*** mysql username ***/
$username = 'root';
/*** mysql password ***/
$password = '';
/*** database name ***/
$database_name = 'esempio';
try {
    $dbh = new PDO("mysql:host=$hostname;dbname=$database_name", $username, $password);
    }
catch(PDOException $e)
    {
    echo $e->getMessage();
    }

if (isset($_GET['azione'])) {
    if (!empty($_GET['tcr1']) && $_GET['tcr1']!='') {
        try {

            echo($_GET["cr1"]);
            $sql = $dbh->prepare("SELECT * FROM calciatori WHERE ? = ?");

            $sql->bindParam(1, $_GET['cr1']);
            $sql->bindParam(2, $_GET['tcr1']);
            $sql->execute();



            echo '<table> <tr> <td>Codice</td> <td>Cognome e Nome</td>     <td>Squadra</td> <td>Ruolo / Tipo</td> <td>Valore</td> </tr>';
            $i = 0;

            $res = $sql->fetchAll();

            foreach ($res as $row){
                $i=$i+1;
                echo '<tr>';
                echo '<td>' . $row['codicecalciatore'] . '</td>';
                echo '<td>' . $row['cognomenome'] . '</td>';
                echo '<td>' . $row['squadra'] . '</td>';
                echo '<td>' . $row['ruolo'] . '</td>';
                echo '<td>' . $row['valore'] . '</td>';
                echo '</tr>';
            }

            echo '</table>';
      echo("$i");
            /*** close the database connection
            $dbh = null;***/
        }
        catch(PDOException $e)
            {
            echo $e->getMessage();
            }
    } else {
    echo("Campi vuoti");
        $_SESSION['istruzione'] = '';
    }
} else {
  echo("Azione non settata");
    $_SESSION['istruzione'] = '';
}
?>

Sorry if varialbes' names are in italian, I hope it won't be a problem, thank's.

You need to post the code so we can see what you might be doing wrong. — Barmar, Mar 07 '16 at 18:15
*no lo so mi amico*. no code, no help. It's all guesswork from hereon in. — Funk Forty Niner, Mar 07 '16 at 18:20
If one of the parameter is a column you cant bind column and table names.If it\`s not a column do that equality check outside the query.Also where do you set `$giocatore` in ricerca.php? — Mihai, Mar 07 '16 at 18:30
*la risposta è semplice/facile:* you can't do this `WHERE ?` as in binding a column. — Funk Forty Niner, Mar 07 '16 at 18:31
@Mihai Sorry, I made a mistake while copying part of the code, I've just post the correct version. — Alessandro Delmastro, Mar 07 '16 at 19:17

low_rents · Accepted Answer · 2016-03-07T18:37:54.897

0

parameters can only be used to bind values, not names!

SELECT * FROM calciatori WHERE ? = ?

comparing two values works, but I think you want the first parameter to be a field name. latter doesn't work and parameters should not be used like that.

you can use php variables instead and make sure to be save from SQL injection by using a whitelist:

$whitelist = array("codicecalciatore" => true, "cognomenome" => true, ...);

if($whitelist[$_GET['cr1']])
{
    $sql = "SELECT * FROM calciatori WHERE " . $_GET['cr1'] . " = ?";
}
else
{
    die("ERROR: unknown fieldname!");
}

edited Mar 07 '16 at 18:37

answered Mar 07 '16 at 18:31

low_rents

4,481
3
27
55

Thank's, but I can't undertand why I can use them to set the table name and not for a field. – Alessandro Delmastro Mar 07 '16 at 19:14
you can't and shouldn't use them for table-names either. unless you want to really "set" the table name, because then it is a string-value. – low_rents Mar 07 '16 at 19:20

Php PDO bind both terms of a comparison

1 Answers1