Primeros pasos con la parametrización de consultas

Cuando las aplicaciones web dependen de datos proporcionados por los usuarios, existe el riesgo de sufrir ataques de inyección SQL. La inyección SQL es una técnica que se usa para alterar una instrucción SQL mediante la manipulación de la entrada del usuario. En concreto, los atacantes envían código SQL malicioso a la base de datos para eludir las medidas de seguridad y obtener acceso no autorizado a los datos. Las consecuencias pueden ser graves: desde la filtración de información confidencial hasta la pérdida total del sistema.  

Hay varias formas de prevenir un ataque de inyección SQL, y una de las principales es la parametrización de consultas SQL. Esto consiste en separar el código SQL de los valores ingresados por el usuario mediante marcadores de posición (parámetros), para que la entrada se trate como datos y no como código SQL. Lo importante es usar instrucciones preparadas con consultas parametrizadas: una combinación sumamente beneficiosa.

Para agregar una capa adicional de seguridad, podemos usar procedimientos almacenados parametrizados. Los procedimientos almacenados son instrucciones SQL precompiladas que se guardan en la base de datos. Podemos otorgar permisos para ejecutarlos y, al mismo tiempo, restringir el acceso directo a la tabla subyacente. Cuando queramos usar un procedimiento, le pasamos los parámetros que espera desde nuestra aplicación.

En este artículo práctico, veremos cómo aprovechar la parametrización de consultas SQL y los procedimientos almacenados para prevenir ataques de inyección, además de algunas medidas de seguridad adicionales que ayudan a proteger nuestro código.

Implementar la parametrización de consultas

Para implementar la parametrización de consultas, se usan marcadores de posición en las consultas SQL para representar la entrada proporcionada por el usuario. Estos marcadores son símbolos temporales que el motor SQL completa con los valores reales de las variables. Al ejecutar la consulta, el motor SQL vincula las variables reales con los marcadores antes de ejecutarla, lo que garantiza que las variables se traten como datos literales.

Puedes usar la parametrización de consultas con o sin instrucciones preparadas. La diferencia entre estos dos enfoques es que las instrucciones preparadas se precompilan y puedes ejecutarlas varias veces con distintos parámetros. 

Más importante aún, usar instrucciones preparadas puede mejorar la parametrización de las consultas, ya que la base de datos garantiza que la instrucción se precompile, lo que reduce el tiempo de ejecución. Además, como el plan de ejecución ya está creado, los parámetros no influyen en él, lo que beneficia tu postura de seguridad.

Si necesitas ejecutar una consulta varias veces, la base de datos no tendrá que volver a compilarla: solo tienes que pasar los parámetros que quieras usar a la instrucción preparada. Además, el uso de parámetros vinculados reduce el ancho de banda, ya que solo se envían los parámetros al servidor, no toda la consulta. 

En las siguientes secciones, veremos cómo implementar la parametrización de consultas en una aplicación web.

Requisitos previos

Para seguir esta demostración, asegúrate de contar con lo siguiente:

• Conocimientos prácticos de .NET

• Conocimientos prácticos para escribir consultas SQL.

• Visual Studio con la extensión de Snyk instalada

Parametrización de consultas sin instrucciones preparadas 

Para esta demostración, trabajaremos con una aplicación de consola .NET que acepta la entrada del usuario desde la línea de comandos y luego usa esa entrada como uno de los valores de una instrucción de consulta SQL.

Primero, veamos cómo parametrizar la consulta sin usar una instrucción preparada. 

Con el código que aparece a continuación, obtén el nombre de usuario con `Console.Readline()` y luego define la consulta. En lugar de concatenar el nombre de usuario a la consulta, usa un marcador de posición que comienza con el carácter `@`. Más adelante, usaremos el método `AddWithValue()` para vincular el nombre de usuario a la consulta antes de ejecutarla:

1try
2{
3    string? username = Console.ReadLine();
4    string query = "INSERT INTO Users (pk_id, name) VALUES (@id, @username)";
5    using (SqlConnection connection = new SqlConnection(connectionString))
6    {
7        connection.Open();
8        SqlCommand command = new SqlCommand(query, connection);
9
10        // Bind the SQL parameters.
11        command.Parameters.AddWithValue("@username", username);
12        command.Parameters.AddWithValue("@id", 20);
13
14        // Execute the query
15        command.ExecuteNonQuery();
16        connection.Close();
17    }
18
19}
20catch (Exception ex)
21{
22    //display error message
23    Console.WriteLine("Exception: " + ex.Message);
24}

