Veja nesse artigo uma relação da utilização de codificações abreviadas, mais conhecidas como shortcodes. Veja também como utilizar códigos dinâmicos no T-SQL.
Artigos Banco de Dados Dynamic T-SQL: Trabalhando com operadores de concatenação(shortcodes)
Neste artigo, trataremos com relação a utilização de codificações abreviadas, mais conhecidas como shortcodes, além de mostrarmos alguns exemplos de sua utilização. Além disso, trataremos também da utilização de códigos dinâmicos. Neste segundo momento, podemos avaliar a sua utilização em casos que precisamos escrever códigos T-SQL que criam códigos T-SQL mais específicos e após isso, executá-los. Esse é o momento do qual estamos falando.
Utilizando os operadores de concatenação
A utilização dos operadores de concatenação podem parecer um recurso simples, mas garantem uma maior facilidade de leitura do código e também uma redução aceitável de código. Primeiramente, vejamos com relação a sintaxe para a concatenação que utiliza o operador de adição, em seguida, apresentaremos a sintaxe de utilização do operador de subtração que é bastante semelhante a sintaxe de adição, estes por sua vez, serão apresentados de acordo com as Listagens 1 e 2. Estas operações não são encontradas apenas no T-SQL, mas sim em muitas das linguagens de programação conhecidas do mercado, como é o caso do PHP, C, C++, C# ou o próprio Java.
Conheça a formação DevMedia DBA, um mix dos melhores cursos online de banco de dados em um só lugar.
Listagem 1. Sintaxe de concatenação com o operador de adição.
Expressão += Expressão
Ao analisarmos a sintaxe apresentada pela Listagem 1, podemos observar que temos duas expressões separadas pela junção dos sinais de adição e de igualdade (+=). Isto é onde a semelhança destes dois operadores termina. O operador de concatenação de Strings é usado para concatenar valores de cadeia. As expressões em que o operador concatenação podem ser de qualquer tipo de dados válido. O valor concatenado que será retornado pela operação de concatenação irá conter o tipo de dados da expressão com a maior precedência. Assim como o suplemento é igual à primeira expressão (do lado esquerdo) que deve ser variável, e é a variável que conterá o resultado final da operação de concatenação.
O operador += exige que cada expressão a ser analisada seja de um tipo de dado numérico, exceto BIT. Este operador assume o valor de cada expressão e o adiciona, retornando um valor numérico da operação de adição em que o tipo de dados do resultado tem um tipo de dados que a expressão com a maior precedência. A expressão do lado esquerdo do operador += tem de ser uma variável. Como dito anteriormente, a operação de subtração concatenada será apresentada pela Listagem 2, a qual apresenta a sua semelhança.
Listagem 2. Sintaxe de concatenação com o operador de subtração.
Expressão -= Expressão
Assim como a operação utilizando o operador de adição, a string de concatenação com o operador de subtração também possui as duas expressões. Assim como na expressão anterior, ambas as expressões precisam ser de tipos numéricos diferente de BIT. Assim como o operador de +=, o operador de -= assume o valor de cada expressão e subtrai o segundo termo a partir do primeiro e, em seguida, retorna um valor numérico, onde o tipo de dado tem que ser um tipo de dado com a maior precedência. Assim como o operador de +=, o operador de -= requer uma variável para a expressão no lado esquerdo do operador que é igual a subtração, e é esta variável que ficará com o valor resultante da operação.
Talvez tenhamos complicado um pouco na teoria com relação ao assunto, mas vamos começar agora com alguns exemplos para um melhor entendimento com relação ao uso desses operadores. Para nossos exemplos estaremos utilizando o SQL SERVER 2014, caso tenham interesse em criar os exemplos, vocês podem baixar através do site http://msdn.microsoft.com/pt-br/evalcenter/dn434042.aspx
Utilizando o operador de concatenação +=
Começaremos então com um exemplo simples utilizando o operador de adição, onde setaremos duas strings e em seguida, faremos a sua concatenação, o código que estaremos apresentando será de acordo com o apresentado pela Listagem 3.
Listagem 3. Utilização simples da concatenação de strings com o operador +.
-- Concatenação com shortcodes
SET @String = 'Edson';
SET @String += ' Dionisio';
-- Concatenação sem Shortcode
SET @String = @String + ' Dionisio'
Percebam que no código apresentado pela Listagem 3 temos dois exemplos diferentes de como podemos concatenar duas strings juntas. No primeiro conjunto de declarações usamos o nosso atalho (digamos dessa forma) que é o operador de concatenação de String, enquanto que no segundo conjunto de declarações, usamos o operador "+" para concatenar duas strings, de forma bem simples. Como podemos observar, no primeiro conjunto de declarações, só tínhamos especificado a @String uma vez. Ela foi especificada apenas no lado esquerdo do operador +=. Considerando que, no segundo conjunto de instruções, temos que a variável @String é especificada em ambos os lados do operador de igualdade (=). Esses conjuntos de instruções realizam a mesma operação de concatenação. Mas percebam que utilizando o atalho, +=, reduzimos a quantidade de código escrito e tivemos o mesmo resultado, numa forma mais limpa.
Vamos mudar um pouco o contexto de strings para valores numéricos agora. No nosso próximo exemplo apresentaremos a utilização dos nossos atalhos para incrementar e decrementar valores numéricos. Apresentaremos isso de acordo com as Listagens 4 e 5.
Listagem 4. Setando um valor numérico para controle de um loop.
SELECT 'O valor da variável @I é ' + CAST(@I as char(1));
Ao olharmos para o código apresentado pela Listagem 4, podemos perceber que incrementamos o valor da variável @I de 1 cada vez que ele passa no while usando o operador de +=. Somar e subtrair valor a uma variável é algo comum quando estamos escrevendo código no nosso dia a dia. Com a adição dos operadores += e -=, esta operação se torna mais fácil de implementar. É isso que ocorre no nosso exemplo da Listagem 4, onde temos nossa variável iniciando em 1 e que vai ser concatenada daí em diante até chegar ao valor esperado que é o 20. O próximo código a ser apresentado é o da Listagem 5 que é equivalente ao código apresentado pela Listagem 4, mas que não usa o nosso atalho +=.
Listagem 5. Outra maneira de incrementar valores no loop.
Ao compararmos os códigos apresentados pelas Listagens 4 e 5, podemos ver que usamos menos atalhos na Listagem 4 para incrementar a variável @I. o operador de -= pode ser usado apenas como o operador +=. Seguiremos agora para a Listagem 6 que contém um código semelhante ao apresentado pela Listagem 4, mas que diminui o valor da variável de controle do loop WHILE usando o operador de subtração.
Listagem 6. Exemplo de utilização do operador -=.
SELECT 'O valor da variável @I é ' + CAST(@I as char(2));
Em todos os nossos exemplos até este momento usamos uma constante com os operadores de adição ou subtração para aumentar ou diminuir o conjunto da variável. Além disso, também é possível usar as variáveis ou mesmo fórmulas no lado direito destes de ambos os operadores, como apresentaremos no exemplo de acordo com a Listagem 7.
Listagem 7. Usando uma formula com o operador +=.
DECLARE @I int = 3;
DECLARE @X int = 15;
Como podemos observar aqui, temos uma formula matemática sendo apresentada com as variáveis X, Y e I, onde realizamos a multiplicação das variáveis @X e @Y e em seguida somamos o valor da variável @I, o que nos dá um resultado de 33 pra @I.
Agora que vimos com relação a utilização dos operadores de concatenação, chegou a hora de tratarmos da utilização de T-SQL dinâmicas, saber o porquê de utilizá-las, o porquê de sua importância. Sigamos então para um pouco de teoria sobre o assunto.
Porquê utilizar T-SQL dinâmicas?
A primeira pergunta que surge a nossa mente é: o que são as T-SQL dinâmicas? Por que é necessário utilizá-las? Bem, um T-SQL dinâmico é o código que é potencialmente diferente a cada vez que nós o executamos. Ele é um conjunto de instruções T-SQL que é gerado e executado em tempo de execução. Este código é criado com base em algumas condições ou mesmo parâmetros no bloco de instruções. Quando as "condições ou parâmetros" são diferentes do código T-SQL, ele produz uma T-SQL diferente para ser executada.
Geralmente usamos a T-SQL dinâmica quando queremos determinar programaticamente o T-SQL que precisamos com base em parâmetros e/ou com base em dados armazenados em tabelas de banco de dados. Os usos para a T-SQL dinâmica são infinitas. Aqui apresentamos dois exemplos de quando podemos usar uma T-SQL dinâmica, que são quando:
Queremos que o usuário selecione alguns critérios de uma lista suspensa que podem realizar uma consulta para executar de formas diferentes, como uma ordem de classificação.
Sua aplicação não sabe o nome da tabela que poderá ser usada em tempo de execução.
Para um melhor entendimento com relação as T-SQL dinâmicas, apresentaremos alguns exemplos a partir desse momento.
Criando uma T-SQL dinâmica simples
Para o nosso primeiro exemplo de criação de T-SQL's dinâmicas, consideremos a seguinte situação: Suponhamos que nós temos uma aplicação onde a interface com o usuário permite que o usuário selecione a tabela que deseja ler a partir de uma lista suspensa. Com isso, podemos ver de início que cada usuário poderá acessar a tabela que quiser para retornar os dados existentes. Para darmos continuidade a esses exemplos, vamos supor que este usuário exiba informações da tabela do banco de dados AdventureWorks2012_database e o mesmo escolhe a tabela de AdventureWorks2012.Sales.SalesOrderDetail. O código que será apresentado na Listagem 8 nos mostra um método de utilizar o código T-SQL dinâmico para retornar os 10 registros da tabela AdventureWorks2012.Sales.SalesOrderDetail.
Listagem 8. Exemplo de utilização de uma T-SQL dinâmica simples.
-- Declaramos aqui a variável que terá o código da T-SQL dinâmica
DECLARE @COMANDO nvarchar(1000);
-- Declarando o nome da tabela para leitura
DECLARE @Tabela nvarchar(125);
SET @Tabela = 'AdventureWorks2012.Sales.SalesOrderDetail';
-- construindo dinamicamente a declaração da T-SQL
SET @COMANDO = 'SELECT TOP 10 * FROM ' + @Tabela;
-- executando a declaração da T-SQL dinâmica
EXECUTE (@COMANDO);
O código apresentado pela Listagem 8, apresenta primeiramente a declaração da variável chamada @COMANDO que é para armazenar a instrução SELECT dinamicamente que vai ser construída e a @Table que é para manter o nome da tabela. Então definiremos a @Table para AdventureWorks2012.Sales.SalesOrderDetail. Para construirmos a nossa declaração T-SQL de forma dinâmica, usamos uma instrução SET. Esta declaração define a @COMANDO para o valor da cadeia concatenada que contém uma instrução SELECT e o valor da @TABLE. Agora que temos a nossa declaração definida, executemos a nossa declaração T-SQL dinâmica que está contida na @COMANDO.
Para realizarmos um outro teste com a T-SQL dinâmica apresentada pela Listagem 8, podemos substituir a tabela utilizada por uma tabela diferente no código, alterando apenas a instrução "SET @ Table =" para usarmos a tabela de AdventureWorks2012.Sales.Sales.OrderHeader.
Vamos partir agora para um exemplo um pouco mais complexo! Há momentos em que você precisa para escrever alguns códigosT-SQL dinâmicos mais complicados. Nós como DBA's ou mesmo programadores (com privilégios de DBA =) ) nos encontramos em uma daquelas situações em que precisamos gerar um código para executarmos algum tipo de manutenção de banco de dados. O que fazer? O que podemos fazer é construir uma T-SQL dinâmica para fins de manutenção de banco de dados que aplicaríamos da seguinte maneira, leríamos uma visão (view) do sistema e, em seguida, geraríamos um script que seria executado. Suponhamos então que assumimos a manutenção de um banco de dados e que desejamos excluir várias tabelas de teste que foram criadas em uma determinada base de dados. As tabelas possuem nomes que começam com o prefixo "Teste", como uma boa prática. Para demonstrarmos, vamos ler a view sys.tables e gerar as instruções DELETE apropriadas, este código será de acordo com o apresentado pela Listagem 9.
Listagem 9. Código dinâmico para exclusão de tabelas.
-- Criando a base e as tabelas de exemplo
CREATE TABLE Dados1 (Id int, DataDesc varchar(100));
CREATE TABLE Dados2 (Id int, DataDesc varchar(100));
CREATE TABLE TesteDados1 (Id int, DataDesc varchar(100));
CREATE TABLE TesteDados2 (Id int, DataDesc varchar(100));
-- código T-SQL dinâmico para deletar as tabelas
DECLARE @TabelaNome varchar(100);
SELECT TOP 1 @TabelaNome = nome FROM sys.tables
WHERE nome like 'Teste%'
SELECT @COMAND = 'DROP TABLE ' + @TabelaNome + ';';
PRINT @ COMAND
EXECUTE(@COMAND);
WHERE nome like 'Teste%' and nome > @TabelaNome
-- Limpeza profunda!
DROP DATABASE DEVMEDIA;
De acordo com o código apresentado pela Listagem 9, temos três seções diferentes. Na primeira seção, criamos um banco de dados chamado DEVMEDIA, e em seguida criamos quatro tabelas diferentes, duas das quais começam com "Teste". Estas duas tabelas são as tabelas que desejamos apagar com código T-SQL dinamicamente. Na segunda seção apresentada do código está o nosso T-SQL dinâmico. E por fim, na terceira seção do código, limpamos o banco de dados de teste que criamos.
Se analisarmos o código apresentado pela segunda seção, encontraremos o código T-SQL dinâmico que primeiro imprime as instruções de DELETE que será executada, em seguida, exclui as tabelas de teste que criamos na primeira parte, onde fazemos o processamento em um loop while para ver as diferentes tabelas que começam com a cadeia de caracteres "Teste". Para cada tabela que começar com "Teste", construímos um comando DELETE que é armazenado no @COMAND. Daí, apresentamos a declaração DELETE usando uma instrução PRINT, imediatamente seguido da execução da instrução usando a instrução EXECUTE. Na última seção, realizamos a limpeza, deixando o banco de dados que criamos.
Para testarmos este código, sugerimos que a execução seja realizada a cada seção comentada de forma independente, a fim de iniciarmos a partir de primeira seção. Após executar esta seção, verificamos que há quatro tabelas no banco de dados DEVMEDIA. No próximo bloco de código, quando executado, vamos ver duas mensagens sendo exibidas na guia mensagem na janela de Query Analyzer. As duas declarações são mostradas com as duas instruções DELETE que foram geradas e executadas de forma dinâmica. Uma vez que isso é feito, executamos o código da segunda seção, e voltamos a ver as tabelas no nosso banco de dados DEVMEDIA. Se o Pesquisador de Objetos no SQL Server Management Studio estiver ativo, não esqueçam de atualizar. Alternativamente, podemos selecionar apenas do ponto de vista sys.tables. Agora devemos observar que existem apenas duas tabelas, e as duas tabelas excluídas são aquelas que começam com "Teste". Após essa verificação ter sido realizada, executamos o código presente na terceira seção que é responsável pela limpeza. Este é um exemplo muito simples de como examinar as linhas de metadados e gerarmos a T-SQL dinâmica. Como DBA's, haverá muitas oportunidades de usarmos funções como essa.
Agora o que podemos fazer em casos de sql injection? podemos em algum momento, ter obtido a informação de que uma TSQL dinâmica é ruim, onde a parte ruim da T-SQL dinâmica é que ela abre possibilidades para um ataque de SQL Injection. O SQL Injection é uma técnica de hacking que usuários mal intencionados tentam explorar o uso de um campo de entrada de dados de forma livre. Esses usuários mal intencionados tentam inserir um código T-SQL adicional em um campo de entrada de dados de uma forma diferente de como o campo de entrada de dados foi originalmente destinado a ser utilizado. Através da inserção de códigos T-SQL que podem enganar o sistema e retornar dados que originalmente não deveriam ser obtidos, ou poderiam ainda mais, executar comandos adicionais T-SQL contra seu banco de dados SQL Server. Dependendo das permissões que o aplicativo tenha executado sob um ataque de injeção de SQL, isso pode inserir dados em suas tabelas de banco de dados, excluir uma tabela, ou pior ainda configurar um novo login com direitos sysadmin diferentes.
Para demonstrarmos agora como uma T-SQL dinâmica pode estar sujeita a um ataque de SQL Injection, criaremos uma nova base de dados e uma nova tabela de acordo com a Listagem 10, com o intuito de demonstrar como uma T-SQL dinâmica pode ser vulnerável a um ataque de desse tipo.
Listagem 10. Base de dados e tabela criadas para teste de sql injection.
CREATE DATABASE DEVMEDIA2;
USE DEVMEDIA2;
CREATE TABLE Produto(ID int, Nome_produto varchar(100), Preco money);
INSERT INTO Produto VALUES (1, boneco lego vermelho', 12.99),(2, 'Boneco Hulk vermelho', 23.18), (2, 'Carrinho de fricção', 7.59), (2, 'Drone', 177.76);
O código que acabamos de apresentar na Listagem 10 cria o nosso banco de dados chamado de DEVMEDIA2 e, em seguida, criamos e preenchemos uma tabela que chamamos de produto com quatro linhas de dados. Agora, suponhamos que a nossa aplicação tenha uma tela de seleção de dados, onde o usuário poderá inserir uma cadeia de caracteres que está contido em um nome_produto e, em seguida, a aplicação retorne todos os registros da tabela de produtos que contenham a sequência de texto digitado. Após isso, esta sequência de caracteres é processada por uma stored procedure, chamada de GetProdutos, que tem a finalidade de selecionar os dados de acordo com o padrão estabelecido, e, em seguida, estes dados são retornados pela stored procedure e são exibidos para este usuário. O código do stored procedure GetProdutos será se acordo com o apresentado pela Listagem 11.
Listagem 11. Código da stored procedure GetProdutos.
CREATE PROC GetProdutos
(@pesquisa varchar (100))
DECLARE @COMAND varchar(255);
SET @COMAND = 'SELECT nome_produto, Preco ' +
'FROM [DEVMEDIA2].[dbo].Produto ' +
'WHERE nome_produto LIKE ''%' +
@ pesquisa + '%''';
EXEC (@COMAND);
Ao analisarmos nossa procedure GetProdutos presente na Listagem 11, podemos observar que esta stored procedure aceita um único parâmetro que é o @pesquisa. Este parâmetro é então usado para criar dinamicamente uma instrução T-SQL que é armazenada na varável @COMAND. Para demonstrarmos como usar a nossa stored procedure, vamos então executá-la, utilizando o código presente na Listagem 12.
Listagem 12. Executando a stored procedure GetProdutos.
EXEC GetProdutos 'vermelho';
Ao executarmos essa procedure, obtemos o retorno de duas linhas em nossa consulta que contém o termo "vermelho" contido nelas. Como o código presente em nossa stored procedure GetProdutos tem um parâmetro do tipo varchar e gera a variável @COMAND, isso deixa a stored procedure propícia a um ataque de SQL Injection. Podemos demonstrar isso através da execução da stored procedure GetProdutos com o seguinte código apresentado pela Listagem 13.
Listagem 13. Código que mostra como a stored procedure está vulnerável.
EXEC GetProdutos 'vermelho%'' and ID = 1 --';
Se pararmos para analisar o código apresentado pela Listagem 13, veremos que foi passada uma série de outros termos adicionais para a string "vermelho" ao nosso GetProdutos. Esses termos adicionais que foram passados, nos permitem restringir a consulta para retornar somente os produtos que possuem "vermelho" na coluna nome_produto e que possui um valor de ID de 1. Ao permitirmos que o stored procedure possa usar o texto no parâmetro @pesquisa, permitimos a injeção de caracteres adicionais em que o parâmetro pode fazer com que o código execute outras ações que não foram originalmente destinados a realizar no stored procedure GetProdutos.
Até o momento, mostramos um ataque de SQL Injection não destrutivo usando a nossa T-SQL dinâmica com a stored procedure GetProdutos. A maioria dos ataques de SQL Injection busca obter dados adicionais dos nossos sistemas, ou apenas quer corromper nossos bancos de dados. Para mais informações, vamos explorar um pouco mais, vejamos o código apresentado pela Listagem 14.
Listagem 14. SQL injection para retornar dados adicionais.
EXEC GetProdutos 'vermelho'' ;SELECT * FROM [DEVMEDIA2].[dbo].Produto;--';
Ao executarmos o código da Listagem 14, ele irá gerar os dois conjuntos de resultados. Onde o primeiro conjunto de resultados tem zero linhas e o segundo conjunto retorna os registros cadastrados na base anteriormente.
Se formos comparar os resultados de uma execução normal da stored procedure GetProdutos encontrada nos resultados com os resultados encontrados da nossa última execução, veremos que o código da Listagem 14 gerou algumas colunas de saída adicionais que nossa stored procedure não foi originalmente preparada para exibir, mas que acabou exibindo devido a um ataque de SQL Injection.
O nosso exemplo da Listagem 14 ainda não era um uso destrutivo de SQL Injection, mas deu brecha para explorarmos o parâmetro @pesquisa de nossa stored procedure getProdutos para retornar dados de todas as colunas da tabela. Para conseguir isso adicionamos o "'; Select * from [DEVMEDIA].[dbo].produtos; -". Uma observação a se fazer é com relação a adição de dois traços ("-") no fim de nossa string. Isso nos permite comentar o que quer que o código que nossa stored procedure possa ter incluído após o parâmetro. Para finalizarmos o exemplo, faremos agora um ataque destrutivo T-SQL. Vejamos de acordo com o código presente na Listagem 15.
Listagem 15. Código destrutivo de sql injection.
EXEC GetProdutos 'vermelho'' ;DROP TABLE [DEVMEDIA2].[dbo].Produto;--';
Ao executarmos agora nossa instrução, apagamos a nossa tabela! Esse é um tipo de sql injection destrutivo. Agora, vejamos como podemos combater sql injections. Ninguém quer ter seu código comprometido por um ataque de SQL Injection. Então, para combatermos contra estes ataques de SQL, devemos considerar alguns pontos ao desenvolver o código do aplicativo T-SQL, que são:
Não usar SQL dinâmico
Editar os parâmetros que o usuário passou para caracteres especiais como o ponto e vírgula e comentários
Ter parâmetros apenas enquanto for necessário para manter os dados digitados pelo usuário.
Caso devamos usar SQL dinâmico, devemos em seguida usar T-SQL parametrizado que utilize sp_execute sql para executar nossas T-SQL dinâmica, ao invés de usar o EXEC.
Se realmente for necessário a construção do código que contenha T-SQL dinâmico, usar uma T-SQL parametrizada é uma boa maneira de combater o SQL Injection. Na Listagem 16 apresentamos o código alterado de nossa stored procedure.
Listagem 16. Usando parametrizações no T-SQL.
ALTER PROC GetProdutos
DECLARE @COMAND nvarchar(1000);
DECLARE @ParametroCoringa varchar(102);
'WHERE Nome_produto LIKE @pesquisa';
SET @ParametroCoringa = '%' + @pesquisa + '%';
EXEC sp_executesql @COMAND,N'@pesquisa varchar(100)',@pesquisa=@ParametroCoringa;
Ao fazermos estas alterações, o usuário não poderá mais tentar injetar código T-SQL adicional em nosso stored procedure.
Então com isso concluímos o nosso artigo, onde tratamos das questões de utilização de operadores de concatenação, como podemos utilizá-los e a melhor forma de fazermos isso, além disso, tratamos também com relação a utilização de T-SQL dinâmicas em nossas aplicações o que nos ajudam em muitas situações do dia a dia e para finalizar, vimos como nos defender de um sql injection em casos que tenhamos que manter a nossa T-SQL dinâmica a todo custo. Espero que tenham gostado do artigo e até a próxima. =)