Feature #23374 » 15457_core.diff
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' ..."
|