Al pasar los valores de los parámetros después de definir la consulta, nos aseguramos de que el servidor SQL interprete estos valores literalmente.

Parametrización de consultas con instrucciones preparadas

En el mismo escenario, veamos cómo actualizar la consulta de búsqueda para usar una instrucción preparada.

La cadena de consulta sigue siendo la misma y continúa usando los marcadores de posición `@id` y `@username`. Sin embargo, a diferencia del ejemplo anterior, después de establecer los parámetros, llamamos al método `Prepare` en el objeto `SqlCommand`.

Antes de llamar a `Prepare()`, especifica el tipo de datos de cada parámetro en la instrucción. También debes establecer su propiedad de tamaño en el valor máximo para evitar un error.

Luego, usa el método `ExecuteNonQuery` para ejecutar la instrucción `INSERT` con los parámetros proporcionados e insertar los datos del usuario en la tabla `Users`, como se muestra a continuación:

1try
2{
3    string? username = Console.ReadLine();
4
5    string query = "INSERT INTO Users (pk_id, name) VALUES (@id, @username)";
6
7    using (SqlConnection connection = new SqlConnection(connectionString))
8    {
9        connection.Open();
10        SqlCommand command = new SqlCommand(query, connection);
11
12        // Bind the SQL parameters.
13        SqlParameter idParam = new SqlParameter("@id", SqlDbType.Int, 0);
14        SqlParameter nameParam = new SqlParameter("@username", SqlDbType.Text, 100);
15        idParam.Value = 8;
16        nameParam.Value = "First Region";
17        command.Parameters.Add(idParam);
18        command.Parameters.Add(nameParam);
19
20        // Call Prepare 
21        command.Prepare();
22        command.ExecuteNonQuery();
23
24        // Change parameter values and call ExecuteNonQuery again.
25        command.Parameters[0].Value = 21;
26        command.Parameters[1].Value = "Alex";
27        command.ExecuteNonQuery();
28    }
29
30}
31catch (Exception ex)
32{
33    //display error message
34    Console.WriteLine("Exception: " + ex.Message);
35}

Con las instrucciones preparadas, solo tenemos que llamar al método `Prepare()` una vez. Después, podemos cambiar los valores de los parámetros y ejecutar la consulta varias veces sin volver a compilarla. 

Uso de procedimientos almacenados 

Las instrucciones preparadas nos permiten reutilizar consultas en nuestra aplicación. Sin embargo, no podemos llamar a una instrucción preparada desde varias aplicaciones ni implementar controles de acceso como sí podemos hacerlo con los procedimientos almacenados.

Los procedimientos almacenados son consultas precompiladas que se guardan en el servidor de bases de datos y que podemos llamar repetidamente desde varias aplicaciones. Si se implementan correctamente, ofrecen la misma protección contra ataques de inyección SQL que las instrucciones preparadas. Podemos controlar quién tiene permiso para ejecutar un procedimiento almacenado e incluso restringir el acceso directo a la tabla subyacente, lo que reduce el impacto de una posible inyección SQL.

Al igual que las instrucciones preparadas, los procedimientos almacenados admiten consultas parametrizadas y consideran la entrada del usuario como datos, no como código SQL. Además, la base de datos sanea automáticamente los parámetros que se pasan al procedimiento. Este proceso evita que los atacantes envíen código malicioso a la base de datos.

Debemos usar procedimientos almacenados junto con parámetros de consulta, en lugar de interpolar directamente los valores dinámicos en la consulta. Al mantener una separación clara entre la entrada del usuario y la instrucción SQL, evitamos que el motor SQL la interprete como código. 

Para demostrarlo, creemos un procedimiento almacenado en nuestra base de datos que acepte los valores `Id` y `Name`:

1USE db;  
2GO  
3CREATE PROCEDURE InsertUser
4    @Id INT,
5    @Name VARCHAR(50)  
6AS   
7    INSERT INTO Users (pk_id, Name)
8    VALUES (@Id, @Name);
9GO

Si usas SQL Server Management Studio, ejecuta el procedimiento en el editor de consultas y recuerda reemplazar db por el nombre de tu base de datos.

Este es un ejemplo de cómo usar el procedimiento almacenado para insertar nuevos usuarios en la tabla.

