Project

General

Profile

Feature #23374 » 15457_core.diff

Administrator Admin, 2010-08-14 22:04

View differences:

t3lib/core_autoload.php (working copy)
't3lib_compressor' => PATH_t3lib . 'class.t3lib_compressor.php',
't3lib_cs' => PATH_t3lib . 'class.t3lib_cs.php',
't3lib_db' => PATH_t3lib . 'class.t3lib_db.php',
't3lib_db_preparedstatement' => PATH_t3lib . 'db/class.t3lib_db_PreparedStatement.php',
't3lib_diff' => PATH_t3lib . 'class.t3lib_diff.php',
't3lib_div' => PATH_t3lib . 'class.t3lib_div.php',
't3lib_exception' => PATH_t3lib . 'class.t3lib_exception.php',
t3lib/db/class.t3lib_db_PreparedStatement.php (revision 0)
<?php
/***************************************************************
* Copyright notice
*
* (c) 2010 Xavier Perseguers <typo3@perseguers.ch>
* All rights reserved
*
* This script is part of the TYPO3 project. The TYPO3 project is
* free software; you can redistribute it and/or modify
* it under the terms of the GNU General Public License as published by
* the Free Software Foundation; either version 2 of the License, or
* (at your option) any later version.
*
* The GNU General Public License can be found at
* http://www.gnu.org/copyleft/gpl.html.
* A copy is found in the textfile GPL.txt and important notices to the license
* from the author is found in LICENSE.txt distributed with these scripts.
*
*
* This script is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU General Public License for more details.
*
* This copyright notice MUST APPEAR in all copies of the script!
***************************************************************/
/**
* TYPO3 prepared statement for t3lib_db class.
*
* USE:
* In all TYPO3 scripts when you need to create a prepared query:
* <code>
* $prepared = $GLOBALS['TYPO3_DB']->prepare_SELECTquery('*', 'pages', 'pid = :pid');
* $prepared->execute(array(':uid' => 2));
* while (($row = $prepared->fetch()) !== FALSE) {
* // ...
* }
* $prepared->free();
* </code>
*
* @author Xavier Perseguers <typo3@perseguers.ch>
* @package TYPO3
* @subpackage t3lib
*/
class t3lib_db_PreparedStatement {
/**
* Represents the SQL NULL data type.
* @var integer
*/
const PARAM_NULL = 0;
/**
* Represents the SQL INTEGER data type.
* @var integer
*/
const PARAM_INT = 1;
/**
* Represents the SQL CHAR, VARCHAR, or other string data type.
* @var integer
*/
const PARAM_STR = 2;
/**
* Represents a boolean data type.
* @var integer
*/
const PARAM_BOOL = 5;
/**
* Automatically detects underlying type
* @var integer
*/
const PARAM_AUTOTYPE = 10;
/**
* Specifies that the fetch method shall return each row as an array indexed by
* column name as returned in the corresponding result set. If the result set
* contains multiple columns with the same name, t3lib_db_PreparedStatement::FETCH_ASSOC
* returns only a single value per column name.
* @var integer
*/
const FETCH_ASSOC = 2;
/**
* Specifies that the fetch method shall return each row as an array indexed by
* column number as returned in the corresponding result set, starting at column 0.
* @var integer
*/
const FETCH_NUM = 3;
/**
* Query to be executed.
* @var string
*/
protected $query;
/**
* Components of the query to be executed.
* @var array
*/
protected $queryComponents;
/**
* Table (used to call $GLOBALS['TYPO3_DB']->fullQuoteStr().
* @var string
*/
protected $table;
/**
* Binding parameters.
* @var array
*/
protected $parameters;
/**
* MySQL result pointer (of SELECT query) / DBAL object.
* @var pointer
*/
protected $resource;
/**
* Creates a new PreparedStatement. Either $query or $queryComponents
* should be used. Typically $query will be used by native MySQL TYPO3_DB
* on a ready-to-be-executed query. On the other hand, DBAL will have
* parse the query and will be able to safely know where parameters are used
* and will use $queryComponents instead.
*
* @param string $query SQL query to be executed
* @param string FROM table (used to call $GLOBALS['TYPO3_DB']->fullQuoteStr().
* @param array $queryComponents Components of the query to be executed
* @access protected This constructor may only be used by t3lib_DB
*/
public function __construct($query, $from_table, array $queryComponents = array()) {
$this->query = $query;
$this->queryComponents = $queryComponents;
$this->table = $table;
$this->parameters = array();
$this->resource = NULL;
}
/**
* Binds a PHP variable to a corresponding named or question mark placeholder in the SQL
* statement that was use to prepare the statement. Unlike
* {@see t3lib_db_PreparedStatement::bindValue()}, the variable is bound as a reference
* and will only be evaluated at the time that {@see t3lib_db_PreparedStatement::execute()}
* is called.
*
* @param mixed $parameter Parameter identifier. For a prepared statement using named placeholders, this will be a parameter name of the form :name. For a prepared statement using question mark placeholders, this will be the 1-indexed position of the parameter.
* @param mixed $variable Name of the PHP variable to bind to the SQL statement parameter.
* @param integer $data_type Explicit data type for the parameter using the t3lib_db_PreparedStatement::PARAM_* constants.
* @return boolean Returns TRUE on success or FALSE on failure.
* @api
*/
public function bindParam($parameter, &$variable, $data_type = self::PARAM_AUTOTYPE) {
$key = is_numeric($parameter) ? intval($parameter) - 1 : $parameter;
if (isset($this->parameters[$key])) {
// Parameter is already bound
return FALSE;
}
$this->parameters[$key] = array(
'value' => &$variable,
'type' => $data_type,
);
return TRUE;
}
/**
* Binds a value to a corresponding named or question mark placeholder in the SQL
* statement that was use to prepare the statement.
*
* @param mixed $parameter Parameter identifier. For a prepared statement using named placeholders, this will be a parameter name of the form :name. For a prepared statement using question mark placeholders, this will be the 1-indexed position of the parameter.
* @param mixed $value The value to bind to the parameter.
* @param integer $data_type Explicit data type for the parameter using the t3lib_db_PreparedStatement::PARAM_* constants.
* @return boolean Returns TRUE on success or FALSE on failure.
* @api
*/
public function bindValue($parameter, $value, $data_type = self::PARAM_AUTOTYPE) {
$key = is_numeric($parameter) ? intval($parameter) - 1 : $parameter;
if (isset($this->parameters[$key])) {
// Parameter is already bound
return FALSE;
}
$this->parameters[$key] = array(
'value' => $value,
'type' => ($data_type == self::PARAM_AUTOTYPE ? $this->guessValueType($value) : $data_type),
);
return TRUE;
}
/**
* Execute the prepared statement. If the prepared statement included parameter
* markers, you must either:
* - call {@see t3lib_db_PreparedStatement::bindParam()} to bind PHP variables
* to the parameter markers: bound variables pass their value as input
* - or pass an array of input-only parameter values
*
* @param array $input_parameters An array of values with as many elements as there are bound parameters in the SQL statement being executed. All values are treated as t3lib_db_PreparedStatement::PARAM_AUTOTYPE.
* @return boolean Returns TRUE on success or FALSE on failure.
* @api
*/
public function execute(array $input_parameters = array()) {
$query = $this->query;
$queryComponents = $this->queryComponents;
$parameterValues = $this->parameters;
if (count($input_parameters) > 0) {
$parameterValues = array();
foreach ($input_parameters as $value) {
$parameterValues[] = array(
'value' => $value,
'type' => $this->guessValueType($value),
);
}
}
$this->thawValuesInQuery($query, $queryComponents, $parameterValues);
$this->resource = $GLOBALS['TYPO3_DB']->stmt_execute($query, $queryComponents);
return ($this->resource ? TRUE : FALSE);
}
/**
* Fetches a row from a result set associated with a t3lib_db_PreparedStatement object.
*
* @param integer $fetch_style Controls how the next row will be returned to the caller. This value must be one of the t3lib_db_PreparedStatement::FETCH_* constants.
* @return array Array of rows or FALSE if there are no more rows.
* @api
*/
public function fetch($fetch_style = self::FETCH_ASSOC) {
switch ($fetch_style) {
case self::FETCH_ASSOC:
$row = $GLOBALS['TYPO3_DB']->sql_fetch_assoc($this->resource);
break;
case self::FETCH_NUM:
$row = $GLOBALS['TYPO3_DB']->sql_fetch_row($this->resource);
break;
default:
throw new InvalidArgumentException('$fetch_style must be either FETCH_ASSOC or FETCH_NUM', 1281646455);
}
return $row;
}
/**
* Returns an array containing all of the result set rows.
*
* @param integer $fetch_style Controls the contents of the returned array as documented in {@see t3lib_db_PreparedStatement::fetch()}.
* @return array Array of rows.
* @api
*/
public function fetchAll($fetch_style = self::FETCH_ASSOC) {
$rows = array();
while (($row = $this->fetch($fetch_style)) !== FALSE) {
$rows[] = $row;
}
return $rows;
}
/**
* Releases the cursor.
*
* @return void
* @api
*/
public function free() {
$GLOBALS['TYPO3_DB']->sql_free_result($this->resource);
}
/**
* Guesses the type of a given value.
*
* @param mixed $value
* @return integer One of the t3lib_db_PreparedStatement::PARAM_* constants
*/
protected function guessValueType($value) {
if (is_bool($value)) {
$type = self::PARAM_BOOL;
} elseif (is_numeric($value)) {
$type = self::PARAM_INT;
} elseif (strtoupper($value) === 'NULL') {
$type = self::PARAM_NULL;
} else {
$type = self::PARAM_STR;
}
return $type;
}
/**
* Thaws values for each parameter in a query.
*
* @param string $query
* @param array $queryComponents
* @param array $parameterValues
* @return void
*/
protected function thawValuesInQuery(&$query, array &$queryComponents, array $parameterValues) {
foreach ($this->parameters as $key => $typeValue) {
if ($typeValue['type'] == self::PARAM_AUTOTYPE) {
$typeValue['type'] = $this->guessValueType($typeValue['value']);
}
switch ($typeValue['type']) {
case self::PARAM_INT:
$value = $typeValue['value'];
break;
case self::PARAM_BOOL:
$value = $typeValue['value'] ? 1 : 0;
break;
case self::PARAM_STR:
$value = $GLOBALS['TYPO3_DB']->fullQuoteStr($typeValue['value'], $this->table);
break;
}
if (is_numeric($key)) {
if (isset($queryComponents['parameters'])) {
$queryComponents['parameters']['?'][$key][0] = $value;
} else {
$parts = explode('?', $query, 2);
$parts[0] .= $value;
$query = implode('', $parts);
}
} else {
if (isset($queryComponents['parameters'])) {
$queryComponents['parameters'][$key][0] = $value;
} else {
$query = str_replace($key, $value, $query);
}
}
}
}
}
if (defined('TYPO3_MODE') && $TYPO3_CONF_VARS[TYPO3_MODE]['XCLASS']['t3lib/db/class.t3lib_db_PreparedStatement.php']) {
include_once($TYPO3_CONF_VARS[TYPO3_MODE]['XCLASS']['t3lib/db/class.t3lib_db_PreparedStatement.php']);
}
?>
/**************************************
*
* Prepared Query Support
*
**************************************/
/**
* Creates a SELECT prepared SQL statement.
*
* @param string See exec_SELECTquery()
* @param string See exec_SELECTquery()
* @param string See exec_SELECTquery()
* @param string See exec_SELECTquery()
* @param string See exec_SELECTquery()
* @param string See exec_SELECTquery()
* @param array $input_parameters An array of values with as many elements as there are bound parameters in the SQL statement being executed. All values are treated as t3lib_db_PreparedStatement::PARAM_AUTOTYPE.
* @return t3lib_db_PreparedStatement Prepared statement
*/
public function prepare_SELECTquery($select_fields, $from_table, $where_clause, $groupBy = '', $orderBy = '', $limit = '', array $input_parameters = array()) {
$query = $this->SELECTquery($select_fields, $from_table, $where_clause, $groupBy, $orderBy, $limit);
$stmt = t3lib_div::makeInstance('t3lib_db_PreparedStatement', $query, $from_table, array());
/* @var $stmt t3lib_db_PreparedStatement */
// Bind values to parameters
foreach ($input_parameters as $key => $value) {
$stmt->bindValue($key, $value, t3lib_db_PreparedStatement::PARAM_AUTOTYPE);
}
// Return prepared statement
return $stmt;
}
/**
* Creates a SELECT prepared SQL statement based on input query parts array
*
* @param array Query parts array
* @param array $input_parameters An array of values with as many elements as there are bound parameters in the SQL statement being executed. All values are treated as t3lib_db_PreparedStatement::PARAM_AUTOTYPE.
* @return t3lib_db_PreparedStatement Prepared statement
*/
public function prepare_SELECTqueryArray(array $queryParts, array $input_parameters = array()) {
return $this->prepare_SELECTquery(
$queryParts['SELECT'],
$queryParts['FROM'],
$queryParts['WHERE'],
$queryParts['GROUPBY'],
$queryParts['ORDERBY'],
$queryParts['LIMIT'],
$input_parameters
);
}
/**
* Executes a prepared query.
*
* @param string $query The query to execute
* @param array $queryComponents The components of the query to execute
* @return pointer MySQL result pointer / DBAL object
* @access protected This method may only be called by t3lib_db_PreparedStatement
*/
public function stmt_execute($query, array $queryComponents) {
$res = mysql_query($query, $this->link);
if ($this->debugOutput) {
$this->debug('stmt_execute', $query);
}
return $res;
}
/**************************************
*
* Various helper functions
t3lib/class.t3lib_sqlparser.php (working copy)
* Parsing SELECT query
*
* @param string SQL string with SELECT query to parse
* @param array Array holding references to either named (:name) or question mark (?) parameters found
* @return mixed Returns array with components of SELECT query on success, otherwise an error message string.
* @see compileSELECT()
*/
protected function parseSELECT($parseString) {
protected function parseSELECT($parseString, &$parameterReferences = NULL) {
// Removing SELECT:
$parseString = $this->trimSQL($parseString);
......
// Init output variable:
$result = array();
if ($parameterReferences == NULL) {
$result['parameters'] = array();
$parameterReferences = &$result['parameters'];
}
$result['type'] = 'SELECT';
// Looking for STRAIGHT_JOIN keyword:
......
if ($parseString) {
// Get WHERE clause:
$result['WHERE'] = $this->parseWhereClause($parseString, '^(GROUP[[:space:]]+BY|ORDER[[:space:]]+BY|LIMIT)[[:space:]]+');
$result['WHERE'] = $this->parseWhereClause($parseString, '^(GROUP[[:space:]]+BY|ORDER[[:space:]]+BY|LIMIT)[[:space:]]+', $parameterReferences);
if ($this->parse_error) { return $this->parse_error; }
// If the WHERE clause parsing was stopped by GROUP BY, ORDER BY or LIMIT, then proceed with parsing:
......
*
* @param string WHERE clause to parse. NOTICE: passed by reference!
* @param string Regular expressing to STOP parsing, eg. '^(GROUP BY|ORDER BY|LIMIT)([[:space:]]*)'
* @param array Array holding references to either named (:name) or question mark (?) parameters found
* @return mixed If successful parsing, returns an array, otherwise an error string.
*/
public function parseWhereClause(&$parseString, $stopRegex = '') {
public function parseWhereClause(&$parseString, $stopRegex = '', array &$parameterReferences = array()) {
// Prepare variables:
$parseString = $this->trimSQL($parseString);
......
if (preg_match('/^EXISTS[[:space:]]*[(]/i', $parseString)) {
$stack[$level][$pnt[$level]]['func']['type'] = $this->nextPart($parseString, '^(EXISTS)[[:space:]]*');
$parseString = trim(substr($parseString, 1)); // Strip of "("
$stack[$level][$pnt[$level]]['func']['subquery'] = $this->parseSELECT($parseString);
$stack[$level][$pnt[$level]]['func']['subquery'] = $this->parseSELECT($parseString, $parameterReferences);
// Seek to new position in parseString after parsing of the subquery
$parseString = $stack[$level][$pnt[$level]]['func']['subquery']['parseString'];
unset($stack[$level][$pnt[$level]]['func']['subquery']['parseString']);
......
$stack[$level][$pnt[$level]]['value'] = $values;
} else if (t3lib_div::inList('IN,NOT IN', $stack[$level][$pnt[$level]]['comparator']) && preg_match('/^[(][[:space:]]*SELECT[[:space:]]+/', $parseString)) {
$this->nextPart($parseString, '^([(])');
$stack[$level][$pnt[$level]]['subquery'] = $this->parseSELECT($parseString);
$stack[$level][$pnt[$level]]['subquery'] = $this->parseSELECT($parseString, $parameterReferences);
// Seek to new position in parseString after parsing of the subquery
$parseString = $stack[$level][$pnt[$level]]['subquery']['parseString'];
unset($stack[$level][$pnt[$level]]['subquery']['parseString']);
......
$stack[$level][$pnt[$level]]['values'][1] = $this->getValue($parseString);
} else {
// Finding value for comparator:
$stack[$level][$pnt[$level]]['value'] = $this->getValue($parseString, $stack[$level][$pnt[$level]]['comparator']);
$stack[$level][$pnt[$level]]['value'] = &$this->getValueOrParameter($parseString, $stack[$level][$pnt[$level]]['comparator'], '', $parameterReferences);
if ($this->parse_error) {
return $this->parse_error;
}
......
}
/**
* Finds value or either named (:name) or question mark (?) parameter markers at the beginning
* of $parseString, returns result and strips it of parseString.
*
* @param string $parseString The parseString
* @param string $comparator The comparator used before.
* @param string $mode The mode, e.g., "INDEX"
* @param mixed The value (string/integer) or parameter (:name/?). Otherwise an array with error message in first key (0)
*/
protected function &getValueOrParameter(&$parseString, $comparator = '', $mode = '', array &$parameterReferences = array()) {
$parameter = $this->nextPart($parseString, '^(\\:[[:alnum:]_]+|\\?)');
if ($parameter === '?') {
if (!isset($parameterReferences['?'])) {
$parameterReferences['?'] = array();
}
$value = array('?');
$parameterReferences['?'][] = &$value;
} elseif ($parameter !== '') { // named parameter
if (isset($parameterReferences[$parameter])) {
// Use the same reference as last time we encountered this parameter
$value = &$parameterReferences[$parameter];
} else {
$value = array($parameter);
$parameterReferences[$parameter] = &$value;
}
} else {
$value = $this->getValue($parseString, $comparator, $mode);
}
return $value;
}
/**
* Finds value in beginning of $parseString, returns result and strips it of parseString
*
* @param string The parseString, eg. "(0,1,2,3) ..." or "('asdf','qwer') ..." or "1234 ..." or "'My string value here' ..."
(2-2/5)