Blog – Fabiano Neves Amorim

Primeiramente quero agradecer a todos que se interessaram pelo desafio e perderam algum tempo tentando chegar na solução . Eu gostei tanto que já tenho outro desafio preparado para publicação, mas vamos por partes…

 

Se você não sabe do que estou falando veja o desafio aqui.

 

A única pessoa que conseguiu responder o desafio mandou uma solução MUITO MELHOR que a minha hahuahuuh… não era por menos, quem respondeu o desafio foi o grande (modo de dizer) Fabricio Catae (blog|twitter) o ninja!

Parabéns Catae você é o cara! No próximo desafio vou colocar um aviso do tipo “Aviso: Ninjas estão automaticamente desclassificados! huahuahua”

 

Vamos analisar primeiro a minha solução, e depois veremos a do Catae.

 

Solução 1:

Minha solução consiste em trabalhar com as mensagens de erro do SQL Server para ver as informações que supostamente não deveríamos saber que existe. Isso é muito utilizado em SQL Injection, ou seja, se você não trata as mensagens de erro que envia para sua aplicação, tá muito errado, cuidado.

Primeiramente vejamos o plano de execução do select na view: (pra quem tá chorando nessa hora dizendo, aaaa mas não vale ver o plano e tals… não Sr. eu disse que vale tudo, ou seja, você conhece as tabelas, pode ver o plano logado com um usuário que te permissão para isso, mas para “ver” os dados que não poderíamos ver você precisa estar logado com o usuarioquenaopodenada)

 

 

Vamos executar este plano passo a passo:

1. Executa o Clustered Index Scan na tabela TabFuncionarios, ou seja, vai fazer um SCAN na tabela, pra quem ainda não entendeu, isso significa que o SQL vai passar por TODAS as linhas da tabela. É importante que você entenda isso.
2. Nested Loops trabalhando como Anti Semi Join (left anti semi = not exists, right anti semi = exists). Um Anti Semi Join é um Join que retorna dados de apenas um dos lados do join, ou seja, neste caso apenas colunas da tabela TabFuncionarios serão retornadas.
3. Para cada linha recebida pelo Scan na TabFuncionarios, o Nested Loops chama o Clustered Index Scan na tabela TabEsconderFuncionarios.
4. Clustered Index Scan na tabela TabEsconderFuncionarios retorna as linhas da tabela TabEsconderFuncionarios para o Nested Loops para ver quem faz o join, e consequentemente eliminar as linhas que não queremos exibir (validação do not exisits).

Bom, agora que sabemos que a PRIMEIRA COISA que o plano faz é varrer a tabela TabFuncionarios, podemos tentar aplicar um filtro nesta tabela para tentar gerar um erro de conversão… Por exemplo:

O que acontece se eu rodar o seguinte comando?

SELECT *

  FROM vw_TabFuncionarios

WHERE CONVERT(Int, Nome) = 0

Msg 245, Level 16, State 1, Line 1

Conversion failed when converting the varchar value ‘Fabiano’ to data type int.

 

Plano da consulta:

O que acontece é que o SQL tenta converter a coluna Nome para Int e gera um erro de conversão. Ótimo, estamos chegando perto…

Repare que no plano de execução da consulta acima o SQL Server executa (Predicate) o CONVERT(Int, Nome) = 0 antes de fazer o join, ou seja, o SQL vai tentar fazer a conversão para TODAS as linhas da tabela, no caso acima na primeira linha lida o erro já é exibido, porém podemos incluir mais um filtro no valor que já conhecemos para ignorar o “Fabiano” por exemplo:

SELECT *

  FROM vw_TabFuncionarios

WHERE CONVERT(Int, Nome) = 0

   AND Nome NOT IN (‘Fabiano’)

 

Msg 245, Level 16, State 1, Line 1

Conversion failed when converting the varchar value ‘Gilberto’ to data type int.

 

Plano da consulta:

 

Repare que agora a conversão deu erro no Gilberto, vamos inclui-lo na lista do IN:

SELECT *

  FROM vw_TabFuncionarios

WHERE CONVERT(Int, Nome) = 0

   AND Nome NOT IN (‘Fabiano’, ‘Gilberto’)

 

Msg 245, Level 16, State 1, Line 1

Conversion failed when converting the varchar value ‘Luciano’ to data type int.

 

Já conseguimos visualizar que existe um funcionário chamado Luciano , e pra saber qual é o salario dele? … Basta concatenar a coluna salario com o Nome e tentar converter tudo para Int…

SELECT *

  FROM vw_TabFuncionarios

WHERE CONVERT(Int, Nome + CONVERT(VarChar(30), Salario)) = 0

   AND Nome NOT IN (‘Fabiano’, ‘Gilberto’)

 

Msg 245, Level 16, State 1, Line 1

Conversion failed when converting the varchar value ‘Luciano25999.00’ to data type int.

 

Feito, conseguimos ver que o Luciano ganha R$25.999,00 por mês, tai o motivo dele estar na tabela EsconderFuncionarios J. Para ver quanto o Ivan ganha, basta incluir o Luciano na lista do IN…

Tem uma coisa importante que talvez você não tenha notado… Repare que para que isso tudo funcione o SQL Server primeiro tem que fazer a validação do NOT IN, e depois fazer a validação da conversão do nome para int = 0. Ou seja,

