Solicitud preparada

En los sistemas de gestión de bases de datos, una consulta preparada o consulta parametrizada es la capacidad de un DBMS para precompilar código SQL separado de los datos [1] . Beneficios de las consultas preparadas:

Una declaración preparada es en realidad una plantilla precompilada que se sustituye con valores constantes durante cada ejecución, y es común usar declaraciones SQL DML como INSERT , SELECT o UPDATE .

La secuencia habitual para usar sentencias preparadas es:

INSERTAR EN productos (nombre, precio) VALORES (?, ?);

Una alternativa a una consulta preparada es llamar a SQL directamente desde el código fuente de la aplicación de una manera que combine código y datos. Equivalente directo al ejemplo anterior:

INSERTAR EN productos (nombre, precio) VALORES ("bicicleta", "10900");

No todas las optimizaciones se pueden realizar en el momento de la compilación de la plantilla de declaración por dos razones: el mejor plan de consulta puede depender de valores de parámetros específicos y el mejor plan de consulta puede cambiar con el tiempo debido a tablas e índices cambiantes [4] . Cuando y si una consulta preparada se ejecuta solo una vez, se ejecutará más lentamente debido al viaje de ida y vuelta adicional al servidor [5] . Las limitaciones de implementación también pueden conducir a una degradación del rendimiento; por ejemplo, algunas versiones de MySQL no almacenaron en caché los resultados de las consultas preparadas [6] . Los procedimientos almacenados , que también se precompilan y almacenan en el servidor para su ejecución posterior, ofrecen beneficios similares. A diferencia de los procedimientos almacenados, una consulta preparada generalmente no se escribe en un lenguaje de procedimiento y no puede usar ni modificar variables ni usar estructuras de flujo de control, sino que se basa en un lenguaje de consulta de base de datos declarativo. Debido a su simplicidad y la capacidad de emular en el lado del cliente (si el DBMS de destino no las admite), las consultas preparadas son más portátiles entre diferentes DBMS que los procedimientos almacenados.

Soporte de software

Casi todos los DBMS comunes , incluidos SQLite , [7] MySQL , [8] Oracle , [9] DB2 , [10] Microsoft SQL Server [11] y PostgreSQL [12] admiten consultas preparadas. Las consultas preparadas generalmente se invocan utilizando un protocolo binario especial que parece aumentar la velocidad de transferencia de datos y se supone que protege aún más contra la inyección de SQL, pero algunos DBMS, incluido, por ejemplo, MySQL, permiten, con fines de depuración, llamar consultas preparadas utilizando la sintaxis Consultas SQL [13] .

Muchos lenguajes de programación admiten consultas preparadas en sus bibliotecas estándar y las emulan para casos en los que el DBMS de destino no admite esta capacidad. Entre estos lenguajes se encuentran Java (usando JDBC [14] ), Perl (usando DBI (perl) [15] ), PHP (usando PDO [1] ) y Python (usando DB-API [16] ) . La emulación del lado del cliente puede ser más eficiente en términos de rendimiento para solicitudes únicas y menos eficiente para múltiples solicitudes. También ayuda contra las inyecciones de SQL, al igual que la implementación directa de consultas preparadas en el lado de DBMS [17] .

Ejemplos

Java JDBC

Este ejemplo usa Java y JDBC :

importar com.mysql.jdbc.jdbc2.opcional.MysqlDataSource ; importar java.sql.Conexión ; importar java.sql.DriverManager ; importar java.sql.PreparedStatement ; importar java.sql.ResultSet ; importar java.sql.SQLException ; importar java.sql.Statement ; clase pública Principal { public static void main ( String [] args ) lanza SQLException { MysqlDataSource ds = new MysqlDataSource (); ds . setDatabaseName ( "mysql" ); ds . setUsuario ( "raíz" ); try ( Conexión conn = ds . getConnection ()) { try ( Declaración stmt = conn . createStatement ()) { stmt . ejecutarActualizar ( "CREAR TABLA SI NO EXISTE productos (nombre VARCHAR(40), precio INT)" ); } try ( PreparadStatement stmt = conn . prepareStatement ( "INSERTAR EN LOS VALORES de los productos (?, ?)" )) { stmt . setString ( 1 , "bicicleta" ); sentencia _ setInt ( 2 , 10900 ); sentencia _ ejecutarActualizar (); sentencia _ setString ( 1 , "zapatos" ); sentencia _ setInt ( 2 , 7400 ); sentencia _ ejecutarActualizar (); sentencia _ setString ( 1 , "teléfono" ); sentencia _ setInt ( 2 , 29500 ); sentencia _ ejecutarActualizar (); } try ( PreparadStatement stmt = conn . prepareStatement ( "SELECT * FROM productos DONDE nombre =?" )) { stmt . setString ( 1 , "zapatos" ); Conjunto de resultados rs = sentencia . ejecutar consulta (); rs . siguiente (); sistema _ fuera _ println ( rs . getInt ( 2 )); } } } }