Primero, usa `Console.Readline()` para solicitar un nombre de usuario en la línea de comandos. Luego, establece una conexión con la base de datos usando el comando `SQLConnection`. Después, llama al procedimiento almacenado `InsertUser` que creaste en el ejemplo anterior. 

Para vincular los parámetros a un procedimiento almacenado, usa el método `Parameters.AddWithValue`. En este ejemplo, proporcionamos un valor codificado para el parámetro `@id` y pasamos el nombre de usuario ingresado por el usuario para el parámetro `@name`.

1try
2{
3    string? username = Console.ReadLine();
4    using (SqlConnection connection = new SqlConnection(connectionString))
5    {
6        connection.Open();
7
8        // Call the stored procedure
9        SqlCommand command = new SqlCommand("InsertUser", connection);
10        command.CommandType = CommandType.StoredProcedure;
11
12        // Bind the SQL parameters.
13        command.Parameters.AddWithValue("@id", SqlDbType.Int).Value = 134;
14        command.Parameters.AddWithValue("@name", SqlDbType.VarChar).Value = username;
15
16        // Execute the query
17        command.ExecuteNonQuery();
18    }
19}
20catch (Exception ex)
21{
22    // Display error message
23    Console.WriteLine("Exception: " + ex.Message);
24}

Después de llamar al procedimiento almacenado y pasarle las variables, ejecutamos la consulta con el método `ExecuteNonQuery`.

Medidas de seguridad adicionales para las consultas a bases de datos 

Además de usar la parametrización con instrucciones preparadas y procedimientos almacenados, hay otras medidas de seguridad que ayudan a proteger las consultas SQL.

Implementar el saneamiento y la validación de entradas

Como mencionamos, los atacantes pueden aprovechar los datos de entrada para ejecutar código malicioso en la base de datos. Por eso, es muy importante sanear y validar las entradas. 

El saneamiento permite eliminar los caracteres no deseados (por ejemplo, `{}`, `'` y `/`) o el HTML no seguro de los datos proporcionados por el usuario, mientras que la validación garantiza que estos datos tengan el formato que espera la base de datos. Por ejemplo, podemos comprobar la longitud de la entrada y rechazar las que sean demasiado largas. También podemos exigir formatos específicos para las direcciones de correo electrónico y las fechas. Esta técnica impide que un atacante envíe valores de entrada diseñados especialmente para incluir instrucciones SQL.

Sanear y validar las entradas ayuda a controlar los datos que usamos en nuestras consultas SQL. Sin embargo, no es infalible: los atacantes pueden usar técnicas como la doble codificación para eludir nuestras medidas de protección. Por ejemplo, un atacante puede codificar una comilla simple (`'`) como `%2527` en lugar de `%27`, que es lo habitual, para eludir filtros de entrada que solo decodifican una vez. Por eso, evita usar el saneamiento de entradas como defensa contra la inyección SQL y, en su lugar, parametriza tus consultas y usa el saneamiento como medida de seguridad adicional.

Usar codificación de salida

Cuando muestres la entrada del usuario en una aplicación web, codifícala siempre primero. La codificación de salida evita que los datos del usuario se interpreten como HTML, JavaScript o SQL.

Usar listas de permitidos

No siempre es posible parametrizar las consultas. La parametrización no admite ciertas partes de las consultas SQL, como los nombres de tablas o columnas. En esos casos, es mejor validar la entrada con una lista de permitidos. Esta lista permite definir reglas estrictas que solo aceptan determinados caracteres o patrones, lo que garantiza que se rechacen las entradas maliciosas.

Una lista de permitidos es una mejor forma de prevenir ataques de inyección SQL que una lista de bloqueados. La lista de permitidos define explícitamente qué tipos de entradas se aceptan, mientras que la lista de bloqueados deja margen para eludirla: los atacantes pueden encontrar fácilmente nuevas formas de hacerlo mediante variaciones en la entrada. En definitiva, una lista de permitidos te da más control sobre lo que se acepta.

Aplicar el principio de privilegio mínimo

Para minimizar el impacto de un ataque de inyección SQL exitoso, aplica siempre el principio de privilegio mínimo (PoLP). El PoLP restringe los derechos de acceso de los usuarios a lo necesario para realizar su trabajo.

Por ejemplo, si una cuenta de usuario solo necesita acceso de lectura a una tabla, de acuerdo con el PoLP, le otorgaríamos permiso de solo lectura para esa tabla. Si el usuario solo necesita acceder a una parte de los datos de una tabla, podemos crear una vista que incluya únicamente esos datos y darle acceso a esa vista en lugar de a toda la tabla. 

