Получение необработанной строки запроса SQL из подготовленных операторов PDO
Есть ли способ получить необработанную строку SQL, выполняемую при вызове PDOStatement::execute() для подготовленного оператора? Для целей отладки это было бы чрезвычайно полезно.
17 ответов
Я предполагаю, что вы имеете в виду, что вам нужен окончательный SQL-запрос со значениями параметров, вставленными в него. Я понимаю, что это было бы полезно для отладки, но это не то, как работают подготовленные операторы. Параметры не объединяются с подготовленным оператором на стороне клиента, поэтому PDO никогда не должен иметь доступа к строке запроса в сочетании с ее параметрами.
Оператор SQL отправляется на сервер базы данных, когда вы выполняете prepare(), а параметры отправляются отдельно, когда вы выполняете execute(). Общий журнал запросов MySQL действительно показывает окончательный SQL со значениями, интерполированными после того, как вы выполните (). Ниже приведена выдержка из моего общего журнала запросов. Я запускал запросы из CLI mysql, а не из PDO, но принцип тот же.
081016 16:51:28 2 Query prepare s1 from 'select * from foo where i = ?'
2 Prepare [2] select * from foo where i = ?
081016 16:51:39 2 Query set @a =1
081016 16:51:47 2 Query execute s1 using @a
2 Execute [2] select * from foo where i = 1
Вы также можете получить то, что вы хотите, если вы установите атрибут PDO PDO::ATTR_EMULATE_PREPARES. В этом режиме PDO интерполирует параметры в запрос SQL и отправляет весь запрос при выполнении (). Это не правильно подготовленный запрос. Вы обойдете преимущества подготовленных запросов, интерполируя переменные в строку SQL перед execute().
Re комментарий от @afilina:
Нет, текстовый SQL-запрос не объединяется с параметрами во время выполнения. Так что PDO ничего не может вам показать.
Внутренне, если вы используете PDO::ATTR_EMULATE_PREPARES, PDO создает копию запроса SQL и интерполирует в него значения параметров перед подготовкой и выполнением. Но PDO не предоставляет этот модифицированный SQL-запрос.
Объект PDOStatement имеет свойство $queryString, но оно задается только в конструкторе для PDOStatement и не обновляется, когда запрос перезаписывается с параметрами.
Было бы разумным запросом функции для PDO попросить их предоставить переписанный запрос. Но даже это не даст вам "завершенного" запроса, если вы не используете PDO::ATTR_EMULATE_PREPARES.
Вот почему я показываю вышеупомянутый обходной путь использования общего журнала запросов сервера MySQL, потому что в этом случае даже подготовленный запрос с заполнителями параметров перезаписывается на сервере со значениями параметров, заданными в строку запроса. Но это делается только во время регистрации, а не во время выполнения запроса.
/**
* Replaces any parameter placeholders in a query with the value of that
* parameter. Useful for debugging. Assumes anonymous parameters from
* $params are are in the same order as specified in $query
*
* @param string $query The sql query with parameter placeholders
* @param array $params The array of substitution parameters
* @return string The interpolated query
*/
public static function interpolateQuery($query, $params) {
$keys = array();
# build a regular expression for each parameter
foreach ($params as $key => $value) {
if (is_string($key)) {
$keys[] = '/:'.$key.'/';
} else {
$keys[] = '/[?]/';
}
}
$query = preg_replace($keys, $params, $query, 1, $count);
#trigger_error('replaced '.$count.' keys');
return $query;
}
Я изменил метод, чтобы включить обработку вывода массивов для операторов вроде WHERE IN (?).
ОБНОВЛЕНИЕ: просто добавлена проверка на значение NULL и дублированные $params, чтобы фактические значения $ param не изменялись.
Отличная работа bigwebguy и спасибо!
/**
* Replaces any parameter placeholders in a query with the value of that
* parameter. Useful for debugging. Assumes anonymous parameters from
* $params are are in the same order as specified in $query
*
* @param string $query The sql query with parameter placeholders
* @param array $params The array of substitution parameters
* @return string The interpolated query
*/
public function interpolateQuery($query, $params) {
$keys = array();
$values = $params;
# build a regular expression for each parameter
foreach ($params as $key => $value) {
if (is_string($key)) {
$keys[] = '/:'.$key.'/';
} else {
$keys[] = '/[?]/';
}
if (is_string($value))
$values[$key] = "'" . $value . "'";
if (is_array($value))
$values[$key] = "'" . implode("','", $value) . "'";
if (is_null($value))
$values[$key] = 'NULL';
}
$query = preg_replace($keys, $values, $query);
return $query;
}
Решение состоит в том, чтобы добровольно поместить ошибку в запрос и напечатать сообщение об ошибке:
//Connection to the database
$co = new PDO('mysql:dbname=myDB;host=localhost','root','');
//We allow to print the errors whenever there is one
$co->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
//We create our prepared statement
$stmt = $co->prepare("ELECT * FROM Person WHERE age=:age"); //I removed the 'S' of 'SELECT'
$stmt->bindValue(':age','18',PDO::PARAM_STR);
try {
$stmt->execute();
} catch (PDOException $e) {
echo $e->getMessage();
}
Стандартный вывод:
SQLSTATE [42000]: синтаксическая ошибка или нарушение прав доступа: [...] рядом с 'ВЫБРАТЬ * ОТ ЛИЦА, ГДЕ ВОЗРАСТ =18' в строке 1
Важно отметить, что он печатает только первые 80 символов запроса.
Немного поздно, наверное, но сейчас есть PDOStatement::debugDumpParams
Сбрасывает информацию, содержащуюся в подготовленном утверждении, непосредственно на вывод. Он предоставит используемый SQL-запрос, количество используемых параметров (Params), список параметров с их именем, типом (paramtype) в виде целого числа, именем ключа или позицией и позицией в запросе (если это поддерживается драйвером PDO, в противном случае это будет -1).
Вы можете найти больше на официальных документах php
Пример:
<?php
/* Execute a prepared statement by binding PHP variables */
$calories = 150;
$colour = 'red';
$sth = $dbh->prepare('SELECT name, colour, calories
FROM fruit
WHERE calories < :calories AND colour = :colour');
$sth->bindParam(':calories', $calories, PDO::PARAM_INT);
$sth->bindValue(':colour', $colour, PDO::PARAM_STR, 12);
$sth->execute();
$sth->debugDumpParams();
?>
Добавил немного больше в код Майка - пройтись по значениям, чтобы добавить одинарные кавычки
/**
* Replaces any parameter placeholders in a query with the value of that
* parameter. Useful for debugging. Assumes anonymous parameters from
* $params are are in the same order as specified in $query
*
* @param string $query The sql query with parameter placeholders
* @param array $params The array of substitution parameters
* @return string The interpolated query
*/
public function interpolateQuery($query, $params) {
$keys = array();
$values = $params;
# build a regular expression for each parameter
foreach ($params as $key => $value) {
if (is_string($key)) {
$keys[] = '/:'.$key.'/';
} else {
$keys[] = '/[?]/';
}
if (is_array($value))
$values[$key] = implode(',', $value);
if (is_null($value))
$values[$key] = 'NULL';
}
// Walk the array to see if we can add single-quotes to strings
array_walk($values, create_function('&$v, $k', 'if (!is_numeric($v) && $v!="NULL") $v = "\'".$v."\'";'));
$query = preg_replace($keys, $values, $query, 1, $count);
return $query;
}
PDOStatement имеет открытое свойство $queryString. Это должно быть то, что вы хотите.
Я только что заметил, что PDOStatement имеет недокументированный метод debugDumpParams(), который вы также можете посмотреть.
Я потратил много времени на изучение этой ситуации для собственных нужд. Эта и несколько других SO-тем мне очень помогли, поэтому я хотел поделиться тем, что я придумал.
Хотя доступ к интерполированной строке запроса является значительным преимуществом при устранении неполадок, мы хотели иметь возможность вести журнал только определенных запросов (поэтому использование журналов базы данных для этой цели не было идеальным). Мы также хотели иметь возможность использовать журналы для воссоздания состояния таблиц в любой момент времени, поэтому нам нужно было убедиться, что интерполированные строки были экранированы правильно. Наконец, мы хотели расширить эту функциональность до всей нашей кодовой базы, чтобы переписать ее как можно меньше (сроки, маркетинг и тому подобное; вы знаете, как это).
Мое решение состояло в том, чтобы расширить функциональные возможности объекта PDOStatement по умолчанию для кэширования параметризованных значений (или ссылок), и при выполнении инструкции используйте функциональные возможности объекта PDO для правильного экранирования параметров, когда они вводятся обратно в запрос. строка. Затем мы могли бы связать, чтобы выполнить метод объекта оператора и зарегистрировать фактический запрос, который был выполнен в это время (или, по крайней мере, настолько точным, насколько это возможно).
Как я уже сказал, мы не хотели изменять всю базу кода, чтобы добавить эту функциональность, поэтому мы перезаписываем значение по умолчанию bindParam() а также bindValue() методы объекта PDOStatement, делаем наше кэширование связанных данных, затем вызываем parent::bindParam() или родительский::bindValue(), Это позволило нашей существующей кодовой базе продолжать функционировать как обычно.
Наконец, когда execute() метод вызывается, мы выполняем нашу интерполяцию и предоставляем результирующую строку как новое свойство E_PDOStatement->fullQuery, Это может быть вывод для просмотра запроса или, например, запись в файл журнала.
Расширение вместе с инструкциями по установке и настройке доступно на github:
https://github.com/noahheck/E_PDOStatement
ОТКАЗ ОТ ОТВЕТСТВЕННОСТИ:
Очевидно, как я уже упоминал, я написал это расширение. Поскольку это было разработано с помощью многих потоков здесь, я хотел опубликовать свое решение здесь на случай, если кто-то еще сталкивался с этими потоками, так же, как я сделал.
Вы можете расширить класс PDOStatement, чтобы захватывать ограниченные переменные и сохранять их для дальнейшего использования. Затем можно добавить 2 метода: один для очистки переменных ( debugBindedVariables), а другой для печати запроса с этими переменными ( debugQuery):
class DebugPDOStatement extends \PDOStatement{
private $bound_variables=array();
protected $pdo;
protected function __construct($pdo) {
$this->pdo = $pdo;
}
public function bindValue($parameter, $value, $data_type=\PDO::PARAM_STR){
$this->bound_variables[$parameter] = (object) array('type'=>$data_type, 'value'=>$value);
return parent::bindValue($parameter, $value, $data_type);
}
public function bindParam($parameter, &$variable, $data_type=\PDO::PARAM_STR, $length=NULL , $driver_options=NULL){
$this->bound_variables[$parameter] = (object) array('type'=>$data_type, 'value'=>&$variable);
return parent::bindParam($parameter, $variable, $data_type, $length, $driver_options);
}
public function debugBindedVariables(){
$vars=array();
foreach($this->bound_variables as $key=>$val){
$vars[$key] = $val->value;
if($vars[$key]===NULL)
continue;
switch($val->type){
case \PDO::PARAM_STR: $type = 'string'; break;
case \PDO::PARAM_BOOL: $type = 'boolean'; break;
case \PDO::PARAM_INT: $type = 'integer'; break;
case \PDO::PARAM_NULL: $type = 'null'; break;
default: $type = FALSE;
}
if($type !== FALSE)
settype($vars[$key], $type);
}
if(is_numeric(key($vars)))
ksort($vars);
return $vars;
}
public function debugQuery(){
$queryString = $this->queryString;
$vars=$this->debugBindedVariables();
$params_are_numeric=is_numeric(key($vars));
foreach($vars as $key=>&$var){
switch(gettype($var)){
case 'string': $var = "'{$var}'"; break;
case 'integer': $var = "{$var}"; break;
case 'boolean': $var = $var ? 'TRUE' : 'FALSE'; break;
case 'NULL': $var = 'NULL';
default:
}
}
if($params_are_numeric){
$queryString = preg_replace_callback( '/\?/', function($match) use( &$vars) { return array_shift($vars); }, $queryString);
}else{
$queryString = strtr($queryString, $vars);
}
echo $queryString.PHP_EOL;
}
}
class DebugPDO extends \PDO{
public function __construct($dsn, $username="", $password="", $driver_options=array()) {
$driver_options[\PDO::ATTR_STATEMENT_CLASS] = array('DebugPDOStatement', array($this));
$driver_options[\PDO::ATTR_PERSISTENT] = FALSE;
parent::__construct($dsn,$username,$password, $driver_options);
}
}
И тогда вы можете использовать этот унаследованный класс для отладки мошенников.
$dbh = new DebugPDO('mysql:host=localhost;dbname=test;','user','pass');
$var='user_test';
$sql=$dbh->prepare("SELECT user FROM users WHERE user = :test");
$sql->bindValue(':test', $var, PDO::PARAM_STR);
$sql->execute();
$sql->debugQuery();
print_r($sql->debugBindedVariables());
В результате чего
ВЫБЕРИТЕ пользователя ИЗ ПОЛЬЗОВАТЕЛЕЙ, ГДЕ user = 'user_test'
Array ([: test] => user_test)
Ни один из существующих ответов не казался полным или безопасным, поэтому я придумал эту функцию со следующими улучшениями:
работает с обоими безымянными(
?) и по имени (:foo) параметры.используя PDO::quote() для правильного экранирования значений, которые не
NULL,int,floatили жеbool.правильно обрабатывает строковые значения, содержащие
"?"а также":foo"не принимая их за заполнители.
function interpolateSQL(PDO $pdo, string $query, array $params) : string {
$s = chr(2); // Escape sequence for start of placeholder
$e = chr(3); // Escape sequence for end of placeholder
$keys = [];
$values = [];
// Make sure we use escape sequences that are not present in any value
// to escape the placeholders.
foreach ($params as $key => $value) {
while( mb_stripos($value, $s) !== false ) $s .= $s;
while( mb_stripos($value, $e) !== false ) $e .= $e;
}
foreach ($params as $key => $value) {
// Build a regular expression for each parameter
$keys[] = is_string($key) ? "/$s:$key$e/" : "/$s\?$e/";
// Treat each value depending on what type it is.
// While PDO::quote() has a second parameter for type hinting,
// it doesn't seem reliable (at least for the SQLite driver).
if( is_null($value) ){
$values[$key] = 'NULL';
}
elseif( is_int($value) || is_float($value) ){
$values[$key] = $value;
}
elseif( is_bool($value) ){
$values[$key] = $value ? 'true' : 'false';
}
else{
$value = str_replace('\\', '\\\\', $value);
$values[$key] = $pdo->quote($value);
}
}
// Surround placehodlers with escape sequence, so we don't accidentally match
// "?" or ":foo" inside any of the values.
$query = preg_replace(['/\?/', '/(:[a-zA-Z0-9_]+)/'], ["$s?$e", "$s$1$e"], $query);
// Replace placeholders with actual values
$query = preg_replace($keys, $values, $query, 1, $count);
// Verify that we replaced exactly as many placeholders as there are keys and values
if( $count !== count($keys) || $count !== count($values) ){
throw new \Exception('Number of replacements not same as number of keys and/or values');
}
return $query;
}
Я уверен, что его можно еще улучшить.
В моем случае я в конечном итоге просто зарегистрировал фактический «неподготовленный запрос» (т.е. SQL, содержащий заполнители) вместе с параметрами в кодировке JSON. Однако этот код может использоваться в некоторых случаях, когда вам действительно нужно интерполировать окончательный SQL-запрос.
Вы можете использовать sprintf(str_replace('?', '"%s"', $sql), ...$params);
Вот пример:
function mysqli_prepared_query($link, $sql, $types='', $params=array()) {
echo sprintf(str_replace('?', '"%s"', $sql), ...$params);
//prepare, bind, execute
}
$link = new mysqli($server, $dbusername, $dbpassword, $database);
$sql = "SELECT firstname, lastname FROM users WHERE userage >= ? AND favecolor = ?";
$types = "is"; //integer and string
$params = array(20, "Brown");
if(!$qry = mysqli_prepared_query($link, $sql, $types, $params)){
echo "Failed";
} else {
echo "Success";
}
Обратите внимание, что это работает только для PHP >= 5.6.
Упомянутое свойство $queryString, вероятно, будет возвращать только переданный запрос, без замены параметров их значениями. В.Net часть catch моего исполнителя запросов выполняет простую поисковую замену параметров с их значениями, которые были предоставлены, чтобы журнал ошибок мог показывать фактические значения, которые использовались для запроса. Вы должны иметь возможность перечислять параметры в PHP и заменять параметры на присвоенные им значения.
Я знаю, что этот вопрос немного устарел, но я использую этот код уже давно (я использовал ответ от @chris-go), и теперь этот код устарел с PHP 7.2
Я выложу обновленную версию этого кода (кредит для основного кода от @bigwebguy, @mike и @ chris-go, все они ответы на этот вопрос):
/**
* Replaces any parameter placeholders in a query with the value of that
* parameter. Useful for debugging. Assumes anonymous parameters from
* $params are are in the same order as specified in $query
*
* @param string $query The sql query with parameter placeholders
* @param array $params The array of substitution parameters
* @return string The interpolated query
*/
public function interpolateQuery($query, $params) {
$keys = array();
$values = $params;
# build a regular expression for each parameter
foreach ($params as $key => $value) {
if (is_string($key)) {
$keys[] = '/:'.$key.'/';
} else {
$keys[] = '/[?]/';
}
if (is_array($value))
$values[$key] = implode(',', $value);
if (is_null($value))
$values[$key] = 'NULL';
}
// Walk the array to see if we can add single-quotes to strings
array_walk($values, function(&$v, $k) { if (!is_numeric($v) && $v != "NULL") $v = "\'" . $v . "\'"; });
$query = preg_replace($keys, $values, $query, 1, $count);
return $query;
}
Обратите внимание, что изменения в коде внесены в функцию array_walk(), заменив функцию create_function анонимной функцией. Это делает эти хорошие части кода функциональными и совместимыми с PHP 7.2 (и, надеюсь, будущими версиями тоже).
Ответ Майка работает хорошо, пока вы не используете значение связывания "повторное использование".
Например:
SELECT * FROM `an_modules` AS `m` LEFT JOIN `an_module_sites` AS `ms` ON m.module_id = ms.module_id WHERE 1 AND `module_enable` = :module_enable AND `site_id` = :site_id AND (`module_system_name` LIKE :search OR `module_version` LIKE :search)
Ответ Майка может заменить только первое: поиск, но не второе.
Итак, я переписал его ответ для работы с несколькими параметрами, которые можно использовать повторно правильно.
public function interpolateQuery($query, $params) {
$keys = array();
$values = $params;
$values_limit = [];
$words_repeated = array_count_values(str_word_count($query, 1, ':_'));
# build a regular expression for each parameter
foreach ($params as $key => $value) {
if (is_string($key)) {
$keys[] = '/:'.$key.'/';
$values_limit[$key] = (isset($words_repeated[':'.$key]) ? intval($words_repeated[':'.$key]) : 1);
} else {
$keys[] = '/[?]/';
$values_limit = [];
}
if (is_string($value))
$values[$key] = "'" . $value . "'";
if (is_array($value))
$values[$key] = "'" . implode("','", $value) . "'";
if (is_null($value))
$values[$key] = 'NULL';
}
if (is_array($values)) {
foreach ($values as $key => $val) {
if (isset($values_limit[$key])) {
$query = preg_replace(['/:'.$key.'/'], [$val], $query, $values_limit[$key], $count);
} else {
$query = preg_replace(['/:'.$key.'/'], [$val], $query, 1, $count);
}
}
unset($key, $val);
} else {
$query = preg_replace($keys, $values, $query, 1, $count);
}
unset($keys, $values, $values_limit, $words_repeated);
return $query;
}
Мне нужно записать полную строку запроса после bind param, так что это часть моего кода. Надеюсь, это полезно для всех, у кого та же проблема.
/**
*
* @param string $str
* @return string
*/
public function quote($str) {
if (!is_array($str)) {
return $this->pdo->quote($str);
} else {
$str = implode(',', array_map(function($v) {
return $this->quote($v);
}, $str));
if (empty($str)) {
return 'NULL';
}
return $str;
}
}
/**
*
* @param string $query
* @param array $params
* @return string
* @throws Exception
*/
public function interpolateQuery($query, $params) {
$ps = preg_split("/'/is", $query);
$pieces = [];
$prev = null;
foreach ($ps as $p) {
$lastChar = substr($p, strlen($p) - 1);
if ($lastChar != "\\") {
if ($prev === null) {
$pieces[] = $p;
} else {
$pieces[] = $prev . "'" . $p;
$prev = null;
}
} else {
$prev .= ($prev === null ? '' : "'") . $p;
}
}
$arr = [];
$indexQuestionMark = -1;
$matches = [];
for ($i = 0; $i < count($pieces); $i++) {
if ($i % 2 !== 0) {
$arr[] = "'" . $pieces[$i] . "'";
} else {
$st = '';
$s = $pieces[$i];
while (!empty($s)) {
if (preg_match("/(\?|:[A-Z0-9_\-]+)/is", $s, $matches, PREG_OFFSET_CAPTURE)) {
$index = $matches[0][1];
$st .= substr($s, 0, $index);
$key = $matches[0][0];
$s = substr($s, $index + strlen($key));
if ($key == '?') {
$indexQuestionMark++;
if (array_key_exists($indexQuestionMark, $params)) {
$st .= $this->quote($params[$indexQuestionMark]);
} else {
throw new Exception('Wrong params in query at ' . $index);
}
} else {
if (array_key_exists($key, $params)) {
$st .= $this->quote($params[$key]);
} else {
throw new Exception('Wrong params in query with key ' . $key);
}
}
} else {
$st .= $s;
$s = null;
}
}
$arr[] = $st;
}
}
return implode('', $arr);
}
preg_replace не работал для меня, и когда binding_ был больше 9, binding_1 и binding_10 был заменен str_replace (оставив 0 позади), поэтому я сделал замены в обратном направлении:
public function interpolateQuery($query, $params) {
$keys = array();
$length = count($params)-1;
for ($i = $length; $i >=0; $i--) {
$query = str_replace(':binding_'.(string)$i, '\''.$params[$i]['val'].'\'', $query);
}
// $query = str_replace('SQL_CALC_FOUND_ROWS', '', $query, $count);
return $query;
}
Надеюсь, кто-то найдет это полезным.
Немного связано... если вы просто пытаетесь очистить определенную переменную, вы можете использовать PDO:: quote. Например, для поиска нескольких частичных условий LIKE, если вы застряли с ограниченной структурой, такой как CakePHP:
$pdo = $this->getDataSource()->getConnection();
$results = $this->find('all', array(
'conditions' => array(
'Model.name LIKE ' . $pdo->quote("%{$keyword1}%"),
'Model.name LIKE ' . $pdo->quote("%{$keyword2}%"),
),
);