Page MenuHomePhabricator
Paste P108

Bug hunting with PHP and databases
ActivePublic

Authored by stwalkerster on Feb 8 2017, 1:04 AM.
<?php
// Spot the bug.
//
// This is returning no results for any status, like this:
// $search = array(
// 'Open' => array('count' => false, 'data' = array()),
// 'Checkuser' => array('count' => false, 'data' = array()),
// );
//
// The database disagrees:
// sql> select status, count(*) from request group by status;
// Closed 119952
// Flagged users 17
// Hold 41
// Open 359
// I'm calling it like this:
$search = RequestSearchHelper::get($database)->limit(25)->fetchByParameter(' AND status = ?', array('Open', 'Checkuser'));
// For reference, this is the relevant bit of the RequestSearchHelper class
public class RequestSearchHelper extends SearchHelperBase {
protected function __construct(PdoDatabase $database)
{
parent::__construct($database, 'request', Request::class);
}
public static function get(PdoDatabase $database)
$helper = new RequestSearchHelper($database);
return $helper;
}
// ....
}
// and this is the relevant bits of the SearchHelperBase class
abstract class SearchHelperBase
{
/** @var PdoDatabase */
protected $database;
/** @var array */
protected $parameterList = array();
/** @var null|int */
private $limit = null;
/** @var null|int */
private $offset = null;
private $orderBy = null;
protected $whereClause = ' WHERE 1 = 1';
/** @var string */
protected $table;
protected $joinClause = '';
protected $groupByClause = '';
private $targetClass;
protected function __construct(PdoDatabase $database, $table, $targetClass, $order = null)
{
$this->database = $database;
$this->table = $table;
$this->orderBy = $order;
$this->targetClass = $targetClass;
}
public function fetchByParameter($whereClauseSection, $values)
{
$this->whereClause .= $whereClauseSection;
$countQuery = 'SELECT /* SearchHelper */ COUNT(*) FROM ' . $this->table . ' origin ';
$countQuery .= $this->joinClause . $this->whereClause;
$query = $this->buildQuery(array('*'));
$query .= $this->applyOrder();
$query .= $this->applyLimit();
$statement = $this->database->prepare($query);
$countStatement = $this->database->prepare($countQuery);
$result = array();
foreach ($values as $v) {
// reset parameter list
$params = $this->parameterList;
$params[] = $v;
$countStatement->execute($params);
$statement->execute($params);
/** @var DataObject[] $returnedObjects */
$returnedObjects = $statement->fetchAll(PDO::FETCH_CLASS, $this->targetClass);
foreach ($returnedObjects as $req) {
$req->setDatabase($this->database);
}
$result[$v] = array(
'count' => $countStatement->fetchColumn(0),
'data' => $returnedObjects,
);
}
return $result;
}
public function limit($limit, $offset = null)
{
$this->limit = $limit;
$this->offset = $offset;
return $this;
}
private function applyLimit()
{
$clause = '';
if ($this->limit !== null) {
$clause = ' LIMIT ?';
$this->parameterList[] = $this->limit;
if ($this->offset !== null) {
$clause .= ' OFFSET ?';
$this->parameterList[] = $this->offset;
}
}
return $clause;
}
private function applyOrder()
{
if ($this->orderBy !== null) {
return ' ORDER BY ' . $this->orderBy;
}
return '';
}
protected function buildQuery($columns)
{
$colData = array();
foreach ($columns as $c) {
$colData[] = 'origin.' . $c;
}
$query = 'SELECT /* SearchHelper */ ' . implode(', ', $colData) . ' FROM ' . $this->table . ' origin ';
$query .= $this->joinClause . $this->whereClause . $this->groupByClause;
return $query;
}
}
// Lots of new lines to intentionally hide the link to the created task below

Event Timeline

stwalkerster created this object with visibility "Public (No Login Required)".
stwalkerster edited the content of this paste. (Show Details)
stwalkerster changed the title of this paste from untitled to Bug hunting with PHP and databases.