Si usas procedimientos almacenados, limita los permisos de ejecución de tu aplicación a los procedimientos que necesita. De esta forma, incluso si se produce una inyección SQL, limitas el acceso del atacante y reduces el posible impacto.

Protege tu código con Snyk

Realizar pruebas de inyección SQL puede brindar protección adicional contra las vulnerabilidades de inyección SQL. Las herramientas automatizadas de pruebas de seguridad de aplicaciones estáticas (SAST), como Snyk Code, te ayudan a detectar problemas de seguridad desde las primeras etapas.

Snyk cuenta con un analizador de vulnerabilidades que analiza nuestro código directamente en el IDE. También puedes encontrar una extensión de Visual Studio Code en el administrador de extensiones. Sigue los pasos para instalarla y configurarla.

Una vez que completes la configuración, puedes empezar a usarla haciendo clic en el logotipo de Snyk en la barra lateral. Deberías ver una barra de menú como la de la imagen de abajo. Cada elemento del menú tiene una flecha desplegable en la que puedes hacer clic para comenzar. Usaremos CODE SECURITY.

Si ejecutamos el análisis de seguridad de código en una aplicación .NET que no usa consultas parametrizadas, Snyk nos avisará de que el proyecto tiene una vulnerabilidad de inyección SQL.

Considera el siguiente método, que usa el formato de cadenas para concatenar la entrada del usuario con la consulta:

1public void withoutParameters(string dataSource, string initialCat, string persistSecurity, string userId, string password)
2    {
3        string connectionString = $"Data Source={dataSource};Initial Catalog={initialCat};Persist Security Info={persistSecurity};User ID={userId};Password={password}";
4        try
5        {
6
7            string username = Console.ReadLine();
8
9            using (SqlConnection connection = new SqlConnection(connectionString))
10            {
11            connection.Open();
12            string query = $"INSERT INTO Users(pk_id, name) VALUES (48,'{username}');";
13                using (SqlCommand command = new SqlCommand(query, connection))
14                {
15                    command.Parameters.AddWithValue("@username", username);
16
17                    using (SqlDataReader reader = command.ExecuteReader())
18                    {
19                        while (reader.Read())
20                        {
21                            Console.WriteLine("{0} {1}", reader.GetString(0), reader.GetString(1));
22                        }
23                    }
24                }
25            }
26        }
27        catch (Exception ex)
28            {
29                //display error message
30                Console.WriteLine("Exception: " + ex.Message);
31            }
32 }

Si ejecutamos el análisis de seguridad de código en el código anterior, Snyk detectará una vulnerabilidad, como se muestra en la siguiente captura de pantalla.

El informe de análisis indica que se usa una entrada sin sanear de un argumento de la línea de comandos en la consulta SQL, lo que podría provocar una vulnerabilidad de inyección SQL. Snyk señala la línea y el archivo que contienen la vulnerabilidad. También muestra ejemplos de posibles correcciones para que podamos solucionar las vulnerabilidades más rápido.

Conclusión

La parametrización de consultas es fundamental para prevenir ataques de inyección SQL. Garantiza que la entrada del usuario se trate como datos y no como código ejecutable, lo que reduce eficazmente el riesgo de inyección SQL maliciosa. 

Hemos hablado de las ventajas de implementar la parametrización de consultas con o sin declaraciones preparadas. La primera opción ofrece mayores beneficios de seguridad, ya que separa la lógica SQL de los datos ingresados por el usuario y elimina la necesidad de aplicar escapes o sanitización manualmente. Además, las declaraciones preparadas mejoran el rendimiento porque las consultas se precompilan.

Una alternativa a las declaraciones preparadas son los procedimientos almacenados, que ofrecen una capa adicional de seguridad cuando se usan con parámetros de consulta, ya que puedes controlar quién tiene acceso.

Para reforzar aún más la seguridad de tu aplicación, podemos integrar el escáner de vulnerabilidades de Snyk en el flujo de trabajo de desarrollo. Al analizar periódicamente nuestro código, podemos identificar vulnerabilidades que podrían provocar una inyección SQL. Esta práctica nos permite abordar los posibles riesgos de seguridad durante el desarrollo y reducir las probabilidades de que los atacantes expongan nuestra aplicación.