SQL字段作为字符串打印

Question

我有一个搜索表单用于获取一些记录。表单中的限制字段之一是record，它是一个下拉框，长这样：

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

然后当他们搜索时，以下代码将运行：

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

这可以很好地使用，但有一个问题。这行代码：$placeholders[':record'] = 'runs'; 实际上会被打印在 SQL 中，而不是从数据库中选取 runs 字段，因此对于每个条目，$record['record'] 都会被打印为“runs”，而不是从表中取出的数字。

如果引号被替换为 ""，将发生同样的事情，如果替换为``，则什么也不会发生（结果为空）。

- cameronjonesweb

Answer 1

您不应该使用占位符来代替表或字段名称。相反，应该使用变量，因为这个值不需要经过净化处理。

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

- ironcito

Answer 2

PDO希望绑定参数作为值在例如WHERE子句中。因此，

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

无法按预期工作。PDO是从创建的

。

SELECT playerid, :record as record, user.usertitle

something like

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

并尝试执行。

- SteAp