Java PreparedStatementproporciona "establecedores" ( setInt(int), setString(String), setDouble(double),etc.) para todos los principales tipos de datos integrados.

PHP DOP

Este ejemplo usa PHP y PDO :

<?php intente { // Conéctese a una base de datos llamada "mysql", con la contraseña "root" $conexión = new PDO ( 'mysql:dbname=mysql' , 'root' ); // Ejecutar una solicitud en la conexión, que creará // una tabla "productos" con dos columnas, "nombre" y "precio" $conexión -> exec ( 'CREATE TABLE IF NOT EXISTS products (name VARCHAR(40), precio INT)' ); // Preparar una consulta para insertar múltiples productos en la tabla $estado de cuenta = $conexión -> preparar ( 'INSERT INTO products VALUES (?, ?)' ); $productos = [ [ 'bicicleta' , 10900 ], [ 'zapatos' , 7400 ], [ 'teléfono' , 29500 ], ]; // Iterar a través de los productos en la matriz "products" y // ejecutar la instrucción preparada para cada producto foreach ( $productos as $producto ) { $instrucción -> ejecutar ( $producto ); } // Preparar una nueva instrucción con un parámetro con nombre $instrucción = $conexión -> preparar ( 'SELECT * FROM productos WHERE nombre =: nombre' ); $sentencia -> ejecutar ([ ':nombre' => 'zapatos' , ]); // Use la desestructuración de matrices para asignar el nombre del producto y su precio // a las variables correspondientes [ $producto , $precio ] = $estado de cuenta -> buscar (); // Mostrar el resultado al usuario echo "El precio del producto { $producto } es \$ { $precio } ." ; // Cierra el cursor para que `fetch` se pueda usar de nuevo $statement -> closeCursor (); } catch ( \Exception $e ) { echo 'Ha ocurrido un error: ' . $e -> obtenerMensaje (); }

Perl DBI

Este ejemplo usa Perl y DBI :

#!/usr/bin/perl -w uso estricto ; utilizar DBI ; my ( $ db_name , $db_user , $db_password ) = ( 'my_database' , 'moi' , 'Passw0rD' ); mi $dbh = DBI -> conectar ( "DBI:mysql:database=$db_name" , $db_user , $db_password , { RaiseError => 1 , AutoCommit => 1 }) o morir "ERROR (main:DBI->connect) mientras se conecta a la base de datos $db_name: " . $ DBI:: errstr . "\n" ; $dbh -> do ( 'CREAR TABLA SI NO EXISTE productos (nombre VARCHAR(40), precio INT)' ); my $sth = $dbh -> prepare ( 'INSERTAR EN LOS VALORES DE LOS PRODUCTOS (?, ?)' ); $sth -> ejecutar ( $_ ) foreach [ 'bicicleta' , 10900 ], [ 'zapatos' , 7400 ], [ 'teléfono' , 29500 ]; $sth = $dbh -> prepare ( "SELECCIONE * DE productos DONDE nombre =?" ); $sth -> ejecutar ( 'zapatos' ); imprimir "$$_[1]\n" foreach $sth -> fetchrow_arrayref ; $algo -> terminar ; $dbh -> desconectar ;

C# ADO.NET

Este ejemplo usa C# y ADO.NET :

usando ( comando SqlCommand = conexión . CreateCommand ()) { comando . CommandText = "SELECT * FROM usuarios WHERE USERNAME = @username AND ROOM = @room" ; mando _ Parámetros . AddWithValue ( "@nombre de usuario" , nombre de usuario ); mando _ Parámetros . AddWithValue ( "@habitación" , habitación ); usando ( SqlDataReader dataReader = comando . ExecuteReader ()) { // ... } }

Python DB-API

Este ejemplo usa Python y DB-API:

importar mysql.conector con mysql . conector _ conectar ( base de datos = "mysql" , usuario = "raíz" ) como conn : con conn . cursor ( preparado = True ) como cursor : cursor . ejecutar ( "CREAR TABLA SI NO EXISTE productos (nombre VARCHAR (40), precio INT)" ) params = [( "bicicleta" , 10900 ), ( "zapatos" , 7400 ), ( "teléfono" , 29500 )] cursor . ejecutar muchos ( "INSERTAR EN LOS VALORES DE LOS PRODUCTOS ( %s , %s )" , params ) params = ( "zapatos" ,) cursor . ejecutar ( "SELECCIONAR * DE productos DONDE nombre = %s " , parámetros ) imprimir ( cursor . fetchall ()[ 0 ][ 1 ])

Notas

  1. ↑ 1 2 El grupo de documentación de PHP Sentencias preparadas y procedimientos almacenados . Manual PHP . Consultado el 25 de septiembre de 2011. Archivado desde el original el 8 de abril de 2022.
  2. ↑ Shuping Ran, Doug Palmer, Paul Brebner, Shiping Chen, Ian Gorton, Jeffrey Gosper, Lei Hu, Anna Liu y Phong Tran. METODOLOGÍA DE EVALUACIÓN DEL DESEMPEÑO DE LA TECNOLOGÍA J2EE . citeseerx.ist.psu.edu . Consultado el 15 de abril de 2022. Archivado desde el original el 15 de abril de 2022.
  3. ↑ Stephen Thomas, Laurie Williams, Tao Xie. Sobre la generación automatizada de declaraciones preparadas para eliminar vulnerabilidades de inyección de SQL  //  Tecnología de la información y el software. — 2009-03-01. — vol. 51 , edición. 3 . — pág. 589–598 . — ISSN 0950-5849 . -doi : 10.1016/ j.infsof.2008.08.002 . Archivado desde el original el 9 de mayo de 2012.
  4. ↑ Petrunia, Sergey MySQL Optimizer y declaraciones preparadas . Blog de Sergey Petrunia (28 de abril de 2007). Consultado el 25 de septiembre de 2011. Archivado desde el original el 5 de febrero de 2018.
  5. ↑ Zaitsev, Peter Declaraciones preparadas de MySQL . Blog de rendimiento de MySQL (2 de agosto de 2006). Consultado el 25 de septiembre de 2011. Archivado desde el original el 23 de marzo de 2014.
  6. ↑ 7.6.3.1. Cómo funciona la caché de consultas . Manual de referencia de MySQL 5.1 . Oráculo. Consultado el 26 de septiembre de 2011. Archivado desde el original el 25 de septiembre de 2011.
  7. ↑ Objetos de declaraciones preparadas . SQLite (18 de octubre de 2021). Consultado el 9 de abril de 2022. Archivado desde el original el 7 de mayo de 2022.
  8. ↑ Oráculo 20.9.4. Declaraciones preparadas de la API de C. Manual de referencia de MySQL 5.5 . Consultado el 27 de marzo de 2012. Archivado desde el original el 30 de junio de 2017.
  9. ↑ 13 SQL dinámico de Oracle . Guía del programador del precompilador Pro*C/C++, versión 9.2 . Oráculo. Consultado el 25 de septiembre de 2011. Archivado desde el original el 26 de octubre de 2011.
  10. ↑ Uso de las sentencias PREPARE y EXECUTE . Centro de información de i5/OS, Versión 5 Release 4 . IBM. Recuperado: 25 de septiembre de 2011.  (enlace inaccesible)
  11. ↑ SQL Server 2008 R2: Preparación de sentencias SQL . Biblioteca MSDN . Microsoft. Consultado el 25 de septiembre de 2011. Archivado desde el original el 5 de julio de 2017.
  12. ↑ PREPARAR . Documentación de PostgreSQL 9.5.1 . Grupo de desarrollo global de PostgreSQL. Consultado el 27 de febrero de 2016. Archivado desde el original el 9 de marzo de 2018.
  13. ↑ Oráculo 12.6. Sintaxis SQL para sentencias preparadas . Manual de referencia de MySQL 5.5 . Consultado el 27 de marzo de 2012. Archivado desde el original el 16 de julio de 2019.
  14. ↑ Uso de declaraciones preparadas . Los tutoriales de Java . Oráculo. Consultado el 25 de septiembre de 2011. Archivado desde el original el 12 de noviembre de 2011.
  15. ↑ Bunce, especificación Tim DBI-1.616 . CPAN . Consultado: 26 de septiembre de 2011.
  16. ↑ Python PEP 289: Especificación de la API de la base de datos de Python v2.0 . Consultado el 9 de abril de 2022. Archivado desde el original el 3 de marzo de 2022.
  17. ↑ Anikin Evgeny Alexandrovich. Inyección SQL y cómo protegerse del acceso no autorizado  // CONTINUO. MATEMÁTICAS. INFORMÁTICA. EDUCACIÓN. - 2016. - Nº 4 . — ISSN 2500-1957 .