SQL Server - \u00bfC\u00f3mo evitar SQL Injection? Deje de usar Query Din\u00e1mica como EXEC(@Query). Ahora.

Un recurso muy utilizado en sistemas y rutinas de bases de datos, la Consulta Dinámica consiste en ensamblar una cadena con comandos T-SQL a ejecutar. Esta cadena se ensambla en base a la concatenación de cadenas y posibles validaciones con IF según ciertos escenarios.

Una vez construida la cadena final, se procesa mediante el comando EXECUTE (o EXEC, para aquellos más íntimos... jajaja) y todo lo que esté en esta cadena se ejecutará en la base de datos.

Es muy importante tener en cuenta que la consulta dinámica no es específica únicamente de las consultas creadas desde SQL Server. Un sistema puede hacer lo mismo, creando una variable de cadena en el código fuente de C#, por ejemplo, ensamblando la cadena y luego enviándola a la base de datos. Esta también es una consulta dinámica, pero creada dentro de la aplicación y en la base de datos, será solo una consulta ad-hoc que envía la aplicación.

Ejemplo básico:

DECLARE 
    @Objeto VARCHAR(128) = 'sys.objects',
    @Nome_Objeto VARCHAR(128) = 'syscerts',
    @Query VARCHAR(MAX)

-- Monta a nossa query dinâmica    
SET @Query = 'SELECT * FROM ' + @Objeto + ' WHERE [name] = ''' + @Nome_Objeto + ''''

-- Mostra na tela a query final depois de montada
SELECT @Query

-- Executa o comando no banco
EXEC(@Query)

Resultado:

Como puede ver, las posibilidades son infinitas al utilizar Dynamic Query. Desde realizar uniones y definir las columnas devueltas por SELECT hasta controlar filtros en WHERE. Un ejemplo realmente interesante desde el punto de vista del rendimiento sería este (y sucede con frecuencia):

Consulta original:

DECLARE 
    @Schema_ID INT = 4,
    @Nome_Objeto VARCHAR(128) = 'syscerts',
    @Type VARCHAR(10),
    @object_id INT

SELECT *
FROM sys.objects
WHERE (@Schema_ID IS NULL OR [schema_id] = @Schema_ID)
AND (@Nome_Objeto IS NULL OR [name] = @Nome_Objeto)
AND (@Type IS NULL OR [type] = @Type)
AND (@object_id IS NULL OR [object_id] = @object_id)

Este conjunto de @variabel IS NULL OR [columna] = @variavel en el filtro WHERE de la consulta a menudo puede causar un problema de rendimiento en la consulta, ya que SQL tendrá que realizar varias validaciones en el filtro para devolver los datos. Una posible solución a esto sería utilizar una consulta dinámica:

DECLARE 
    @Schema_ID INT = 4,
    @Nome_Objeto VARCHAR(128) = 'syscerts',
    @Type VARCHAR(10),
    @object_id INT,
    @Query VARCHAR(MAX)


-- Monta a base da nossa query dinâmica 
SET @Query = 'SELECT * FROM sys.objects WHERE 1=1'


-- Aplica os filtros dinâmicamente
IF (@Nome_Objeto IS NOT NULL)
    SET @Query += ' AND [name] = ''' + @Nome_Objeto + ''''

IF (@Type IS NOT NULL)
    SET @Query += ' AND [type] = ''' + @Type + ''''

IF (@Schema_ID IS NOT NULL)
    SET @Query += ' AND [schema_id] = ' + CAST(@Schema_ID AS VARCHAR(10))

IF (@object_id IS NOT NULL)
    SET @Query += ' AND [object_id] = ' + CAST(@object_id AS VARCHAR(10))


-- Mostra na tela a query final depois de montada
SELECT @Query

-- Executa o comando no banco
EXEC(@Query)

Resultado de la ejecución:

Si analizamos la consulta final generada dinámicamente y la consulta original, llena de OR, vemos que la consulta dinámica que se ejecuta es mucho más sencilla que la consulta original. Y esto también se refleja en el plan de ejecución y el costo de ejecución:

En otras palabras, la consulta dinámica tiene una aplicabilidad justificable SI, y es muy útil en N escenarios, pero debemos tener mucho cuidado con su uso para no exponer nuestros entornos a ataques de Inyección SQL.

Después de este breve resumen sobre consultas dinámicas, ahora le presentaré un breve resumen sobre la inyección SQL. Esta técnica de craqueo consiste en explotar consultas dinámicas realizadas en la base de datos para ejecutar comandos maliciosos en la base de datos, que pueden ir desde mostrar datos confidenciales, confidenciales y sensibles hasta eliminar datos y archivos en el servidor de la base de datos.

Si estás pensando que esta técnica debe ser muy compleja y elaborada estás muy equivocado. En realidad, es bastante sencillo de utilizar y un atacante puede utilizarlo en campos de texto de una aplicación, como por ejemplo una pantalla de inicio de sesión, que envía una consulta dinámica al banco para comprobar si el nombre de usuario y la contraseña introducidos son correctos.

Ejemplo 1: iniciar sesión en el sistema sin autorización

Imagine un escenario con la siguiente tabla en su banco, utilizada para el registro y autenticación de usuarios de una manera muy sencilla:

CREATE TABLE dbo.Usuarios (
    Id_Usuario INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
    Ds_Email VARCHAR(200) NOT NULL,
    Ds_Senha VARCHAR(100) NOT NULL
)

INSERT INTO dbo.Usuarios
VALUES('joaozinho@microsoft.com', 'joao')

Y ahora imagina que tu aplicación C# tiene el siguiente código para validar el correo electrónico y la contraseña:

try
{

    using (var conn = new SqlConnection(dadosConexao))
    {

        conn.Open();

        using (var cmd = new SqlCommand())
        {

            cmd.CommandType = CommandType.Text;
            cmd.Connection = conn;

            /* Olha a nossa query dinâmica aqui :) */
            var dsQuery = "SELECT 1 FROM dbo.Usuarios WHERE Ds_Email = '" + email + "' AND Ds_Senha = '" + senha + "'";
                    
            cmd.CommandText = dsQuery;
            
            var retorno = cmd.ExecuteScalar();
            
            if (retorno != null && retorno = "1")
                Login.autenticaUsuario();
            else
                Login.retornaErro();

        }
    }

}
catch (Exception e)
{
    Retorno.Erro("Erro : " + e.Message);
}

Lo que llegará al banco es la consulta que creó la aplicación. Si un usuario malintencionado intenta realizar un ataque de Inyección SQL, utilizando un enfoque simple, como colocar la cadena “‘OR ‘x’=’x’–” en el campo de usuario y hacer clic en el botón “Iniciar sesión” de la aplicación, esta es la consulta que llegará a la base de datos:

SELECT * FROM dbo.Usuarios WHERE Ds_Email = '' OR 'x'='x'--' AND Ds_Senha = ''

Resultado: el atacante pudo iniciar sesión en el sistema

Y créeme. ellos existen MUCHAS cadenas como esta para realizar ataques de inyección SQL.

Ejemplo 2: eliminar y ver datos

En el mismo ejemplo anterior, en una pantalla de inicio de sesión simple, tenga en cuenta que una cadena simple como “'; TRUNCATE TABLE dbo.Usuarios;–” puede causar interrupciones importantes en la vida de un DBA simplemente BORRANDO todos los datos de la tabla Usuarios del sistema.

Por supuesto, el atacante aún necesita descubrir el nombre de las tablas del sistema y, en el escenario anterior, esta tarea termina siendo complicada ya que la consulta no devuelve muchos datos, solo validación. Pero si esto es en una pantalla de consulta, por ejemplo, donde los datos devueltos se muestran en una tabla en pantalla, entonces tenemos un escenario ideal para realizar un ataque al usar una cadena como “'; SELECT name, name, name, name FROM sys.tables; –” en un campo de búsqueda, por ejemplo, que sabemos que muestra 4 columnas en pantalla.

Consulta que termina siendo enviada a la base de datos:

SELECT cpfcnpj, FirstName, [Uid], ID 
FROM dbo.Tabela 
WHERE cpfcnpj = ''; SELECT name, name, name, name FROM sys.tables; --'

Resultado:

Y luego el atacante ahora tiene el nombre de las tablas que existen en la base de datos. Lo que hace que la "broma" sea mucho más interesante ahora, ya que puede eliminar datos de cualquiera de estas tablas o ver estos datos a través del propio sistema. ¿Y si pudiera enviar los datos de la tabla por correo electrónico? Mmmmm..

Usando la cadena "'; EXEC msdb.dbo.sp_send_dbmail @recipients ='[correo electrónico protegido]’, @subject = ‘Teste’, @query = ‘SELECT * FROM Usuarios’ –“, el atacante puede usar DatabaseMail instalado en el servidor de la base de datos para enviarse a sí mismo los resultados de una consulta y ver los datos de la base de datos:

En otras palabras, la idea aquí es mostrar cuán peligrosos son los ataques de inyección SQL. Esto se debe a que ni siquiera voy a demostrar cómo usar xp_cmdshell para ejecutar comandos a nivel del sistema operativo en el servidor. Incluso puedes descargar ransomware de forma remota y ejecutarlo en el servidor. Todo ello a través de un sencillo campo de búsqueda en el sistema.

Espero que se haya sorprendido por el impacto devastador que la inyección SQL puede tener en su entorno. Dependiendo del nivel de permisos, el atacante puede incluso instalar ransomware, difundirlo por su red y causar un daño masivo a su empresa a través de un simple campo de texto en un sistema.

Consejo 1: restringir permisos a nivel de base de datos

El primer paso para contener este tipo de ataque es limitar primero el alcance del ataque y luego tratar de evitar que ocurra.

Sí, eso es correcto. Parece extraño, pero si puedes limitar hasta dónde puede llegar un ataque de este tipo, suele ser un proceso mucho más rápido de implementar en el entorno y por eso prefiero empezar con este paso.

Por supuesto, esta generalización mía se basa en escenarios tradicionales y debe evaluarse, ya que su escenario puede ser diferente. Generalmente en escenarios de grandes empresas, existen varios sistemas diferentes accediendo a la misma instancia, con decenas de Procedimientos Almacenados con consulta dinámica, además de las diversas consultas ad-hoc que son resultado de consultas dinámicas ensambladas en la aplicación y enviadas a la base de datos.

Evaluar y tratar todos estos puntos de entrada suele llevar mucho tiempo. Precisamente por eso recomiendo empezar por restringir los permisos de los usuarios de la aplicación. Es muy inusual que un usuario del sistema necesite permisos que vayan más allá de la lectura/escritura en las bases de datos a las que accede el sistema. Los usuarios del sistema rara vez necesitan permisos a nivel de instancia.

Y no me digas que siempre liberas el permiso db_owner ¿verdad? Detengamos esto HOY. Un permiso db_owner conlleva implícitamente otros permisos no necesarios como PERSONIFICAR, que es tan peligroso para las auditorías, y también permiso para ver y enumerar las bases de datos, aunque no tengo permiso VER NINGUNA BASE DE DATOS.

En la gran mayoría de los casos, una aplicación puede necesitar, como máximo, permiso de EJECUCIÓN general en la base de datos y estar en las funciones de base de datos db_datareader, db_datawriter y quizás db_ddladmin. Si usted es uno de esos DBA que libera el permiso db_owner al usuario de la aplicación porque es más práctico, le sugiero leer el artículo. SQL Server: comprensión de los riesgos de la propiedad CONFIABLE habilitada en una base de datos, que muestra lo que un usuario db_owner puede hacer en un entorno donde la base de datos tiene habilitado el parámetro TRUSTWORTHY. Incluso si no lo es, se le permitirá usar IMPERSONATE, que ya es bastante peligroso.

Consejo 2: restricción de permisos a nivel de instancia

Lamentablemente, ¿qué vemos a diario? Aplicación que se conecta al banco con un usuario SA o un usuario de la aplicación con permiso de administrador de sistemas. Es decir, imaginemos el escenario en el que disponemos de un sistema Web muy sencillo para cubrir una necesidad concreta del sector comercial de la empresa. Este sistema es sólo un registro de proveedores, lo que ni siquiera es tan crítico para la empresa. Y este sistema conecta al banco con el usuario sa. Un atacante encuentra una vulnerabilidad utilizando este usuario administrador de sistemas y aplica una inyección SQL a la instancia que contiene todos los sistemas de la empresa.

¡Datos de toda la empresa capturados y luego cifrados con éxito! Y todo ello desde un sistema que ni siquiera era el Core de la empresa. En otras palabras, el primer paso para prevenir este tipo de ataque es limitar los permisos de la aplicación y desde qué bases de datos esta aplicación puede leer/escribir datos. Si un sistema no accede/cambia ninguna tabla en otra base de datos, ¿por qué tiene este permiso?

Además, el simple hecho de que la aplicación no tenga permisos de administrador de sistemas significa que el atacante no podrá activar funciones que están deshabilitadas, como xp_cmdshell, por ejemplo, y tampoco podrá ejecutar comandos críticos como xp_cmdshell, enviar correos electrónicos utilizando Database Mail, etc.

Por todas las razones mencionadas anteriormente, es fundamental limitar los permisos de los usuarios de la aplicación a nivel de instancia. La idea es que ni siquiera tengas este permiso. Utilizar usuarios SA o usuarios con permiso de administrador de sistemas es impensable e injustificable.

Consejo 3: restringir los permisos a nivel del sistema operativo (SO)

Para cerrar esta parte de permisos, también necesitamos limitar los permisos del usuario de SQL Server en el sistema operativo. Vemos en muchas empresas al usuario del servicio SQL Server con permisos de Administrador del servidor local o incluso Administrador de Dominio (Permiso máximo dentro de Active Directory. Puede hacer TODO en CUALQUIER servidor).

Este tipo de permiso mejora enormemente el efecto de estos ataques de inyección SQL. Entonces lo más correcto es limitarse al usuario de servicios de SQL Server, que es quien ejecuta los comandos a nivel de Sistema Operativo cuando ejecutas un xp_cmdshell, por ejemplo a un usuario con el mínimo permiso requerido.

Consejo 4: identificar consultas dinámicas en el entorno

Como mencioné anteriormente, la parte de permisos suele ser más rápida de implementar y es por eso que elijo hacerlo primero. Ahora cambiaremos nuestro enfoque a prevenir ataques, pero necesitamos identificar los posibles puntos de entrada para los ataques de inyección SQL, es decir, dónde utilizamos la consulta dinámica en nuestro entorno.

A nivel de aplicación y sistema, este trabajo lo debe realizar el equipo de Sistemas/Desarrollo, escaneando todo el código fuente en busca de lugares que utilicen consultas dinámicas y aplicando el tratamiento de esta en la aplicación, sobre todo porque, cuando la consulta se ensambla en la aplicación, el banco no tiene forma de diferenciar qué es una consulta dinámica o no para intentar tratarla en la base de datos.

A nivel de base de datos, cuando existen Procedimientos Almacenados que utilizan esta técnica de consulta dinámica, podemos utilizar una consulta T-SQL para identificar todos estos SP's y evaluar cuáles se pueden utilizar para ataques de Inyección SQL. Generalmente solo evalúo Procedimientos que tienen parámetros de cadena (varchar, nvarchar, char, nchar) y con un tamaño mayor a 10. Los parámetros numéricos y las cadenas cortas son muy difíciles de usar para Inyección SQL.

Para identificar estos procedimientos almacenados, puede utilizar la siguiente consulta:

DECLARE @Objetos_Query_Dinamica TABLE ( [Ds_Database] nvarchar(256), [Ds_Objeto] nvarchar(256), [Ds_Tipo] nvarchar(128), [definition] VARCHAR(MAX) )


IF (OBJECT_ID('tempdb.dbo.#Palavras_Exec') IS NOT NULL) DROP TABLE #Palavras_Exec
CREATE TABLE #Palavras_Exec (
    Palavra VARCHAR(100) COLLATE SQL_Latin1_General_CP1_CI_AI
)

INSERT INTO #Palavras_Exec
VALUES('%EXEC (%'), ('%EXEC(%'), ('%EXECUTE (%'), ('%EXECUTE(%'), ('%sp_executesql%')


INSERT INTO @Objetos_Query_Dinamica
EXEC sys.sp_MSforeachdb '
IF (''?'' <> ''tempdb'')
BEGIN

    SELECT DISTINCT TOP(100)
        ''?'' AS Ds_Database,
        B.[name],
        B.[type_desc],
        A.[definition]
    FROM
        [?].sys.sql_modules A WITH(NOLOCK)
        JOIN [?].sys.objects B WITH(NOLOCK) ON B.[object_id] = A.[object_id]
        JOIN #Palavras_Exec C WITH(NOLOCK) ON A.[definition] COLLATE SQL_Latin1_General_CP1_CI_AI LIKE C.Palavra
    WHERE
        B.is_ms_shipped = 0
        AND ''?'' <> ''ReportServer''
        AND B.[name] NOT IN (''sp_WhoIsActive'', ''sp_showindex'', ''sp_AllNightLog'', ''sp_AllNightLog_Setup'', ''sp_Blitz'', ''sp_BlitzBackups'', ''sp_BlitzCache'', ''sp_BlitzFirst'', ''sp_BlitzIndex'', ''sp_BlitzLock'', ''sp_BlitzQueryStore'', ''sp_BlitzWho'', ''sp_DatabaseRestore'')
        AND NOT (B.[name] LIKE ''stp_DTA_%'' AND ''?'' = ''msdb'')
        AND NOT (B.[name] = ''sp_readrequest'' AND ''?'' = ''master'')
        AND EXISTS (
            SELECT NULL
            FROM [?].sys.parameters X1 WITH(NOLOCK)
            JOIN [?].sys.types X2 WITH(NOLOCK) ON X1.system_type_id = X2.user_type_id
            WHERE A.[object_id] = X1.[object_id]
            AND X2.[name] IN (''text'', ''ntext'', ''varchar'', ''nvarchar'')
            AND (X1.max_length > 10 OR X1.max_length < 0)
        )
            
END'

SELECT * FROM @Objetos_Query_Dinamica

Resultado:

Ahora solo identifica, valida y analiza las consultas que aparecen en esta consulta y verifica si son susceptibles a ataques de Inyección SQL y siempre que sea posible, reemplaza el comando EXECUTE por sp_executesql. En el próximo tema mostraré por qué.

Consejo 5: Manejar consultas dinámicas

Y ahora por fin ha llegado la parte donde debemos gestionar nuestras consultas.

A nivel de aplicación y sistema, este trabajo lo debe realizar el equipo de Sistemas/Desarrollo, escaneando todo el código fuente en busca de lugares que utilicen consulta dinámica y aplicando este tratamiento en la aplicación, como ya había comentado en el tema anterior. Hay varias formas de evitar esto en la aplicación, como consultas parametrizadas, que bloqueará cualquier tipo de inyección SQL que un atacante pueda intentar atacar.

A nivel de base de datos, debe utilizar la consulta que compartí en el tema anterior para identificar consultas que utilizan consultas dinámicas. El primer paso es analizar si realmente esta consulta necesita ser dinámica. Créame, he visto muchos casos en los que se utilizaron consultas dinámicas sin necesidad alguna, como lo demostraré a continuación:

ALTER PROCEDURE dbo.stpConsulta_CPF ( 
    @CPF VARCHAR(14) 
)
AS
BEGIN
    
    DECLARE @Query VARCHAR(MAX) = 'SELECT * FROM dbo._Teste WHERE CPF = ''' + @CPF + ''''
    EXEC(@Query)

END

Tenga en cuenta que en el ejemplo anterior, el uso de consultas dinámicas es altamente evitable:

ALTER PROCEDURE dbo.stpConsulta_CPF ( 
    @CPF VARCHAR(14) 
)
AS
BEGIN
    
    SELECT * 
    FROM dbo._Teste 
    WHERE CPF = @CPF

END

Mucho más seguro ahora y estamos libres de inyección SQL en este ejemplo. 🙂

¿Es realmente necesario que la consulta sea dinámica? Entonces el segundo paso es intercambiar, siempre que sea posible, los comandos EXECUTE por sp_executesql, ya que la segunda opción permite la parametrización de consultas. Incluso usaré el ejemplo que ya mostré aquí.

Consulta dinámica con EJECUTAR:

DECLARE 
    @Schema_ID INT = 4,
    @Nome_Objeto VARCHAR(128) = 'syscerts',
    @Type VARCHAR(10),
    @object_id INT,
    @Query VARCHAR(MAX)


-- Monta a base da nossa query dinâmica 
SET @Query = 'SELECT * FROM sys.objects WHERE 1=1'


-- Aplica os filtros dinâmicamente
IF (@Nome_Objeto IS NOT NULL)
    SET @Query += ' AND [name] = ''' + @Nome_Objeto + ''''

IF (@Type IS NOT NULL)
    SET @Query += ' AND [type] = ''' + @Type + ''''

IF (@Schema_ID IS NOT NULL)
    SET @Query += ' AND [schema_id] = ' + CAST(@Schema_ID AS VARCHAR(10))

IF (@object_id IS NOT NULL)
    SET @Query += ' AND [object_id] = ' + CAST(@object_id AS VARCHAR(10))


-- Mostra na tela a query final depois de montada
SELECT @Query

-- Executa o comando no banco
EXEC(@Query)

Resultado:

Ahora hagamos algunos cambios en la consulta para usar la consulta parametrizada con sp_executesql:

DECLARE 
    @Schema_ID INT = 4,
    @Nome_Objeto VARCHAR(128) = 'syscerts',
    @Type VARCHAR(10),
    @object_id INT,
    @Query NVARCHAR(MAX)


-- Monta a base da nossa query dinâmica 
SET @Query = 'SELECT * FROM sys.objects WHERE 1=1'


-- Aplica os filtros dinâmicamente
IF (@Nome_Objeto IS NOT NULL)
    SET @Query += ' AND [name] = @Nome_Objeto'

IF (@Type IS NOT NULL)
    SET @Query += ' AND [type] = @Type'

IF (@Schema_ID IS NOT NULL)
    SET @Query += ' AND [schema_id] = @Schema_ID'

IF (@object_id IS NOT NULL)
    SET @Query += ' AND [object_id] = @object_id'


-- Mostra na tela a query final depois de montada
SELECT @Query

-- Executa o comando no banco
EXEC sys.sp_executesql 
    @stmt = @Query,
    @params = N'@Nome_Objeto VARCHAR(128), @Type VARCHAR(10), @Schema_ID INT, @object_id INT',
    @Nome_Objeto = @Nome_Objeto, @Type = @Type, @Schema_ID = @Schema_ID, @object_id = @object_id

Resultado:

Y así, tenemos lo mejor de todos los mundos: Consulta flexible y personalizable, rápida y segura.

• Ejecutar comandos SQL dinámico en SQL Server
• Protegiéndose de la inyección SQL en SQL Server – Parte 1
• Protegiéndose de la inyección SQL en SQL Server – Parte 2
• Uso de parámetros para consultas de SQL Server y procedimientos almacenados
• Uso de desencadenantes para mitigar algunos efectos de la inyección SQL
• Inyección SQL: la regla de oro
• Inyección SQL: defensa en profundidad
• Todo lo que quería saber sobre la inyección SQL (pero tenía miedo de preguntar)
• Los procedimientos almacenados y los ORM no lo salvarán de la inyección SQL
• SQL dinámico y inyección de SQL