No WHERE, ele tem que executar primeiro isso “NomeNOTIN(‘Fabiano’,‘Gilberto’)” e depois “CONVERT(Int,Nome)= 0”, veja que ainda que nós tenhamos escrito o código especificando primeiro o CONVERT e depois o NOT IN, o SQL Server executou o NOT IN antes, vejamos isso no plano:

O otimizador de consultas que decide quem ele deve executar primeiro, leia mais sobre isso neste post do Itzik. Ok, mas dai vem a pergunta, como forçar a ordem de execução das expressões e ter certeza de que nossa solução vai funcionar independente da versão do SQL Server que estou usando?  (No SQL Server 2000 a consulta acima não funciona).

Para forçar a ordem de execução das expressões e evitar (ou forçar) um erro devemos usar o CASE, portanto a consulta final ficaria assim:

— Rodar a consulta no escopo do UsuarioQueNaoPodeNada

EXEC AS USER = ‘UsuarioQueNaoPodeNada’

GO

— Quanto o Luciano ganha?

SELECT *

  FROM vw_TabFuncionarios

WHERE (CASE

          WHEN (Nome NOT IN (‘Fabiano’, ‘Gilberto’)) AND (CONVERT(Int, Nome + CONVERT(VarChar, Salario) ) = 0) THEN 0

          ELSE 1

        END) = 0

 

Solução 2 (by Fabrício Catae a.k.a. Ninja):

A solução do Catae é sensacional porque ela não retorna os dados utilizando as mensagens de erro do SQL. A ideia é simples (depois eu você vê é claro), ela consiste em gerar um lock exclusivo nas linhas da tabela TabEsconderFuncionarios e depois ler a view utilizando o hint ReadPast.

Pra quem não conhecia, o hint READPAST é similar ao famoso NOLOCK, a ideia é não ficar bloqueado por uma sessão que está com lock exclusivo nas linhas que você esta tentando ler. Porém diferentemente do NOLOCK (quer permite leitura suja para não ficar bloqueado) o READPAST “pula” as linhas que estão com lock por linha (KEY LOCK). Voltando ao nosso cenário, se as linhas da tabela TabEsconderFuncionarios estiverem bloqueadas com xLock (lock exclusivo) e RowLock (lock por linha) ao ler os dados da view, o SQL Server “entende” que não existe nada na tabela e consequentemente retorna tudo.

Vejamos na prática:

 

— Sessão 1

BEGIN TRAN

— Gerar lock exclusivo em tudo o que lê, ou seja,

— nas linhas Fabiano e Gilberto da TabFuncionarios

— e nas linhas ID 3 e 4 da TabEsconderFuncionarios

SELECT * FROM vw_TabFuncionarios WITH (xlock, rowlock)

WHERE Nome IN (‘Fabiano’, ‘Gilberto’)

GO

Em uma sessão eu inicio uma transação, e leio a view utilizando dois hints, o xlock para forçar a o lock exclusivo, e o rowlock para forçar o lock por linha (lembre-se o READPAST só “ignora” locks de linha).

Podemos consultar os locks gerado pela consulta na view utilizando o seguinte código:

— Sessão 1

— Consultar locks

— Gerou os locks esperados

SELECT OBJECT_NAME(partitions.OBJECT_ID) AS ObjectName,

       dm_tran_locks.resource_type,

       dm_tran_locks.resource_description,

       dm_tran_locks.request_mode

  FROM sys.dm_tran_locks

  LEFT OUTER JOIN sys.partitions

    ON partitions.hobt_id = dm_tran_locks.resource_associated_entity_id

WHERE request_session_id = @@spid

   AND resource_type = ‘KEY’

ORDER BY 1

 

Conforme podemos observar o select na view gerou 2 locks na tabela TabFuncionarios (para os IDs 1 e 2, repare no where por nome) e 2 locks na tabela TabEsconderFuncionarios (nos IDs 3 e 4)

Se eu abrir outra sessão e tentar rodar um select na vw_TabFuncionarios eu vou ficar bloqueado pro causa dos locks exclusivos gerados na sessão 1, porém se eu rodar o código com o hint READPAST, o SQL vai ler as linhas de IDs 3 e 4 da tabela TabFuncionarios e ao tentar fazer o join com a TabEsconderFuncionarios ele não vai encontrar nada lá (porque ele “pula” as linhas bloqueadas), e consequentemente retorna os dados… Vejamos:

— Sessão 2

— Abrir outra sessão e ler os dados da view com READPAST hint

— Como os IDs da TabEsconderFuncionarios estão com lock na linha (importante que lock de linha)

— ao fazer o join para ver quem NOT EXISTS o SQL não ve nada, ou seja, posso ver todas as linhas

SELECT * FROM vw_TabFuncionarios WITH(readpast)

 

Conforme podemos observar, conseguimos visualizar todos os dados da tabela.

É importante que o SQL Server gere os locks POR LINHA, se na sessão 1 o SQL Server gerar os locks por página ou tabela, esquece que não vai funcionar…

Se você esta se perguntando, como evitar esse comportamento… a resposta é… você poderia criar view com with(nolock) nas tabelas e dai ao tentar ler a view utilizando o READPAST o SQL Server ira gerar a seguinte mensagem de erro:

 

Msg 650, Level 16, State 1, Line 5

You can only specify the READPAST lock in the READ COMMITTED or REPEATABLE READ isolation levels.

 

Bom, é isso, espero que tenham gostado, quer mais? … J

Fique de olho que na semana que vem vou publicar outro desafio, desta vez mais simples, porém relacionado a performance.

Abs.