Получение необработанной строки запроса SQL из подготовленных операторов PDO

PDOStatement имеет общедоступное свойство $ queryString. Это должно быть то, что вы хотите.

Я только что заметил, что в PDOStatement есть недокументированный метод debugDumpParams (), на который вы также можете захотеть взглянуть.

Упомянутое свойство $ queryString, вероятно, вернет только переданный запрос без замены параметров их значениями. В .Net у меня есть часть catch моего обработчика запросов, которая выполняет простую поисковую замену параметров с их значениями, которые были предоставлены, чтобы журнал ошибок мог отображать фактические значения, которые использовались для запроса. Вы должны иметь возможность перечислять параметры в PHP и заменять параметры их назначенными значениями.

Я предполагаю, что вы имеете в виду, что хотите получить окончательный SQL-запрос с интерполированными в него значениями параметров. Я понимаю, что это было бы полезно для отладки, но подготовленные операторы работают не так. Параметры не объединяются с подготовленным оператором на стороне клиента, поэтому PDO никогда не должен иметь доступ к строке запроса, объединенной с ее параметрами.

Оператор SQL отправляется на сервер базы данных, когда вы выполняете prepare (), а параметры отправляются отдельно, когда вы выполняете execute (). Общий журнал запросов MySQL показывает окончательный SQL со значениями, интерполированными после выполнения (). Ниже приводится выдержка из моего общего журнала запросов. Я запускал запросы из командной строки 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 ().

Ответ от @afilina:

Нет, текстовый SQL-запрос - это нет в сочетании с параметрами во время выполнения. Так что PDO нечего вам показать.

Внутренне, если вы используете PDO :: ATTR_EMULATE_PREPARES, PDO делает копию запроса SQL и интерполирует в него значения параметров перед подготовкой и выполнением. Но PDO не предоставляет этот измененный SQL-запрос.

У объекта PDOStatement есть свойство $ queryString, но оно устанавливается только в конструкторе для PDOStatement и не обновляется при перезаписи запроса с параметрами.

Было бы разумным запросом функции для PDO попросить их предоставить переписанный запрос. Но даже это не даст вам «полного» запроса, если вы не используете PDO :: ATTR_EMULATE_PREPARES.

Вот почему я показываю выше обходной путь использования общего журнала запросов сервера MySQL, потому что в этом случае даже подготовленный запрос с заполнителями параметров перезаписывается на сервере, а значения параметров возвращаются в строку запроса. Но это делается только во время регистрации, а не во время выполнения запроса.

Я изменил метод, чтобы включить обработку вывода массивов для таких операторов, как 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;
}

Добавил немного больше в код Майка - пройдитесь по значениям, чтобы добавить одинарные кавычки

/**
 * 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;
}

Я потратил много времени на изучение этой ситуации для своих нужд. Эта и несколько других тем SO мне очень помогли, поэтому я хотел поделиться тем, что я придумал.

Хотя доступ к интерполированной строке запроса является значительным преимуществом при устранении неполадок, мы хотели иметь возможность вести журнал только определенных запросов (поэтому использование журналов базы данных для этой цели было не идеальным). Мы также хотели иметь возможность использовать журналы для воссоздания состояния таблиц в любой момент времени, поэтому нам нужно было убедиться, что интерполированные строки были экранированы правильно. Наконец, мы хотели расширить эту функциональность на всю нашу базу кода, чтобы переписать ее как можно меньше (дедлайны, маркетинг и т. д., Вы знаете, как это бывает).

Мое решение заключалось в том, чтобы расширить функциональные возможности объекта PDOStatement по умолчанию для кеширования параметризованных значений (или ссылок), а при выполнении оператора использовать функциональные возможности объекта PDO, чтобы правильно экранировать параметры, когда они вводятся обратно в запрос. нить. Затем мы могли бы подключиться к методу выполнения объекта оператора и зарегистрировать фактический запрос, который был выполнен в то время (или, по крайней мере, как можно более точным воспроизведением).

Как я уже сказал, мы не хотели изменять всю базу кода, чтобы добавить эту функциональность, поэтому мы перезаписываем методы bindParam() и bindValue() по умолчанию объекта PDOStatement, выполняем кэширование связанных данных, а затем вызываем parent::bindParam() или parent :: bindValue(). . Это позволило нашей существующей кодовой базе продолжать работать в обычном режиме.

Наконец, когда вызывается метод execute(), мы выполняем нашу интерполяцию и предоставляем результирующую строку как новое свойство E_PDOStatement->fullQuery. Его можно выводить для просмотра запроса или, например, записывать в файл журнала.

Расширение вместе с инструкциями по установке и настройке доступно на github:

https://github.com/noahheck/E_PDOStatement

ОТКАЗ ОТ ОТВЕТСТВЕННОСТИ:
Очевидно, как я уже упоминал, я написал это расширение. Поскольку оно было разработано с помощью многих потоков здесь, я хотел опубликовать свое решение здесь на случай, если кто-то еще столкнется с этими потоками, как и я.

Немного поздно наверное но сейчас есть PDOStatement::debugDumpParams

Dumps the informations contained by a prepared statement directly on the output. It will provide the SQL query in use, the number of parameters used (Params), the list of parameters, with their name, type (paramtype) as an integer, their key name or position, and the position in the query (if this is supported by the PDO driver, otherwise, it will be -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();

?>

В некоторой степени связано ... если вы просто пытаетесь очистить конкретную переменную, вы можете использовать PDO :: цитата. Например, чтобы найти несколько частичных условий 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}%"),
    ),
);

Ответ Майка работает нормально, пока вы не используете значение привязки "повторно использовать". Например:

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;
}

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;

}

Надеюсь, кто-то сочтет это полезным.

Мне нужно записать полную строку запроса после параметра привязки, так что это часть моего кода. Надеюсь, это будет полезно для всех, у кого есть одна и та же проблема.

/**
 * 
 * @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);
}

Вы можете расширить класс 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());

В результате чего

SELECT user FROM users WHERE user = 'user_test'

Array ( [:test] => user_test )

Решение состоит в том, чтобы добровольно поместить ошибку в запрос и распечатать сообщение об ошибке:

//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]: Syntax error or access violation: [...] near 'ELECT * FROM Person WHERE age=18' at line 1

Важно отметить, что он печатает только первые 80 символов запроса.

Я знаю, что этот вопрос немного устарел, но я использую этот код с давних пор (я использовал ответ от @ chris-go), и теперь этот код устарел с PHP 7.2.

Я отправлю обновленную версию этого кода (кредит за основной код взят из @bigwebguy, @Майк и @ 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 (и, надеюсь, с будущими версиями).

Можно использовать 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.

Ни один из существующих ответов не казался полным или безопасным, поэтому я придумал эту функцию, которая имеет следующие улучшения:

• работает как с безымянными (?), так и с именованными (: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-запрос.