En los sistemas de gestión de bases de datos (DBMS), una declaración preparada , una declaración parametrizada o una consulta parametrizada es una característica en la que la base de datos precompila el código SQL y almacena los resultados, separándolos de los datos. Los beneficios de las declaraciones preparadas son: [1]
Una declaración preparada toma la forma de una plantilla precompilada en la que se sustituyen valores constantes durante cada ejecución y, por lo general, utiliza declaraciones SQL DML como INSERT , SELECT o UPDATE .
Un flujo de trabajo común para declaraciones preparadas es:
INSERT INTO products (name, price) VALUES (?, ?);La alternativa a una declaración preparada es llamar a SQL directamente desde el código fuente de la aplicación de una manera que combine código y datos. El equivalente directo al ejemplo anterior es:
INSERTAR EN productos ( nombre , precio ) VALORES ( 'bicicleta' , '10900' ); No toda la optimización se puede realizar en el momento en que se compila la plantilla de declaración, por dos razones: el mejor plan puede depender de los valores específicos de los parámetros y el mejor plan puede cambiar a medida que las tablas y los índices cambian con el tiempo. [2]
Por otro lado, si una consulta se ejecuta sólo una vez, las sentencias preparadas del lado del servidor pueden ser más lentas debido al viaje de ida y vuelta adicional al servidor. [3] Las limitaciones de implementación también pueden dar lugar a penalizaciones de rendimiento; por ejemplo, algunas versiones de MySQL no almacenaban en caché los resultados de las consultas preparadas. [4] Un procedimiento almacenado , que también está precompilado y almacenado en el servidor para su posterior ejecución, tiene ventajas similares. A diferencia de un procedimiento almacenado, una declaración preparada normalmente no se escribe en un lenguaje de procedimiento y no puede usar ni modificar variables ni usar estructuras de flujo de control, sino que depende del lenguaje de consulta de base de datos declarativa. Debido a su simplicidad y emulación del lado del cliente, las declaraciones preparadas son más portátiles entre proveedores.
Los principales DBMS , incluidos SQLite , [5] MySQL , [6] Oracle , [7] IBM Db2 , [8] Microsoft SQL Server [9] y PostgreSQL [10] admiten declaraciones preparadas. Las declaraciones preparadas normalmente se ejecutan a través de un protocolo binario que no es SQL para mayor eficiencia y protección contra la inyección de SQL, pero con algunos DBMS, como MySQL, las declaraciones preparadas también están disponibles utilizando una sintaxis SQL para fines de depuración. [11]
Varios lenguajes de programación admiten declaraciones preparadas en sus bibliotecas estándar y las emularán en el lado del cliente incluso si el DBMS subyacente no las admite, incluido JDBC de Java , [ 12] DBI de Perl , [13] PHP PDO [1] y DB-API de Python . [14] La emulación del lado del cliente puede ser más rápida para consultas que se ejecutan solo una vez, al reducir el número de viajes de ida y vuelta al servidor, pero suele ser más lenta para consultas ejecutadas muchas veces. Resiste ataques de inyección SQL con la misma eficacia.
Muchos tipos de ataques de inyección SQL se pueden eliminar deshabilitando los literales , lo que requiere efectivamente el uso de declaraciones preparadas; A partir de 2007, [actualizar]sólo H2 admite esta función. [15]
Este ejemplo utiliza Java y JDBC :
importar com.mysql.jdbc.jdbc2.optional.MysqlDataSource ; importar java.sql.Connection ; 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 . ejecutarUpdate ( "CREAR TABLA SI NO EXISTE productos (nombre VARCHAR(40), precio INT)" ); } try ( PreparedStatement stmt = conn . prepareStatement ( "INSERTAR EN PRODUCTOS VALORES (?, ?)" )) { stmt . setString ( 1 , "bicicleta" ); stmt . establecerInt ( 2 , 10900 ); stmt . ejecutarActualización (); stmt . setString ( 1 , "zapatos" ); stmt . establecerInt ( 2 , 7400 ); stmt . ejecutarActualización (); stmt . setString ( 1 , "teléfono" ); stmt . establecerInt ( 2 , 29500 ); stmt . ejecutarActualización (); } try ( PreparadStatement stmt = conn . prepareStatement ( "SELECCIONAR * DE productos DONDE nombre =?" )) { stmt . setString ( 1 , "zapatos" ); Conjunto de resultados rs = stmt . ejecutar la solicitud (); rs . próximo (); Sistema . afuera . println ( rs.getInt ( 2 ) ) ; } } } } Java PreparedStatementproporciona "configuradores" ( setInt(int), setString(String), setDouble(double),etc.) para todos los principales tipos de datos integrados.
Este ejemplo utiliza PHP y PDO :
<?phptry { // Conéctese a una base de datos llamada "mysql", con la contraseña "root" $conexión = nuevo 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 ( 'CREAR TABLA SI NO EXISTE productos (nombre VARCHAR(40), precio INT)' ); // Prepare una consulta para insertar varios productos en la tabla $statement = $connection -> prepare ( 'INSERT INTO productos VALUES (?, ?)' ); $productos = [ [ 'bicicleta' , 10900 ], [ 'zapatos' , 7400 ], [ 'teléfono' , 29500 ], ]; // Iterar a través de los productos en la matriz "productos" y // ejecutar la declaración preparada para cada producto foreach ( $productos como $producto ) { $declaración -> ejecutar ( $producto ); } // Preparar una nueva declaración con un parámetro con nombre $declaración = $conexión -> preparar ( 'SELECCIONAR * DE productos DONDE nombre = :nombre' ); $sentencia -> ejecutar ([ ':nombre' => 'zapatos' , ]); // Utilice la desestructuración de matrices para asignar el nombre del producto y su precio // a las variables correspondientes [ $producto , $precio ] = $declaración -> fetch (); // Muestra el resultado al usuario echo "El precio del producto { $producto } es \$ { $precio } ". ; // Cierra el cursor para que `fetch` pueda usarse nuevamente $statement -> closeCursor (); } catch ( \Exception $e ) { echo 'Se ha producido un error:' . $e -> obtenerMensaje (); }Este ejemplo utiliza Perl y DBI :
#!/usr/bin/perl -w uso estricto ; utilizar DBI ; my ( $db_name , $db_user , $db_password ) = ( 'my_database' , 'moi' , 'Contraseña0rD' ); mi $dbh = DBI -> conectar ( "DBI:mysql:database=$db_name" , $db_user , $db_password , { RaiseError => 1 , AutoCommit => 1 }) o morir "ERROR (principal:DBI->connect) mientras se conecta a la base de datos $db_name: " . $ DBI:: errorstr . "\n" ; $dbh -> do ( 'CREAR TABLA SI NO EXISTE productos (nombre VARCHAR(40), precio INT)' );my $sth = $dbh -> preparar ( 'INSERTAR EN PRODUCTOS VALORES (?, ?)' ); $ algo -> ejecutar ( @$_ ) foreach [ 'bicicleta' , 10900 ], [ 'zapatos' , 7400 ], [ 'teléfono' , 29500 ]; $sth = $dbh -> preparar ( "SELECCIONAR * DE productos DONDE nombre =?" ); $algo -> ejecutar ( 'zapatos' ); imprimir "$$_[1]\n" foreach $algo -> fetchrow_arrayref ; $algo -> terminar ; $dbh -> desconectar ;Este ejemplo utiliza C# y ADO.NET :
usando ( comando SqlCommand = conexión . CreateCommand ()) { comando . CommandText = "SELECCIONAR * DE los usuarios DONDE NOMBRE DE USUARIO = @nombredeusuario Y HABITACIÓN = @habitación" ; dominio . Parámetros . AddWithValue ( "@nombredeusuario" , nombre de usuario ); dominio . Parámetros . AddWithValue ( "@habitación" , habitación ); usando ( SqlDataReader dataReader = comando . ExecuteReader ()) { // ... } } ADO.NET SqlCommandaceptará cualquier tipo para el valueparámetro de AddWithValuey la conversión de tipos se produce automáticamente. Tenga en cuenta el uso de "parámetros con nombre" (es decir "@username"), en lugar de "?": esto le permite utilizar un parámetro varias veces y en cualquier orden arbitrario dentro del texto del comando de consulta.
Sin embargo, el método AddWithValue no debe usarse con tipos de datos de longitud variable, como varchar y nvarchar. Esto se debe a que .NET supone que la longitud del parámetro es la longitud del valor dado, en lugar de obtener la longitud real de la base de datos mediante reflexión. La consecuencia de esto es que para cada longitud diferente se compila y almacena un plan de consulta diferente. En general, el número máximo de planes "duplicados" es el producto de las longitudes de las columnas de longitud variable especificadas en la base de datos. Por este motivo, es importante utilizar el método Add estándar para columnas de longitud variable:
command.Parameters.Add(ParamName, VarChar, ParamLength).Value = ParamValue, donde ParamLength es la longitud especificada en la base de datos.
Dado que el método Agregar estándar debe usarse para tipos de datos de longitud variable, es un buen hábito usarlo para todos los tipos de parámetros.
Este ejemplo utiliza Python y DB-API:
importar mysql.connectorcon mysql . conector . conectar ( base de datos = "mysql" , usuario = "raíz" ) como conn : con conn . cursor ( preparado = Verdadero ) como cursor : cursor . ejecutar ( "CREAR TABLA SI NO EXISTE productos (nombre VARCHAR(40), precio INT)" ) params = [( "bicicleta" , 10900 ), ( "zapatos" , 7400 ), ( "teléfono" , 29500 )] cursor . ejecutarmany ( "INSERTAR VALORES EN LOS PRODUCTOS ( %s , %s )" , params ) params = ( "zapatos" ,) cursor . ejecutar ( "SELECCIONAR * DE productos DONDE nombre = %s " , parámetros ) imprimir ( cursor . fetchall () [ 0 ] [ 1 ])Este ejemplo utiliza Direct SQL de un lenguaje de cuarta generación como eDeveloper, uniPaaS y magic XPA de Magic Software Enterprises.
Nombre de usuario virtual Alpha 20 init: 'hermana'Contraseña virtual Alpha 20 init: 'amarillo'Comando SQL: SELECT * FROM users WHERE USERNAME=:1 AND PASSWORD=:2Argumentos de entrada:1: nombre de usuario2: contraseñaPureBasic (desde v5.40 LTS) puede gestionar 7 tipos de enlace con los siguientes comandos
SetDatabase Blob , SetDatabase doble , SetDatabase flotante , SetDatabase largo , SetDatabase nulo , SetDatabase cuádruple , SetDatabase cadena
Existen 2 métodos diferentes según el tipo de base de datos.
Para SQLite , ODBC , MariaDB/Mysql utilice:?
SetDatabaseString ( # Base de datos , 0 , "prueba" ) If DatabaseQuery ( # Base de datos , "SELECCIONAR * DEL empleado DONDE id=?" ) ; ... Terminara si Para uso de PostgreSQL : $1, $2, $3, ...
SetDatabaseString ( # Base de datos , 0 , "Smith" ) ; -> $1 SetDatabaseString ( # Base de datos , 1 , "Sí" ) ; -> $2 SetDatabaseLong ( # Base de datos , 2 , 50 ) ; -> $3 If DatabaseQuery ( # Base de datos , "SELECCIONAR * DEL empleado DONDE id=$1 Y activo=$2 Y años>$3" ) ; ... Terminara si