procedimientos - sentencias preparadas php mysqli
Obtener la cadena de consulta SQL sin procesar de las declaraciones preparadas con PDO (14)
¿Hay alguna manera de ejecutar la cadena de SQL sin procesar al llamar a PDOStatement :: execute () en una declaración preparada? Para fines de depuración esto sería extremadamente útil.
Algo relacionado ... si solo estás tratando de desinfectar una variable en particular, puedes usar PDO::quote . Por ejemplo, para buscar múltiples condiciones LIKE parciales si está atrapado con un marco limitado como CakePHP:
$pdo = $this->getDataSource()->getConnection();
$results = $this->find(''all'', array(
''conditions'' => array(
''Model.name LIKE '' . $pdo->quote("%{$keyword1}%"),
''Model.name LIKE '' . $pdo->quote("%{$keyword2}%"),
),
);
La propiedad $ queryString mencionada probablemente solo devuelva la consulta pasada, sin que los parámetros sean reemplazados por sus valores. En .Net, tengo la parte catch de mi query executer hacer una búsqueda simple replace en los parámetros con sus valores que fueron suministrados para que el log de errores pueda mostrar los valores reales que se estaban usando para la consulta. Debería poder enumerar los parámetros en PHP y reemplazar los parámetros con su valor asignado.
Modifiqué el método para incluir el manejo de la salida de las matrices para declaraciones como WHERE IN (?).
ACTUALIZACIÓN: acaba de agregar verificación para el valor NULO y $ params duplicados, por lo que los valores reales de $ param no se modifican.
Gran trabajo bigwebguy y gracias!
/**
* 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;
}
Necesito registrar la cadena de consulta completa después de bind param así que esta es una pieza en mi código. Espero, es útil para todos que el sombrero tenga el mismo problema.
/**
*
* @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 tiene una propiedad pública $ queryString. Debe ser lo que quieras.
Me acabo de dar cuenta de que PDOStatement tiene un método no documentado debugDumpParams () que también puede consultar.
Pasé mucho tiempo investigando esta situación para mis propias necesidades. Este y otros hilos SO me ayudaron mucho, así que quería compartir lo que se me ocurrió.
Si bien tener acceso a la cadena de consulta interpolada es un beneficio significativo durante la resolución de problemas, queríamos poder mantener un registro de solo ciertas consultas (por lo tanto, el uso de los registros de la base de datos para este propósito no era ideal). También queríamos poder usar los registros para recrear la condición de las tablas en un momento dado, por lo tanto, necesitábamos asegurarnos de que las cadenas interpoladas se escaparon correctamente. Finalmente, queríamos extender esta funcionalidad a toda nuestra base de códigos teniendo que volver a escribir lo menos posible (fechas límite, marketing, etc., ya sabe cómo es).
Mi solución fue ampliar la funcionalidad del objeto predeterminado PDOStatement para almacenar en caché los valores parametrizados (o referencias), y cuando se ejecuta la instrucción, utilice la funcionalidad del objeto PDO para escapar adecuadamente los parámetros cuando se vuelvan a inyectar en la consulta cuerda. Podríamos enlazar para ejecutar el método del objeto de declaración y registrar la consulta real que se ejecutó en ese momento ( o al menos tan fiel como sea posible de una reproducción) .
Como dije, no queríamos modificar todo el código base para agregar esta funcionalidad, por lo que sobrescribimos los bindParam() y bindValue() predeterminados del objeto PDOStatement, hacemos el almacenamiento en caché de los datos encuadernados y luego llamamos al parent::bindParam() o parent :: bindValue() . Esto permitió que nuestra base de códigos existente siguiera funcionando normalmente.
Finalmente, cuando se llama al método execute() , realizamos nuestra interpolación y proporcionamos la cadena resultante como una propiedad nueva E_PDOStatement->fullQuery . Esto se puede mostrar para ver la consulta o, por ejemplo, escribir en un archivo de registro.
La extensión, junto con las instrucciones de instalación y configuración, están disponibles en github:
https://github.com/noahheck/E_PDOStatement
DESCARGO DE RESPONSABILIDAD :
Obviamente, como mencioné, escribí esta extensión. Debido a que fue desarrollado con la ayuda de muchos hilos aquí, quería publicar mi solución aquí en caso de que alguien más se encuentre con estos hilos, al igual que yo.
Puede extender PDOStatement class para capturar las variables limitadas y almacenarlas para usarlas en el futuro. Luego se pueden agregar 2 métodos, uno para desinfectar variables (debugBindedVariables) y otro para imprimir la consulta con esas variables (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);
}
}
Y luego puedes usar esta clase heredada para depurar objetivos.
$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());
Resultando en
SELECCIONAR usuario FROM usuarios DONDE user = ''user_test''
Array ([: test] => user_test)
Se agregó un poco más al código de Mike: recorra los valores para agregar comillas simples
/**
* 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;
}
Un poco tarde probablemente, pero ahora hay PDOStatement::debugDumpParams
Vuelca la información contenida en una declaración preparada directamente en el resultado. Proporcionará la consulta SQL en uso, el número de parámetros utilizados (Params), la lista de parámetros, con su nombre, tipo (paramtype) como un entero, su nombre o posición clave, y la posición en la consulta (si esto es compatible con el controlador PDO; de lo contrario, será -1).
Puede encontrar más información sobre los documentos php oficiales
Ejemplo:
<?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();
?>
Una solución es voluntariamente poner un error en la consulta e imprimir el mensaje del error:
//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();
}
Salida estándar:
SQLSTATE [42000]: error de sintaxis o violación de acceso: [...] cerca de ''ELECT * FROM Persona DONDE edad = 18'' en la línea 1
Es importante tener en cuenta que solo imprime los primeros 80 caracteres de la consulta.
preg_replace no funcionó para mí y cuando binding_ fue más de 9, binding_1 y binding_10 se reemplazaron por str_replace (dejando el 0 atrás), así que hice los reemplazos al revés:
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;
}
Espero que alguien lo encuentre útil.
La respuesta de Mike funciona bien hasta que esté utilizando el valor de vinculación "reutilizar".
Por ejemplo:
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)
La respuesta de Mike solo puede reemplazar primero: buscar pero no el segundo.
Por lo tanto, reescribo su respuesta para trabajar con múltiples parámetros que pueden reutilizarse correctamente.
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;
}
Supongo que quiere decir que quiere la consulta SQL final, con los valores de los parámetros interpolados en ella. Entiendo que esto sería útil para la depuración, pero no es la forma en que funcionan las declaraciones preparadas. Los parámetros no se combinan con una declaración preparada en el lado del cliente, por lo que PDO nunca debe tener acceso a la cadena de consulta combinada con sus parámetros.
La instrucción SQL se envía al servidor de la base de datos cuando se prepara (), y los parámetros se envían por separado cuando se ejecuta (). El registro de consultas generales de MySQL muestra el SQL final con valores interpolados después de ejecutar (). A continuación se muestra un extracto de mi registro general de consultas. Ejecuté las consultas de la CLI de mysql, no de PDO, pero el principio es el mismo.
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
También puede obtener lo que desea si establece el atributo PDO PDO :: ATTR_EMULATE_PREPARES. En este modo, PDO interpola parámetros en la consulta SQL y envía la consulta completa cuando ejecuta (). Esta no es una verdadera consulta preparada. Evitará los beneficios de consultas preparadas al interpolar variables en la cadena de SQL antes de ejecutar ().
Comentario de @afilina:
No, la consulta SQL textual no se combina con los parámetros durante la ejecución. Entonces, no hay nada que PDO pueda mostrarle.
Internamente, si usa PDO :: ATTR_EMULATE_PREPARES, PDO realiza una copia de la consulta SQL e interpola los valores de los parámetros en ella antes de preparar y ejecutar. Pero PDO no expone esta consulta SQL modificada.
El objeto PDOStatement tiene una propiedad $ queryString, pero está establecido solo en el constructor para PDOStatement, y no se actualiza cuando la consulta se reescribe con parámetros.
Sería una solicitud de función razonable para PDO pedirles que expongan la consulta reescrita. Pero incluso eso no le daría la consulta "completa" a menos que use PDO :: ATTR_EMULATE_PREPARES.
Esta es la razón por la que muestro la solución anterior de usar el registro general de consultas del servidor MySQL, porque en este caso incluso una consulta preparada con marcadores de posición de parámetros se reescribe en el servidor, con los valores de parámetros rellenos en la cadena de consulta. Pero esto solo se hace durante el registro, no durante la ejecución de la consulta.
/**
* 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;
}