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 compila previamente 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 normalmente utiliza declaraciones DML de SQL como INSERT , SELECT o UPDATE .
Un flujo de trabajo común para las 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 solo 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 conducir 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 se precompila y almacena en el servidor para su ejecución posterior, tiene ventajas similares. A diferencia de un procedimiento almacenado, una sentencia preparada normalmente no se escribe en un lenguaje de procedimiento y no puede usar o modificar variables o usar estructuras de flujo de control, confiando en cambio en el lenguaje de consulta de base de datos declarativa. Debido a su simplicidad y emulación del lado del cliente, las sentencias preparadas son más portables entre proveedores.
Los principales DBMS , incluidos SQLite , [5] MySQL , [6] Oracle , [7] IBM Db2 , [8] Microsoft SQL Server [9] y PostgreSQL [10] admiten sentencias preparadas. Las sentencias preparadas se ejecutan normalmente a través de un protocolo binario no SQL para lograr eficiencia y protección contra la inyección SQL, pero con algunos DBMS como MySQL, las sentencias preparadas también están disponibles utilizando una sintaxis SQL para fines de depuración. [11]
Varios lenguajes de programación admiten sentencias preparadas en sus bibliotecas estándar y las emularán en el lado del cliente incluso si el DBMS subyacente no las admite, incluidos JDBC de Java , [12] DBI de Perl , [13] PDO de PHP [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 generalmente es más lenta para consultas que se ejecutan 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]solo H2 admite esta característica. [15]
// Define un tipo BookModel que envuelve un grupo de conexiones sql.DB. type BookModel struct { DB * sql . DB } // Esto insertará un nuevo libro en la base de datos. func ( m * BookModel ) Insert ( title , author string ) ( int , error ) { stmt := "INSERT INTO book (title, author, created) VALUES(?, ?, UTC_TIMESTAMP())" result , err := m . DB . Exec ( stmt , title , author ) if err != nil { return 0 , err } id , err := result . LastInsertId () // No es compatible con el controlador de postgress si err != nil { return 0 , err } // El ID devuelto tiene el tipo int64, por lo que lo convertimos a un tipo int // antes de regresar. return int ( id ), nil } La sintaxis del parámetro de marcador de posición varía según la base de datos. MySQL, SQL Server y SQLite utilizan la notación ?, pero PostgreSQL utiliza la notación $N. Por ejemplo, si estuviera utilizando PostgreSQL en su lugar, escribiría:
_ , err := m . DB . Exec ( "INSERTAR EN ... VALORES ($1, $2, $3)" , ... ) 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 Main { public static void main ( String [] args ) lanza SQLException { MysqlDataSource ds = new MysqlDataSource (); ds . setDatabaseName ( "mysql" ); ds . setUser ( "root" ); try ( Connection conn = ds . getConnection ()) { try ( Statement stmt = conn . createStatement ()) { stmt .executeUpdate ( "CREAR TABLA SI NO EXISTE productos (nombre VARCHAR (40), precio INT)" ); } try ( PreparadStatement sentencia = conn.prepareStatement ( " INSERTAR EN productos VALORES (?, ?)" ) ) { sentencia.setString ( 1 , " bicicleta " ) ; sentencia.setInt ( 2 , 10900 ) ; sentencia.executeUpdate ( ) ; sentencia.setString ( 1 , " zapatos " ) ; sentencia.setInt ( 2 , 7400 ) ; sentencia.executeUpdate ( ) ; sentencia.setString ( 1 , " teléfono " ) ; sentencia.setInt ( 2 , 29500 ) ; sentencia.executeUpdate ( ) ; } try ( PreparedStatement stmt = conn . prepareStatement ( "SELECT * FROM productos WHERE nombre = ?" )) { stmt . setString ( 1 , "zapatos" ); ResultSet rs = stmt .executeQuery (); rs . next ( ); System . out . println ( rs . getInt ( 2 )); } } } } Java PreparedStatementproporciona "setters" ( setInt(int), setString(String), setDouble(double),etc.) para todos los principales tipos de datos integrados.
Este ejemplo utiliza PHP y PDO :
<?phptry { // Conectarse a una base de datos llamada "mysql", con la contraseña "root" $connection = new PDO ( 'mysql:dbname=mysql' , 'root' ); // Ejecutar una solicitud en la conexión, que creará // una tabla "productos" con dos columnas, "nombre" y "precio" $connection -> exec ( 'CREATE TABLE IF NOT EXISTS products (name VARCHAR(40), price INT)' ); // Preparar una consulta para insertar varios productos en la tabla $statement = $connection -> prepare ( 'INSERT INTO products VALUES (?, ?)' ); $products = [ [ 'bike' , 10900 ], [ 'shoes' , 7400 ], [ 'phone' , 29500 ], ]; // Iterar a través de los productos en la matriz "productos" y // ejecutar la declaración preparada para cada producto foreach ( $products as $product ) { $statement -> execute ( $product ); } // Preparar una nueva declaración con un parámetro nombrado $statement = $connection -> prepare ( 'SELECT * FROM products WHERE name = :name' ); $statement -> execute ([ ':name' => 'shoes' , ]); // Utilice la desestructuración de matrices para asignar el nombre del producto y su precio a las variables correspondientes [ $product , $price ] = $statement -> fetch (); // Mostrar el resultado al usuario echo "El precio del producto { $product } es \$ { $price } ." ; // Cierra el cursor para que `fetch` pueda eventualmente usarse nuevamente $statement -> closeCursor (); } catch ( \Exception $e ) { echo 'Ha ocurrido un error: ' . $e -> getMessage (); }Este ejemplo utiliza Perl y DBI :
#!/usr/bin/perl -w use strict ; use DBI ; mi ( $db_name , $db_user , $db_password ) = ( 'mi_base_de_datos' , 'yo' , 'Contraseña' ); mi $dbh = DBI -> connect ( "DBI:mysql:database=$db_name" , $db_user , $db_password , { RaiseError => 1 , AutoCommit => 1 }) o muere "ERROR (main:DBI->connect) al conectarse a la base de datos $db_name: " . $ DBI:: errstr . "\n" ; $dbh -> do ( 'CREAR TABLA SI NO EXISTE productos (nombre VARCHAR(40), precio INT)' );mi $sth = $dbh -> prepare ( 'INSERT INTO products VALUES (?, ?)' ); $sth -> execute ( @$_ ) foreach [ 'bicicleta' , 10900 ], [ 'zapatos' , 7400 ], [ 'teléfono' , 29500 ]; $sth = $dbh -> preparar ( "SELECT * FROM productos DONDE nombre = ?" ); $sth -> ejecutar ( 'zapatos' ); imprimir "$$_[1]\n" foreach $sth -> fetchrow_arrayref ; $sth -> finalizar ; $dbh -> desconectar ;Este ejemplo utiliza C# y ADO.NET :
usando ( comando SqlCommand = connection . CreateCommand ()) { comando . CommandText = "SELECT * FROM users WHERE USERNAME = @username AND ROOM = @room" ; comando . Parameters . AddWithValue ( "@username" , nombre de usuario ); comando . Parameters . AddWithValue ( "@room" , room ); usando ( SqlDataReader dataReader = comando . ExecuteReader ()) { // ... } } ADO.NET SqlCommandaceptará cualquier tipo para el valueparámetro de AddWithValue, y 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 se debe utilizar 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 se compila y almacena un plan de consulta diferente para cada longitud diferente. En general, la cantidad máxima de planes "duplicados" es el producto de las longitudes de las columnas de longitud variable según se especifica 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 Add 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 .connect ( base de datos = "mysql" , usuario = " root" ) como conn : con conn .cursor ( preparado = True ) como cursor : cursor .execute ( "CREAR TABLA SI NO EXISTE productos ( nombre VARCHAR(40), precio INT)" ) params = [( "bicicleta" , 10900 ) , ( "zapatos" , 7400 ), ( "teléfono" , 29500 )] cursor .executemany ( " INSERTAR EN productos VALORES ( %s , %s )" , params ) params = ( " zapatos" ,) cursor .execute ( " SELECCIONAR * DE productos DONDE nombre = %s " , params ) print ( cursor .fetchall ()[ 0 ][ 1 ]) Este ejemplo utiliza SQL directo de lenguajes 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 administrar 7 tipos de enlaces con los siguientes comandos
Establecer base de datos Blob , Establecer base de datos Doble , Establecer base de datos Flotante , Establecer base de datos Largo , Establecer base de datos Nulo , Establecer base de datos Cuádruple , Establecer base de datos Cadena
Hay 2 métodos diferentes según el tipo de base de datos.
Para SQLite , ODBC , MariaDB/Mysql utilice: ?
SetDatabaseString ( # BaseDeDatos , 0 , "prueba" ) Si DatabaseQuery ( #BaseDeDatos , "SELECT * FROM empleado DONDE id=?" ) ; ... EndIf Para utilizar 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 Si DatabaseQuery ( # Base de datos , "SELECT * FROM empleado DONDE id=$1 AND activo=$2 AND años>$3" ) ; ... FinSi