Confusión entre enunciado preparado y consulta parametrizada en Python
database prepared-statement (3)
Primero, sus preguntas muestran muy buena preparación, bien hecho.
No estoy seguro, si soy la persona que debe proporcionar una respuesta autorizada, pero trataré de explicar mi comprensión de la situación.
La declaración preparada es un objeto, creado en el lado del servidor de base de datos como resultado de la instrucción PREPARE , que convierte la instrucción SQL proporcionada en una especie de procedimiento temporal con parámetros. La declaración preparada tiene una duración de la sesión actual de la base de datos y se desecha una vez que finaliza la sesión. La sentencia de SQL DEALOCATE permite destruir explícitamente la sentencia preparada.
Los clientes de la base de datos pueden usar la instrucción EXECUTE SQL para ejecutar la declaración preparada llamando a su nombre y parámetros.
La declaración parametrizada es un alias para la declaración preparada, ya que la declaración preparada tiene algunos parámetros.
La consulta parametrizada parece ser un alias de uso menos frecuente para el mismo (24 mil hits de Google para la declaración parametrizada, 14 mil hits para la consulta parametrizada). Es posible que algunas personas usen este término para otro propósito.
Las ventajas de las declaraciones preparadas son:
- ejecución más rápida de la llamada a la declaración preparada real (sin contar el tiempo de
PREPARE) - resistencia al ataque de inyección SQL
Jugadores en ejecución de consulta SQL
La aplicación real probablemente tendrá los siguientes participantes:
- código de aplicación
- Paquete ORM (por ejemplo, sqlalchemy)
- controlador de base de datos
- servidor de base de datos
Desde el punto de vista de la aplicación, no es fácil saber si el código realmente usará una declaración preparada en el servidor de la base de datos o no, ya que cualquiera de los participantes puede carecer de soporte para declaraciones preparadas .
Conclusiones
En el código de la aplicación, evite la configuración directa de la consulta SQL, ya que es propenso al ataque de inyección SQL. Por este motivo, se recomienda utilizar lo que ORM proporciona para la consulta parametrizada, incluso si no resulta en el uso de declaraciones preparadas en el lado del servidor de la base de datos, ya que el código ORM se puede optimizar para evitar este tipo de ataque.
Decidir, si la declaración preparada vale la pena por razones de rendimiento . Si tiene una consulta SQL simple, que se ejecuta solo unas pocas veces, no servirá de nada, en algún momento incluso ralentizará un poco la ejecución.
Para consultas complejas ejecutadas muchas veces y tener un tiempo de ejecución relativamente corto será el efecto más grande. En tal caso, puede seguir estos pasos:
- compruebe que la base de datos que va a utilizar admite la instrucción
PREPARE. En la mayoría de los casos estará presente. - compruebe que la unidad que utiliza es compatible con las declaraciones preparadas y, si no, intente encontrar otra que lo respalde.
- Verifique el soporte de esta característica en el nivel de paquete ORM. En algún momento varía controlador por controlador (por ejemplo, sqlalchemy establece algunas limitaciones en las declaraciones preparadas con MySQL debido a cómo MySQL administra eso).
Si estás buscando una verdadera respuesta autorizada, me dirigiría a los autores de sqlalchemy.
Según tengo entendido, las declaraciones preparadas son (principalmente) una función de base de datos que le permite separar los parámetros del código que utiliza dichos parámetros. Ejemplo:
PREPARE fooplan (int, text, bool, numeric) AS
INSERT INTO foo VALUES($1, $2, $3, $4);
EXECUTE fooplan(1, ''Hunter Valley'', ''t'', 200.00);
Una consulta parametrizada sustituye la interpolación de cadena manual, así que en lugar de hacerlo
cursor.execute("SELECT FROM tablename WHERE fieldname = %s" % value)
podemos hacer
cursor.execute("SELECT FROM tablename WHERE fieldname = %s", [value])
Ahora, parece que las declaraciones preparadas, en su mayor parte, se usan en el lenguaje de la base de datos y las consultas parametrizadas se usan principalmente en el lenguaje de programación que se conecta a la base de datos, aunque he visto excepciones a esta regla.
El problema es que preguntar acerca de la diferencia entre una declaración preparada y una consulta parametrizada genera mucha confusión. Su propósito es ciertamente el mismo, pero su metodología parece distinta. Sin embargo, hay sources que indican que ambos son iguales. MySQLdb y Psycopg2 parecen admitir consultas parametrizadas pero no son compatibles con declaraciones preparadas (por ejemplo, here para MySQLdb y en la lista TODO para controladores postgres o esta respuesta en el grupo sqlalchemy). En realidad, hay una idea general de la implementación de un cursor psycopg2 que admite declaraciones preparadas y una explanation mínima al respecto. También hay una suggestion de subclasificar el objeto del cursor en psycopg2 para proporcionar la declaración preparada manualmente.
Me gustaría obtener una respuesta autorizada a las siguientes preguntas:
¿Hay una diferencia significativa entre la declaración preparada y la consulta parametrizada? ¿Importa esto en la práctica? Si utiliza consultas parametrizadas, ¿necesita preocuparse por las declaraciones preparadas?
Si hay una diferencia, ¿cuál es el estado actual de las declaraciones preparadas en el ecosistema de Python? ¿Qué adaptadores de base de datos admiten declaraciones preparadas?
Una instrucción SQL no puede ejecutarse inmediatamente: el DBMS debe interpretarlos antes de la ejecución.
Las declaraciones preparadas ya se interpretan, los parámetros de cambio de DBMS y la consulta se inician de inmediato. Esta es una característica de ciertos DBMS y puede lograr una respuesta rápida (comparable con los procedimientos almacenados).
La declaración parametrizada es solo una forma de componer la cadena de consulta en sus lenguajes de programación. Ya que no importa cómo se formen las cadenas sql, tiene una respuesta más lenta por parte del DBMS.
Si mide el tiempo de ejecución 3-4 veces la misma consulta (seleccione con diferentes condiciones) verá el mismo tiempo con las consultas parametrizadas, el tiempo es menor desde la segunda ejecución de la declaración preparada (la primera vez que el DBMS tiene que interpretar el script). de todas formas).
Declaración preparada: una referencia a una rutina de consulta pre-interpretada en la base de datos, lista para aceptar parámetros
Consulta parametrizada: una consulta realizada por su código de tal manera que está pasando valores junto con algunos SQL que tienen valores de marcador de posición, por lo general
?o%so algo de ese sabor.
La confusión aquí parece derivarse de la (aparente) falta de distinción entre la capacidad de obtener directamente un objeto de declaración preparado y la capacidad de pasar valores a un método de "consulta parametrizada" que actúa como uno ... porque es uno , o al menos hace uno para ti.
Por ejemplo: la interfaz C de la biblioteca SQLite3 tiene muchas herramientas para trabajar con objetos de declaración preparados , pero la api de Python casi no hace mención de ellos. No puedes preparar una declaración y usarla varias veces cuando quieras. En su lugar, puede usar sqlite3.executemany(sql, params) que toma el código SQL, crea una declaración preparada internamente , luego usa esa declaración en un bucle para procesar cada una de las tuplas de parámetros en la iterable que dio.
Muchas otras bibliotecas SQL en Python se comportan de la misma manera. Trabajar con objetos de enunciado preparados puede ser un verdadero dolor, y puede llevar a la ambigüedad, y en un lenguaje como Python, que tiene tanta inclinación hacia la claridad y la facilidad con respecto a la velocidad de ejecución pura, no son realmente la mejor opción. Esencialmente, si tiene que hacer cientos de miles o millones de llamadas a una consulta SQL compleja que se reinterpreta cada vez, probablemente debería estar haciendo las cosas de manera diferente. En cualquier caso, a veces las personas desean poder tener acceso directo a estos objetos porque, si mantiene la misma declaración preparada en el servidor de la base de datos, no tendrá que seguir interpretando el mismo código SQL una y otra vez; la mayoría de las veces, el problema se abordará desde la dirección equivocada y obtendrá ahorros mucho mayores en otros lugares o mediante la reestructuración de su código. *
Quizás lo más importante en general es la forma en que las declaraciones preparadas y las consultas parametrizadas mantienen sus datos sanitarios y separados de su código SQL. ¡Esto es sumamente preferible al formato de cadena! Debe pensar en las consultas parametrizadas y en las declaraciones preparadas, de una forma u otra, como la única forma de pasar datos variables de su aplicación a la base de datos . Si intenta construir la declaración SQL de lo contrario, no solo se ejecutará significativamente más lento sino que también será vulnerable a otros problemas .
* por ejemplo, al generar los datos que se van a enviar a la base de datos en una función de generador y luego usar la executemany() para insertarlo todo de una vez desde el generador, en lugar de llamar a execute() cada vez que realiza un bucle.
tl; dr
Una consulta parametrizada es una única operación que genera una declaración preparada internamente, luego pasa sus parámetros y se ejecuta.