plan - ¿Cómo funciona la parametrización de consultas SQL?

plan de ejecución y optimización de consultas sql server 2008 r2

Question

Me siento un poco tonta por preguntar esto ya que parece que soy la única persona en el mundo que no lo entiende, pero aquí va de todos modos. Voy a usar Python como ejemplo. Cuando uso consultas de SQL sin procesar (normalmente uso ORM), uso parametrización, como en este ejemplo que usa SQLite:

Método A:

username = "wayne"
query_params = (username)
cursor.execute("SELECT * FROM mytable WHERE user=?", query_params)

Sé que esto funciona y sé que esta es la forma generalmente recomendada de hacerlo. Una forma vulnerable a la inyección de SQL para hacer lo mismo sería algo como esto:

Método B:

username = "wayne"
cursor.execute("SELECT * FROM mytable WHERE user=''%s''" % username)

Por lo que sé, entiendo que la inyección SQL, como se explica en este artículo de Wikipedia . Mi pregunta es simple: ¿en qué se diferencia el método A del método B? ¿Por qué el resultado final del método A no es el mismo que el método B? Supongo que el método cursor.execute() (parte de la especificación DB-API de Python) se encarga de que se escapen y cursor.execute() correctamente las entradas, pero esto nunca se establece explícitamente en ninguna parte. ¿Es eso todo lo que la parametrización en este contexto es? Para mí, cuando decimos "parametrización", todo lo que significa es "sustitución de cadenas", como% -formatting. ¿Eso es incorrecto?

Answer 1

Cuando envía una consulta a través de SQL Server, primero verifica el caché de procedimientos. Si encuentra alguna consulta EXACTAMENTE igual, entonces usará el mismo plan, y no volverá a compilar la consulta, solo reemplazará los marcadores de posición (variables) pero en el lado del servidor (db).

verifique la tabla del sistema master.dbo.syscacheobjects y realice algunas pruebas para aprender un poco más sobre este tema.

Answer 2

Cuando realiza la sustitución de texto (como su método B), debe tener cuidado con las comillas y demás, ya que el servidor obtendrá un único fragmento de texto y tendrá que determinar dónde termina el valor.

Con las declaraciones parametrizadas, OTOH, el servidor DB obtiene la declaración tal como está, sin el parámetro. El valor se envía al servidor como un dato diferente, utilizando un simple protocolo seguro binario. Por lo tanto, su programa no tiene que poner comillas alrededor del valor y, por supuesto, no importa si ya había comillas en el valor mismo.

Una analogía tiene que ver con el código fuente y el código compilado: en su método B, está creando el código fuente de un procedimiento, por lo que debe asegurarse de seguir estrictamente la sintaxis del idioma. Con el Método A, primero construye y compila un procedimiento, luego (inmediatamente después, en su ejemplo), llama a ese procedimiento con su valor como parámetro. Y, por supuesto, los valores en memoria no están sujetos a limitaciones de sintaxis.

Umm ... eso no fue realmente una analogía, es realmente lo que está sucediendo bajo el capó (aproximadamente).

Answer 3

El uso de consultas parametrizadas es una buena manera de aplacar la tarea para escapar y evitar inyecciones en la biblioteca del cliente de DB. Hará el escape antes de reemplazar la cadena con "?". Esto se hace en la biblioteca del cliente, antes del servidor DB.

Si tiene MySQL ejecutándose, active el registro SQL e intente algunas consultas parametrizadas, y verá que el servidor MySQL está recibiendo consultas totalmente sustituidas sin "?" en él, pero la biblioteca del cliente MySQL ya ha escapado de cualquier cita en su "parámetro" para usted.

Si usa el método B solo con el reemplazo de cadena, "s no se escapan automáticamente.

Sinergéticamente, con MySQL, puede preparar una consulta parametrizada con anticipación y luego usar la declaración preparada varias veces después. Cuando preparas una consulta, MySQL la analiza y te devuelve una declaración preparada: una cierta representación analizada que MySQL entiende. Cada vez que usa la declaración preparada, no solo está protegido contra la inyección, sino que también evita el costo de analizar la consulta nuevamente.

Y, si realmente desea estar seguro, puede modificar su capa de acceso a base de datos / ORM para que 1) el código del servidor web solo pueda usar declaraciones preparadas, y 2) solo puede preparar declaraciones antes de que se inicie su servidor web. Luego, incluso si su aplicación web está pirateada (por ejemplo, a través de un exploit de saturación de búfer), el pirata informático solo puede usar las declaraciones preparadas, pero nada más. Para ello, debe encarcelar su aplicación web y solo permitir el acceso a la base de datos a través de su capa de acceso / ORM DB.

Answer 4

Sólo una advertencia aquí. Esta ? la sintaxis funcionará bien y se escapa de las comillas simples o dobles de las cadenas correctamente.

Sin embargo encontré un caso donde no funciona. Tengo una columna que rastrea una cadena de versión de la forma "nnn", por ejemplo, "1.2.3" Parece que el formato causa un error porque parece un número real hasta el segundo ".". Por ejemplo:

   rec = (some_value, ''1.2.3'')
   sql = '''''' UPDATE some_table
              SET some_column=?
              WHERE version=? ''''''
    cur = self.conn.cursor()
    cur.execute(sql, rec)

Error con un error "Número incorrecto de enlaces suministrados. La declaración actual usa 1, y se han suministrado 2".

Esto funciona bien:

   vers = ''1.2.3''
   rec = (some_value)
   sql = '''''' UPDATE some_table
              SET some_column=?
              WHERE version=''%s'' '''''' % (vers)
    cur = self.conn.cursor()
    cur.execute(sql, rec)

Answer 5

Una consulta parametrizada en realidad no hace reemplazo de cadena. Si usa la sustitución de cadenas, entonces el motor SQL realmente ve una consulta que parece

SELECT * FROM mytable WHERE user=''wayne''

Si usas un ? parámetro, entonces el motor SQL ve una consulta que parece

SELECT * FROM mytable WHERE user=<some value>

Lo que significa que incluso antes de que vea la cadena "wayne", puede analizar completamente la consulta y comprender, en general, lo que hace la consulta. Pega "wayne" en su propia representación de la consulta, no en la cadena SQL que describe la consulta. Por lo tanto, la inyección de SQL es imposible, ya que ya hemos pasado la etapa SQL del proceso.

(Lo anterior está generalizado, pero más o menos transmite la idea).