CODEWITHSUNDEEP
phpsqlstringpdo
cameronjonesweb
cameronjonesweb

Reputation: 2506

SQL field being printed as a string

I have a search form to get some records. One of the restricting field for the form is record, being a dropdown box that looks like this:

<select name="record" id="record">
<option value="1">Highest Score</option>
<option value="2">Most runs</option>
</select>

Then when they search the following code runs:

if (isset($_GET['action']) and $_GET['action'] == 'search')
{
  include $_SERVER['DOCUMENT_ROOT'] . '/stats/includes/db.inc.php';

  $placeholders = array();

  if($_GET['record'] == '1'){
      $placeholders[':record'] = 'runs';
  } else if($_GET['record'] == '2'){
      $placeholders[':record'] = 'SUM(runs)';
  }

  $select = 'SELECT playerid, :record as record, user.usertitle';
  $from   = ' FROM cricket_performance p INNER JOIN user ON p.playerid = user.userid';
  $where  = ' WHERE TRUE';

  if ($_GET['team'] != '')
  {
    $where .= " AND team = :team";
    $placeholders[':team'] = $_GET['team'];
  }

  if ($_GET['record'] != '')
  {
    $where .= " ORDER BY :record DESC";
  }

  $where .= " LIMIT 10";

  try
  {
    $sql = $select . $from . $where;
    $s = $pdo->prepare($sql);
    $s->execute($placeholders);
  }
  catch (PDOException $e)
  {
    $error = 'Error fetching record';
    include 'form.html.php';
    exit();
  }

    foreach ($s as $row)
    {
    $records[] = array('playerid' => $row['playerid'], 'record' => $row['record'], 'usertitle' => $row['usertitle'], '1' => $row['1']);
    }
    include 'form.html.php';
    exit();
}

And that works perfectly fine, except for one thing. This: $placeholders[':record'] = 'runs'; is quite literally being printed in the SQL as 'runs', instead of the runs field being picked from the database, so $record['record'] will be printed as 'runs' for every entry, instead of the number being picked out of the table.

if the quotations are replaced by "" the same thing occurs, and if replaced by `` nothing happens (empty result)

Upvotes: 5

Views: 95

Answers (2)

SteAp
SteAp

Reputation: 11999

PDO expects bound parameters to be values in e.g. WHERE clauses. Therefore 

$s = $pdo->prepare($sql);
$s->execute($placeholders);

won't work as expected. PDO creates from 

SELECT playerid, :record as record, user.usertitle

something like

SELECT playerid, 'runs' as record, user.usertitle

and tries to execute.

Upvotes: 1

ironcito
ironcito

Reputation: 827

You shouldn't use placeholders for table or field names. Use a variable instead, the value doesn't need to be sanitized anyway.

"SELECT playerid, ".$field." as record, user.usertitle"

Upvotes: 1

Related Questions