/** * @brief Prepare a SQL query * @param string $query Query string * @param int $limit * @param int $offset * @return MDB2_Statement_Common prepared SQL query * * SQL query via MDB2 prepare(), needs to be execute()'d! */ public static function prepare($query, $limit = null, $offset = null) { if (!is_null($limit) && $limit != -1) { if (self::$backend == self::BACKEND_MDB2) { //MDB2 uses or emulates limits & offset internally self::$MDB2->setLimit($limit, $offset); } else { //PDO does not handle limit and offset. //FIXME: check limit notation for other dbs //the following sql thus might needs to take into account db ways of representing it //(oracle has no LIMIT / OFFSET) $limit = (int) $limit; $limitsql = ' LIMIT ' . $limit; if (!is_null($offset)) { $offset = (int) $offset; $limitsql .= ' OFFSET ' . $offset; } //insert limitsql if (substr($query, -1) == ';') { //if query ends with ; $query = substr($query, 0, -1) . $limitsql . ';'; } else { $query .= $limitsql; } } } // Optimize the query $query = self::processQuery($query); self::connect(); // return the result if (self::$backend == self::BACKEND_MDB2) { $result = self::$connection->prepare($query); // Die if we have an error (error means: bad query, not 0 results!) if (PEAR::isError($result)) { $entry = 'DB Error: "' . $result->getMessage() . '"<br />'; $entry .= 'Offending command was: ' . htmlentities($query) . '<br />'; OC_Log::write('core', $entry, OC_Log::FATAL); error_log('DB error: ' . $entry); OC_Template::printErrorPage($entry); } } else { try { $result = self::$connection->prepare($query); } catch (PDOException $e) { $entry = 'DB Error: "' . $e->getMessage() . '"<br />'; $entry .= 'Offending command was: ' . htmlentities($query) . '<br />'; OC_Log::write('core', $entry, OC_Log::FATAL); error_log('DB error: ' . $entry); OC_Template::printErrorPage($entry); } $result = new PDOStatementWrapper($result); } return $result; }
/** * Private method that will use MDB2_Driver_Common::query() for simple and * MDB2_Driver_Common::prepare() & MDB2_Statement_Common::execute() for complex * query specifications. * * @param mixed $sql A string or an array. * @param string $configPath The config path used for exception messages. * * @return MDB2_Result * @throws XML_Query2XML_DBException If a database related error occures. */ private function _prepareAndExecute($sql, $configPath) { $preparedQuery = $sql['query']; if (isset($sql['limit'])) { $preparedQuery .= '; LIMIT:' . $sql['limit']; $preparedQuery .= '; OFFSET:' . $sql['offset']; $this->_db->setLimit($sql['limit'], $sql['offset']); } if (isset($this->_preparedQueries[$preparedQuery])) { $queryHandle = $this->_preparedQueries[$preparedQuery]; } else { // PREPARE $queryHandle = $this->_db->prepare($sql['query']); if (PEAR::isError($queryHandle)) { /* * unit tests: (only if mysql or pgsql is used) * MDB2/_prepareAndExecute/throwDBException_complexQuery.phpt */ throw new XML_Query2XML_DBException($configPath . ': Could not prepare the following SQL query: ' . $sql['query'] . '; ' . $queryHandle->toString()); } $this->_preparedQueries[$preparedQuery] =& $queryHandle; } // EXECUTE if (isset($sql['data'])) { $result = $queryHandle->execute($sql['data']); } else { $result = $queryHandle->execute(); } if (PEAR::isError($result)) { /* * unit tests: * if sqlite is used: MDB2/_prepareAndExecute/ * throwDBException_complexQuery.phpt * if sqlite or mysql is sued: MDB2/getXML/ * throwDBException_nullResultSet_complexQuery_multipleRecords.phpt * throwDBException_nullResultSet_complexQuery_singleRecord.phpt */ throw new XML_Query2XML_DBException($configPath . ': Could not execute the following SQL query: ' . $sql['query'] . '; ' . $result->toString()); } return $result; }
/** * Prepares a query for multiple execution with execute(). * With some database backends, this is emulated. * prepare() requires a generic query as string like * 'INSERT INTO numbers VALUES(?,?)' or * 'INSERT INTO numbers VALUES(:foo,:bar)'. * The ? and :[a-zA-Z] and are placeholders which can be set using * bindParam() and the query can be send off using the execute() method. * * @param string $query the query to prepare * @param mixed $types array that contains the types of the placeholders * @param mixed $result_types array that contains the types of the columns in * the result set or MDB2_PREPARE_RESULT, if set to * MDB2_PREPARE_MANIP the query is handled as a manipulation query * @param mixed $lobs key (field) value (parameter) pair for all lob placeholders * @return mixed resource handle for the prepared query on success, a MDB2 * error on failure * @access public * @see bindParam, execute */ function &prepare($query, $types = null, $result_types = null, $lobs = array()) { if ($this->options['emulate_prepared'] || $this->supported['prepared_statements'] !== true) { $obj =& parent::prepare($query, $types, $result_types, $lobs); return $obj; } $is_manip = $result_types === MDB2_PREPARE_MANIP; $offset = $this->offset; $limit = $this->limit; $this->offset = $this->limit = 0; $query = $this->_modifyQuery($query, $is_manip, $limit, $offset); $result = $this->debug($query, __FUNCTION__, array('is_manip' => $is_manip, 'when' => 'pre')); if ($result) { if (PEAR::isError($result)) { return $result; } $query = $result; } $placeholder_type_guess = $placeholder_type = null; $question = '?'; $colon = ':'; $positions = array(); $position = 0; while ($position < strlen($query)) { $q_position = strpos($query, $question, $position); $c_position = strpos($query, $colon, $position); if ($q_position && $c_position) { $p_position = min($q_position, $c_position); } elseif ($q_position) { $p_position = $q_position; } elseif ($c_position) { $p_position = $c_position; } else { break; } if (is_null($placeholder_type)) { $placeholder_type_guess = $query[$p_position]; } $new_pos = $this->_skipDelimitedStrings($query, $position, $p_position); if (PEAR::isError($new_pos)) { return $new_pos; } if ($new_pos != $position) { $position = $new_pos; continue; //evaluate again starting from the new position } if ($query[$position] == $placeholder_type_guess) { if (is_null($placeholder_type)) { $placeholder_type = $query[$p_position]; $question = $colon = $placeholder_type; } if ($placeholder_type == ':') { //make sure this is not part of an user defined variable $new_pos = $this->_skipUserDefinedVariable($query, $position); if ($new_pos != $position) { $position = $new_pos; continue; //evaluate again starting from the new position } $parameter = preg_replace('/^.{' . ($position + 1) . '}([a-z0-9_]+).*$/si', '\\1', $query); if ($parameter === '') { $err =& $this->raiseError(MDB2_ERROR_SYNTAX, null, null, 'named parameter with an empty name', __FUNCTION__); return $err; } $positions[$p_position] = $parameter; $query = substr_replace($query, '?', $position, strlen($parameter) + 1); } else { $positions[$p_position] = count($positions); } $position = $p_position + 1; } else { $position = $p_position; } } $connection = $this->getConnection(); if (PEAR::isError($connection)) { return $connection; } $statement_name = sprintf($this->options['statement_format'], $this->phptype, md5(time() + rand())); $query = "PREPARE {$statement_name} FROM " . $this->quote($query, 'text'); $statement =& $this->_doQuery($query, true, $connection); if (PEAR::isError($statement)) { return $statement; } $class_name = 'MDB2_Statement_' . $this->phptype; $obj =& new $class_name($this, $statement_name, $positions, $query, $types, $result_types, $is_manip, $limit, $offset); $this->debug($query, __FUNCTION__, array('is_manip' => $is_manip, 'when' => 'post', 'result' => $obj)); return $obj; }
/** * Prepares a query for multiple execution with execute(). * With some database backends, this is emulated. * prepare() requires a generic query as string like * 'INSERT INTO numbers VALUES(?,?)' or * 'INSERT INTO numbers VALUES(:foo,:bar)'. * The ? and :[a-zA-Z] and are placeholders which can be set using * bindParam() and the query can be send off using the execute() method. * * @param string $query the query to prepare * @param mixed $types array that contains the types of the placeholders * @param mixed $result_types array that contains the types of the columns in * the result set or MDB2_PREPARE_RESULT, if set to * MDB2_PREPARE_MANIP the query is handled as a manipulation query * @param mixed $lobs key (field) value (parameter) pair for all lob placeholders * @return mixed resource handle for the prepared query on success, a MDB2 * error on failure * @access public * @see bindParam, execute */ function &prepare($query, $types = null, $result_types = null, $lobs = array()) { if ($this->options['emulate_prepared'] || $this->supported['prepared_statements'] !== true) { $obj =& parent::prepare($query, $types, $result_types, $lobs); return $obj; } $is_manip = $result_types === MDB2_PREPARE_MANIP; $offset = $this->offset; $limit = $this->limit; $this->offset = $this->limit = 0; $query = $this->_modifyQuery($query, $is_manip, $limit, $offset); $result = $this->debug($query, __FUNCTION__, array('is_manip' => $is_manip, 'when' => 'pre')); if ($result) { if (PEAR::isError($result)) { return $result; } $query = $result; } $placeholder_type_guess = $placeholder_type = null; $question = '?'; $colon = ':'; $positions = array(); $position = 0; while ($position < strlen($query)) { $q_position = strpos($query, $question, $position); $c_position = strpos($query, $colon, $position); if ($q_position && $c_position) { $p_position = min($q_position, $c_position); } elseif ($q_position) { $p_position = $q_position; } elseif ($c_position) { $p_position = $c_position; } else { break; } if (is_null($placeholder_type)) { $placeholder_type_guess = $query[$p_position]; } if (is_int($quote = strpos($query, "'", $position)) && $quote < $p_position) { if (!is_int($end_quote = strpos($query, "'", $quote + 1))) { $err =& $this->raiseError(MDB2_ERROR_SYNTAX, null, null, 'query with an unterminated text string specified', __FUNCTION__); return $err; } switch ($this->escape_quotes) { case '': case "'": $position = $end_quote + 1; break; default: if ($end_quote == $quote + 1) { $position = $end_quote + 1; } else { if ($query[$end_quote - 1] == $this->escape_quotes) { $position = $end_quote; } else { $position = $end_quote + 1; } } break; } } elseif ($query[$position] == $placeholder_type_guess) { if (is_null($placeholder_type)) { $placeholder_type = $query[$p_position]; $question = $colon = $placeholder_type; } if ($placeholder_type == ':') { $parameter = preg_replace('/^.{' . ($position + 1) . '}([a-z0-9_]+).*$/si', '\\1', $query); if ($parameter === '') { $err =& $this->raiseError(MDB2_ERROR_SYNTAX, null, null, 'named parameter with an empty name', __FUNCTION__); return $err; } elseif (isset($positions[$parameter])) { $err =& $this->raiseError(MDB2_ERROR_SYNTAX, null, null, 'named parameter names can only be used once per statement', __FUNCTION__); return $err; } $positions[$parameter] = $p_position; $query = substr_replace($query, '?', $position, strlen($parameter) + 1); } else { $positions[] = $p_position; } $position = $p_position + 1; } else { $position = $p_position; } } $connection = $this->getConnection(); if (PEAR::isError($connection)) { return $connection; } $statement_name = 'MDB2_Statement_' . $this->phptype . '_' . md5(time() + rand()); $query = "PREPARE {$statement_name} FROM " . $this->quote($query, 'text'); $statement =& $this->_doQuery($query, true, $connection); if (PEAR::isError($statement)) { return $statement; } $class_name = 'MDB2_Statement_' . $this->phptype; $obj =& new $class_name($this, $statement_name, $positions, $query, $types, $result_types, $is_manip, $limit, $offset); $this->debug($query, __FUNCTION__, array('is_manip' => $is_manip, 'when' => 'post', 'result' => $obj)); return $obj; }
/** * Prepares a query for multiple execution with execute(). * With some database backends, this is emulated. * prepare() requires a generic query as string like * 'INSERT INTO numbers VALUES(?,?)' or * 'INSERT INTO numbers VALUES(:foo,:bar)'. * The ? and :name and are placeholders which can be set using * bindParam() and the query can be sent off using the execute() method. * The allowed format for :name can be set with the 'bindname_format' option. * * @param string $query the query to prepare * @param mixed $types array that contains the types of the placeholders * @param mixed $result_types array that contains the types of the columns in * the result set or MDB2_PREPARE_RESULT, if set to * MDB2_PREPARE_MANIP the query is handled as a manipulation query * @param mixed $lobs key (field) value (parameter) pair for all lob placeholders * @return mixed resource handle for the prepared query on success, a MDB2 * error on failure * @access public * @see bindParam, execute */ function prepare($query, $types = null, $result_types = null, $lobs = array()) { if ($this->options['emulate_prepared'] || $this->supported['prepared_statements'] !== true) { $obj =& parent::prepare($query, $types, $result_types, $lobs); return $obj; } $this->last_query = $query; $is_manip = $result_types === MDB2_PREPARE_MANIP; $offset = $this->offset; $limit = $this->limit; $this->offset = $this->limit = 0; $query = $this->_modifyQuery($query, $is_manip, $limit, $offset); $result = $this->debug($query, __FUNCTION__, array('is_manip' => $is_manip, 'when' => 'pre')); if ($result) { if (PEAR::isError($result)) { return $result; } $query = $result; } $placeholder_type_guess = $placeholder_type = null; $question = '?'; $colon = ':'; $positions = array(); $position = 0; while ($position < strlen($query)) { $q_position = strpos($query, $question, $position); $c_position = strpos($query, $colon, $position); if ($q_position && $c_position) { $p_position = min($q_position, $c_position); } elseif ($q_position) { $p_position = $q_position; } elseif ($c_position) { $p_position = $c_position; } else { break; } if (is_null($placeholder_type)) { $placeholder_type_guess = $query[$p_position]; } $new_pos = $this->_skipDelimitedStrings($query, $position, $p_position); if (PEAR::isError($new_pos)) { return $new_pos; } if ($new_pos != $position) { $position = $new_pos; continue; //evaluate again starting from the new position } if ($query[$position] == $placeholder_type_guess) { if (is_null($placeholder_type)) { $placeholder_type = $query[$p_position]; $question = $colon = $placeholder_type; } if ($placeholder_type == ':') { $regexp = '/^.{' . ($position + 1) . '}(' . $this->options['bindname_format'] . ').*$/s'; $parameter = preg_replace($regexp, '\\1', $query); if ($parameter === '') { $err =& $this->raiseError(MDB2_ERROR_SYNTAX, null, null, 'named parameter name must match "bindname_format" option', __FUNCTION__); return $err; } $positions[$p_position] = $parameter; $query = substr_replace($query, '?', $position, strlen($parameter) + 1); } else { $positions[$p_position] = count($positions); } $position = $p_position + 1; } else { $position = $p_position; } } $connection = $this->getConnection(); if (PEAR::isError($connection)) { return $connection; } $statement = $this->connection->prepare($query); if (!$statement) { return $this->db->raiseError(MDB2_ERROR_NOT_FOUND, null, null, 'unable to prepare statement: ' . $query); } $class_name = 'MDB2_Statement_' . $this->phptype; $obj = new $class_name($this, $statement, $positions, $query, $types, $result_types, $is_manip, $limit, $offset); $this->debug($query, __FUNCTION__, array('is_manip' => $is_manip, 'when' => 'post', 'result' => $obj)); return $obj; }
/** * Prepares a query for multiple execution with execute(). * With some database backends, this is emulated. * prepare() requires a generic query as string like * 'INSERT INTO numbers VALUES(?,?)' or * 'INSERT INTO numbers VALUES(:foo,:bar)'. * The ? and :name and are placeholders which can be set using * bindParam() and the query can be sent off using the execute() method. * The allowed format for :name can be set with the 'bindname_format' option. * * @param string $query the query to prepare * @param mixed $types array that contains the types of the placeholders * @param mixed $result_types array that contains the types of the columns in * the result set or MDB2_PREPARE_RESULT, if set to * MDB2_PREPARE_MANIP the query is handled as a manipulation query * @param mixed $lobs key (field) value (parameter) pair for all lob placeholders * @return mixed resource handle for the prepared query on success, a MDB2 * error on failure * @access public * @see bindParam, execute */ function prepare($query, $types = null, $result_types = null, $lobs = array()) { if ($this->options['emulate_prepared']) { return parent::prepare($query, $types, $result_types, $lobs); } $is_manip = $result_types === MDB2_PREPARE_MANIP; $offset = $this->offset; $limit = $this->limit; $this->offset = $this->limit = 0; $result = $this->debug($query, __FUNCTION__, array('is_manip' => $is_manip, 'when' => 'pre')); if ($result) { if (PEAR::isError($result)) { return $result; } $query = $result; } $pgtypes = function_exists('pg_prepare') ? false : array(); if ($pgtypes !== false && !empty($types)) { $this->loadModule('Datatype', null, true); } $query = $this->_modifyQuery($query, $is_manip, $limit, $offset); $placeholder_type_guess = $placeholder_type = null; $question = '?'; $colon = ':'; $positions = array(); $position = $parameter = 0; while ($position < strlen($query)) { $q_position = strpos($query, $question, $position); $c_position = strpos($query, $colon, $position); //skip "::type" cast ("select id::varchar(20) from sometable where name=?") $doublecolon_position = strpos($query, '::', $position); if ($doublecolon_position !== false && $doublecolon_position == $c_position) { $c_position = strpos($query, $colon, $position + 2); } if ($q_position && $c_position) { $p_position = min($q_position, $c_position); } elseif ($q_position) { $p_position = $q_position; } elseif ($c_position) { $p_position = $c_position; } else { break; } if (null === $placeholder_type) { $placeholder_type_guess = $query[$p_position]; } $new_pos = $this->_skipDelimitedStrings($query, $position, $p_position); if (PEAR::isError($new_pos)) { return $new_pos; } if ($new_pos != $position) { $position = $new_pos; continue; //evaluate again starting from the new position } if ($query[$position] == $placeholder_type_guess) { if (null === $placeholder_type) { $placeholder_type = $query[$p_position]; $question = $colon = $placeholder_type; if (!empty($types) && is_array($types)) { if ($placeholder_type == ':') { } else { $types = array_values($types); } } } if ($placeholder_type_guess == '?') { $length = 1; $name = $parameter; } else { $regexp = '/^.{' . ($position + 1) . '}(' . $this->options['bindname_format'] . ').*$/s'; $param = preg_replace($regexp, '\\1', $query); if ($param === '') { $err = $this->raiseError(MDB2_ERROR_SYNTAX, null, null, 'named parameter name must match "bindname_format" option', __FUNCTION__); return $err; } $length = strlen($param) + 1; $name = $param; } if ($pgtypes !== false) { if (is_array($types) && array_key_exists($name, $types)) { $pgtypes[] = $this->datatype->mapPrepareDatatype($types[$name]); } elseif (is_array($types) && array_key_exists($parameter, $types)) { $pgtypes[] = $this->datatype->mapPrepareDatatype($types[$parameter]); } else { $pgtypes[] = 'text'; } } if ($key_parameter = array_search($name, $positions)) { $next_parameter = 1; foreach ($positions as $key => $value) { if ($key_parameter == $key) { break; } ++$next_parameter; } } else { ++$parameter; $next_parameter = $parameter; $positions[] = $name; } $query = substr_replace($query, '$' . $parameter, $position, $length); $position = $p_position + strlen($parameter); } else { $position = $p_position; } } $connection = $this->getConnection(); if (PEAR::isError($connection)) { return $connection; } static $prep_statement_counter = 1; $statement_name = sprintf($this->options['statement_format'], $this->phptype, $prep_statement_counter++ . sha1(microtime() + mt_rand())); $statement_name = substr(strtolower($statement_name), 0, $this->options['max_identifiers_length']); if (false === $pgtypes) { $result = @pg_prepare($connection, $statement_name, $query); if (!$result) { $err = $this->raiseError(null, null, null, 'Unable to create prepared statement handle', __FUNCTION__); return $err; } } else { $types_string = ''; if ($pgtypes) { $types_string = ' (' . implode(', ', $pgtypes) . ') '; } $query = 'PREPARE ' . $statement_name . $types_string . ' AS ' . $query; $statement = $this->_doQuery($query, true, $connection); if (PEAR::isError($statement)) { return $statement; } } $class_name = 'MDB2_Statement_' . $this->phptype; $obj = new $class_name($this, $statement_name, $positions, $query, $types, $result_types, $is_manip, $limit, $offset); $this->debug($query, __FUNCTION__, array('is_manip' => $is_manip, 'when' => 'post', 'result' => $obj)); return $obj; }
/** * Prepares a query for multiple execution with execute(). * With some database backends, this is emulated. * prepare() requires a generic query as string like * 'INSERT INTO numbers VALUES(?,?)' or * 'INSERT INTO numbers VALUES(:foo,:bar)'. * The ? and :name and are placeholders which can be set using * bindParam() and the query can be sent off using the execute() method. * The allowed format for :name can be set with the 'bindname_format' option. * * @param string $query the query to prepare * @param mixed $types array that contains the types of the placeholders * @param mixed $result_types array that contains the types of the columns in * the result set or MDB2_PREPARE_RESULT, if set to * MDB2_PREPARE_MANIP the query is handled as a manipulation query * @param mixed $lobs key (field) value (parameter) pair for all lob placeholders * @return mixed resource handle for the prepared query on success, a MDB2 * error on failure * @access public * @see bindParam, execute */ function prepare($query, $types = null, $result_types = null, $lobs = array()) { // connect to get server capabilities (http://pear.php.net/bugs/16147) $connection = $this->getConnection(); if (MDB2::isError($connection)) { return $connection; } if ($this->options['emulate_prepared'] || $this->supported['prepared_statements'] !== true) { return parent::prepare($query, $types, $result_types, $lobs); } $is_manip = $result_types === MDB2_PREPARE_MANIP; $offset = $this->offset; $limit = $this->limit; $this->offset = $this->limit = 0; $query = $this->_modifyQuery($query, $is_manip, $limit, $offset); $result = $this->debug($query, __FUNCTION__, array('is_manip' => $is_manip, 'when' => 'pre')); if ($result) { if (MDB2::isError($result)) { return $result; } $query = $result; } $placeholder_type_guess = $placeholder_type = null; $question = '?'; $colon = ':'; $positions = array(); $position = 0; while ($position < strlen($query)) { $q_position = strpos($query, $question, $position); $c_position = strpos($query, $colon, $position); if ($q_position && $c_position) { $p_position = min($q_position, $c_position); } elseif ($q_position) { $p_position = $q_position; } elseif ($c_position) { $p_position = $c_position; } else { break; } if (is_null($placeholder_type)) { $placeholder_type_guess = $query[$p_position]; } $new_pos = $this->_skipDelimitedStrings($query, $position, $p_position); if (MDB2::isError($new_pos)) { return $new_pos; } if ($new_pos != $position) { $position = $new_pos; continue; //evaluate again starting from the new position } //make sure this is not part of an user defined variable $new_pos = $this->_skipUserDefinedVariable($query, $position); if ($new_pos != $position) { $position = $new_pos; continue; //evaluate again starting from the new position } if ($query[$position] == $placeholder_type_guess) { if (is_null($placeholder_type)) { $placeholder_type = $query[$p_position]; $question = $colon = $placeholder_type; } if ($placeholder_type == ':') { $regexp = '/^.{' . ($position + 1) . '}(' . $this->options['bindname_format'] . ').*$/s'; $parameter = preg_replace($regexp, '\\1', $query); if ($parameter === '') { $err = $this->raiseError(MDB2_ERROR_SYNTAX, null, null, 'named parameter name must match "bindname_format" option', __FUNCTION__); return $err; } $positions[$p_position] = $parameter; $query = substr_replace($query, '?', $position, strlen($parameter) + 1); } else { $positions[$p_position] = count($positions); } $position = $p_position + 1; } else { $position = $p_position; } } static $prep_statement_counter = 1; $statement_name = sprintf($this->options['statement_format'], $this->phptype, $prep_statement_counter++ . sha1(microtime() + mt_rand())); $statement_name = substr(strtolower($statement_name), 0, $this->options['max_identifiers_length']); $query = "PREPARE {$statement_name} FROM " . $this->quote($query, 'text'); $statement = $this->_doQuery($query, true, $connection); if (MDB2::isError($statement)) { return $statement; } $class_name = 'MDB2_Statement_' . $this->phptype; $obj = new $class_name($this, $statement_name, $positions, $query, $types, $result_types, $is_manip, $limit, $offset); $this->debug($query, __FUNCTION__, array('is_manip' => $is_manip, 'when' => 'post', 'result' => $obj)); return $obj; }
/** * Prepares a query for multiple execution with execute(). * With some database backends, this is emulated. * prepare() requires a generic query as string like * 'INSERT INTO numbers VALUES(?,?)' or * 'INSERT INTO numbers VALUES(:foo,:bar)'. * The ? and :[a-zA-Z] and are placeholders which can be set using * bindParam() and the query can be send off using the execute() method. * * @param string $query the query to prepare * @param mixed $types array that contains the types of the placeholders * @param mixed $result_types array that contains the types of the columns in * the result set or MDB2_PREPARE_RESULT, if set to * MDB2_PREPARE_MANIP the query is handled as a manipulation query * @param mixed $lobs key (field) value (parameter) pair for all lob placeholders * @return mixed resource handle for the prepared query on success, a MDB2 * error on failure * @access public * @see bindParam, execute */ function &prepare($query, $types = null, $result_types = null, $lobs = array()) { if ($this->options['emulate_prepared']) { $obj =& parent::prepare($query, $types, $result_types, $lobs); return $obj; } $is_manip = $result_types === MDB2_PREPARE_MANIP; $offset = $this->offset; $limit = $this->limit; $this->offset = $this->limit = 0; $result = $this->debug($query, __FUNCTION__, array('is_manip' => $is_manip, 'when' => 'pre')); if ($result) { if (PEAR::isError($result)) { return $result; } $query = $result; } $query = $this->_modifyQuery($query, $is_manip, $limit, $offset); $placeholder_type_guess = $placeholder_type = null; $question = '?'; $colon = ':'; $positions = array(); $position = 0; $parameter = -1; while ($position < strlen($query)) { $q_position = strpos($query, $question, $position); $c_position = strpos($query, $colon, $position); if ($q_position && $c_position) { $p_position = min($q_position, $c_position); } elseif ($q_position) { $p_position = $q_position; } elseif ($c_position) { $p_position = $c_position; } else { break; } if (is_null($placeholder_type)) { $placeholder_type_guess = $query[$p_position]; } $new_pos = $this->_skipDelimitedStrings($query, $position, $p_position); if (PEAR::isError($new_pos)) { return $new_pos; } if ($new_pos != $position) { $position = $new_pos; continue; //evaluate again starting from the new position } if ($query[$position] == $placeholder_type_guess) { if (is_null($placeholder_type)) { $placeholder_type = $query[$p_position]; $question = $colon = $placeholder_type; if (!empty($types) && is_array($types)) { if ($placeholder_type == ':') { if (is_int(key($types))) { $types_tmp = $types; $types = array(); $count = -1; } } else { $types = array_values($types); } } } if ($placeholder_type == ':') { $parameter = preg_replace('/^.{' . ($position + 1) . '}([a-z0-9_]+).*$/si', '\\1', $query); if ($parameter === '') { $err =& $this->raiseError(MDB2_ERROR_SYNTAX, null, null, 'named parameter with an empty name', __FUNCTION__); return $err; } // use parameter name in type array if (isset($count) && isset($types_tmp[++$count])) { $types[$parameter] = $types_tmp[$count]; } $length = strlen($parameter) + 1; } else { ++$parameter; $length = strlen($parameter); } if (!in_array($parameter, $positions)) { $positions[] = $parameter; } if (isset($types[$parameter]) && ($types[$parameter] == 'clob' || $types[$parameter] == 'blob')) { if (!isset($lobs[$parameter])) { $lobs[$parameter] = $parameter; } $value = $this->quote(true, $types[$parameter]); $query = substr_replace($query, $value, $p_position, $length); $position = $p_position + strlen($value) - 1; } elseif ($placeholder_type == '?') { $query = substr_replace($query, ':' . $parameter, $p_position, 1); $position = $p_position + $length; } else { $position = $p_position + 1; } } else { $position = $p_position; } } if (is_array($lobs)) { $columns = $variables = ''; foreach ($lobs as $parameter => $field) { $columns .= ($columns ? ', ' : ' RETURNING ') . $field; $variables .= ($variables ? ', ' : ' INTO ') . ':' . $parameter; } $query .= $columns . $variables; } $connection = $this->getConnection(); if (PEAR::isError($connection)) { return $connection; } $statement = @OCIParse($connection, $query); if (!$statement) { $err =& $this->raiseError(null, null, null, 'Could not create statement', __FUNCTION__); return $err; } $class_name = 'MDB2_Statement_' . $this->phptype; $obj =& new $class_name($this, $statement, $positions, $query, $types, $result_types, $is_manip, $limit, $offset); $this->debug($query, __FUNCTION__, array('is_manip' => $is_manip, 'when' => 'post', 'result' => $obj)); return $obj; }
/** * @brief Prepare a SQL query * @param string $query Query string * @param int $limit * @param int $offset * @param bool $isManipulation * @return MDB2_Statement_Common prepared SQL query * * SQL query via MDB2 prepare(), needs to be execute()'d! */ public static function prepare($query, $limit = null, $offset = null, $isManipulation = null) { if (!is_null($limit) && $limit != -1) { if (self::$backend == self::BACKEND_MDB2) { //MDB2 uses or emulates limits & offset internally self::$MDB2->setLimit($limit, $offset); } else { //PDO does not handle limit and offset. //FIXME: check limit notation for other dbs //the following sql thus might needs to take into account db ways of representing it //(oracle has no LIMIT / OFFSET) $limit = (int) $limit; $limitsql = ' LIMIT ' . $limit; if (!is_null($offset)) { $offset = (int) $offset; $limitsql .= ' OFFSET ' . $offset; } //insert limitsql if (substr($query, -1) == ';') { //if query ends with ; $query = substr($query, 0, -1) . $limitsql . ';'; } else { $query .= $limitsql; } } } else { if (isset(self::$preparedQueries[$query]) and self::$cachingEnabled) { return self::$preparedQueries[$query]; } } $rawQuery = $query; // Optimize the query $query = self::processQuery($query); self::connect(); if ($isManipulation === null) { //try to guess, so we return the number of rows on manipulations $isManipulation = self::isManipulation($query); } // return the result if (self::$backend == self::BACKEND_MDB2) { // differentiate between query and manipulation if ($isManipulation) { $result = self::$connection->prepare($query, null, MDB2_PREPARE_MANIP); } else { $result = self::$connection->prepare($query, null, MDB2_PREPARE_RESULT); } // Die if we have an error (error means: bad query, not 0 results!) if (self::isError($result)) { throw new DatabaseException($result->getMessage(), $query); } } else { try { $result = self::$connection->prepare($query); } catch (PDOException $e) { throw new DatabaseException($e->getMessage(), $query); } // differentiate between query and manipulation $result = new PDOStatementWrapper($result, $isManipulation); } if (is_null($limit) || $limit == -1 and self::$cachingEnabled) { $type = OC_Config::getValue("dbtype", "sqlite"); if ($type != 'sqlite' && $type != 'sqlite3') { self::$preparedQueries[$rawQuery] = $result; } } return $result; }
/** * Prepares a query for multiple execution with execute(). * With some database backends, this is emulated. * prepare() requires a generic query as string like * 'INSERT INTO numbers VALUES(?,?)' or * 'INSERT INTO numbers VALUES(:foo,:bar)'. * The ? and :[a-zA-Z] and are placeholders which can be set using * bindParam() and the query can be send off using the execute() method. * * @param string $query the query to prepare * @param mixed $types array that contains the types of the placeholders * @param mixed $result_types array that contains the types of the columns in * the result set or MDB2_PREPARE_RESULT, if set to * MDB2_PREPARE_MANIP the query is handled as a manipulation query * @param mixed $lobs key (field) value (parameter) pair for all lob placeholders * @return mixed resource handle for the prepared query on success, a MDB2 * error on failure * @access public * @see bindParam, execute */ function &prepare($query, $types = null, $result_types = null, $lobs = array()) { if ($this->options['emulate_prepared']) { $obj =& parent::prepare($query, $types, $result_types, $lobs); return $obj; } $is_manip = $result_types === MDB2_PREPARE_MANIP; $offset = $this->offset; $limit = $this->limit; $this->offset = $this->limit = 0; $result = $this->debug($query, __FUNCTION__, array('is_manip' => $is_manip, 'when' => 'pre')); if ($result) { if (PEAR::isError($result)) { return $result; } $query = $result; } $pgtypes = function_exists('pg_prepare') ? false : array(); if ($pgtypes !== false && !empty($types)) { $this->loadModule('Datatype', null, true); } $query = $this->_modifyQuery($query, $is_manip, $limit, $offset); $placeholder_type_guess = $placeholder_type = null; $question = '?'; $colon = ':'; $positions = array(); $position = $parameter = 0; while ($position < strlen($query)) { $q_position = strpos($query, $question, $position); $c_position = strpos($query, $colon, $position); if ($q_position && $c_position) { $p_position = min($q_position, $c_position); } elseif ($q_position) { $p_position = $q_position; } elseif ($c_position) { $p_position = $c_position; } else { break; } if (is_null($placeholder_type)) { $placeholder_type_guess = $query[$p_position]; } $new_pos = $this->_skipDelimitedStrings($query, $position, $p_position); if (PEAR::isError($new_pos)) { return $new_pos; } if ($new_pos != $position) { $position = $new_pos; continue; //evaluate again starting from the new position } if ($query[$position] == $placeholder_type_guess) { if (is_null($placeholder_type)) { $placeholder_type = $query[$p_position]; $question = $colon = $placeholder_type; if (!empty($types) && is_array($types)) { if ($placeholder_type == ':') { } else { $types = array_values($types); } } } if ($placeholder_type_guess == '?') { $length = 1; $name = $parameter; } else { $name = preg_replace('/^.{' . ($position + 1) . '}([a-z0-9_]+).*$/si', '\\1', $query); if ($name === '') { $err =& $this->customRaiseError(MDB2_ERROR_SYNTAX, null, null, 'named parameter with an empty name', __FUNCTION__); return $err; } $length = strlen($name) + 1; } if ($pgtypes !== false) { if (is_array($types) && array_key_exists($name, $types)) { $pgtypes[] = $this->datatype->mapPrepareDatatype($types[$name]); } elseif (is_array($types) && array_key_exists($parameter, $types)) { $pgtypes[] = $this->datatype->mapPrepareDatatype($types[$parameter]); } else { $pgtypes[] = 'text'; } } if ($key_parameter = array_search($name, $positions)) { $next_parameter = 1; foreach ($positions as $key => $value) { if ($key_parameter == $key) { break; } ++$next_parameter; } } else { ++$parameter; $next_parameter = $parameter; $positions[] = $name; } $query = substr_replace($query, '$' . $parameter, $position, $length); $position = $p_position + strlen($parameter); } else { $position = $p_position; } } $connection = $this->getConnection(); if (PEAR::isError($connection)) { return $connection; } $statement_name = sprintf($this->options['statement_format'], $this->phptype, md5(time() + rand())); $statement_name = strtolower($statement_name); if ($pgtypes === false) { $result = @pg_prepare($connection, $statement_name, $query); if (!$result) { $err =& $this->customRaiseError(null, null, null, 'Unable to create prepared statement handle', __FUNCTION__); return $err; } } else { $types_string = ''; if ($pgtypes) { $types_string = ' (' . implode(', ', $pgtypes) . ') '; } $query = 'PREPARE ' . $statement_name . $types_string . ' AS ' . $query; $statement =& $this->_doQuery($query, true, $connection); if (PEAR::isError($statement)) { return $statement; } } $class_name = 'MDB2_Statement_' . $this->phptype; $obj = new $class_name($this, $statement_name, $positions, $query, $types, $result_types, $is_manip, $limit, $offset); $this->debug($query, __FUNCTION__, array('is_manip' => $is_manip, 'when' => 'post', 'result' => $obj)); return $obj; }
/** * Prepares a query for multiple execution with execute(). * With some database backends, this is emulated. * prepare() requires a generic query as string like * 'INSERT INTO numbers VALUES(?,?)' or * 'INSERT INTO numbers VALUES(:foo,:bar)'. * The ? and :[a-zA-Z] and are placeholders which can be set using * bindParam() and the query can be send off using the execute() method. * * @param string $query the query to prepare * @param mixed $types array that contains the types of the placeholders * @param mixed $result_types array that contains the types of the columns in * the result set or MDB2_PREPARE_RESULT, if set to * MDB2_PREPARE_MANIP the query is handled as a manipulation query * @param mixed $lobs key (field) value (parameter) pair for all lob placeholders * @return mixed resource handle for the prepared query on success, a MDB2 * error on failure * @access public * @see bindParam, execute */ function &prepare($query, $types = null, $result_types = null, $lobs = array()) { if ($this->options['emulate_prepared']) { $obj =& parent::prepare($query, $types, $result_types, $lobs); return $obj; } $is_manip = $result_types === MDB2_PREPARE_MANIP; $offset = $this->offset; $limit = $this->limit; $this->offset = $this->limit = 0; $result = $this->debug($query, __FUNCTION__, array('is_manip' => $is_manip, 'when' => 'pre')); if ($result) { if (PEAR::isError($result)) { return $result; } $query = $result; } $placeholder_type_guess = $placeholder_type = null; $question = '?'; $colon = ':'; $positions = array(); $position = 0; while ($position < strlen($query)) { $q_position = strpos($query, $question, $position); $c_position = strpos($query, $colon, $position); if ($q_position && $c_position) { $p_position = min($q_position, $c_position); } elseif ($q_position) { $p_position = $q_position; } elseif ($c_position) { $p_position = $c_position; } else { break; } if (is_null($placeholder_type)) { $placeholder_type_guess = $query[$p_position]; } $new_pos = $this->_skipDelimitedStrings($query, $position, $p_position); if (PEAR::isError($new_pos)) { return $new_pos; } if ($new_pos != $position) { $position = $new_pos; continue; //evaluate again starting from the new position } if ($query[$position] == $placeholder_type_guess) { if (is_null($placeholder_type)) { $placeholder_type = $query[$p_position]; $question = $colon = $placeholder_type; } if ($placeholder_type == ':') { $parameter = preg_replace('/^.{' . ($position + 1) . '}([a-z0-9_]+).*$/si', '\\1', $query); if ($parameter === '') { $err =& $this->raiseError(MDB2_ERROR_SYNTAX, null, null, 'named parameter with an empty name', __FUNCTION__); return $err; } $positions[] = $parameter; $query = substr_replace($query, '?', $position, strlen($parameter) + 1); } else { $positions[] = count($positions); } $position = $p_position + 1; } else { $position = $p_position; } } $connection = $this->getConnection(); if (PEAR::isError($connection)) { return $connection; } $statement = @ibase_prepare($connection, $query); if (!$statement) { $err =& $this->raiseError(null, null, null, 'Could not create statement', __FUNCTION__); return $err; } $class_name = 'MDB2_Statement_' . $this->phptype; $obj =& new $class_name($this, $statement, $positions, $query, $types, $result_types, $is_manip, $limit, $offset); $this->debug($query, __FUNCTION__, array('is_manip' => $is_manip, 'when' => 'post', 'result' => $obj)); return $obj; }