Bug hunting with PHP and databases
ActivePublic
Actions

Authored by stwalkerster on Feb 8 2017, 1:04 AM.

Tags

Referenced Files

	F9025:
	Feb 8 2017, 1:46 PM

	F8992:
	Feb 8 2017, 1:16 AM

	F8991:
	Feb 8 2017, 1:04 AM

Subscribers

None

	<?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 paste.Feb 8 2017, 1:04 AM

stwalkerster created this object with visibility "Public (No Login Required)".

stwalkerster edited the content of this paste. (Show Details)Feb 8 2017, 1:16 AM

stwalkerster mentioned this in T593: Fix the hackiness in SearchHelperBase surrounding fetchByParameter / limit interactions.Feb 8 2017, 1:44 PM

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.

Bug hunting with PHP and databasesActivePublicActions

Event Timeline

Bug hunting with PHP and databases
ActivePublic
Actions