Utilizando parametros nas queries com Dapper + SqlServer. (Dotnet)

calendar Jan 15, 2020 · 4 min read · dotNetCore sqlServer microsoft dapper sql parameters queryMode dev  ·
Partilhar: linkedin copy
Utilizando parametros nas queries com Dapper + SqlServer. (Dotnet)

Overview

Neste post animado, mas sério, mergulhamos no mundo do Dapper, um micro-ORM queridinho dos desenvolvedores .NET pelo mundo afora. Apresento como domar essa ferramenta poderosa para evitar as armadilhas comuns que levam às temidas SQL Injections, utilizando exemplos do banco de dados AdventureWorks. Então, se você é fã ou novato no Dapper, prepare-se para uma aventura cheia de dicas práticas para tornar seu código não apenas seguro, mas também elegante e eficiente.

Dapper é um micro-orm grátis e open source muito utilizado. Ele é super simples e muito utilizado… da forma errada. Um dos grandes problemas ao utilizar um ORM é tentar reinventar a roda e concatenar valores na variável que contém as queries.

Este tipo de coisa é um grande ponto de entrada para SQL Injections. Você pode fazer um helper para tentar contornar esta ameaça, mas para que reinventar a roda? Neste post, mostro como usar o Dapper de forma segura.

Dois pontos importantes para este post:

  1. Todos os exemplos utilizam o banco de dados AdventureWorks. Se quiser acompanhar, veja a seção extra deste post. Nele você terá as informações necessárias para gerar um container do Docker com o SqlServer pré carregado com o AdventureWorks.
  2. Considere que existe uma variável chamada connectionString com os dados de conexão com o banco de dados.

Instalando Dapper

Install-Package Dapper

Usings são necessários para este exemplo

using Dapper;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Collections.Generic;

Você vai precisar dos usings acima para estes exemplos.

Começando com um exemplo básico

using(var conn = new SqlConnection(connectionString))
{
        conn.Open();	
	var employees = conn.Query<Employee>($@"
				SELECT
					BusinessEntityID,
					NationalIDNumber,
					LoginID,	                            
					OrganizationLevel,
					JobTitle,
					BirthDate,
					MaritalStatus,
					Gender,
					HireDate,
					SalariedFlag,
					VacationHours,
					SickLeaveHours,
					CurrentFlag,
					ModifiedDate
				FROM HumanResources.Employee").ToList();

	//Do something with the result...
}

No exemplo acima, estou fazendo uma consulta simples e sem argumentos. Só para relembrar o básico.

Fazendo uma consulta utilizando argumentos

using (var conn = new SqlConnection(connectionString))
{
    conn.Open();

    var salariedFlag = 1;
    var currentFlag = 1;

    List<Employee> employees = conn.Query<Employee>($@"
                SELECT
                    BusinessEntityID,
                    NationalIDNumber,
                    LoginID,	                            
                    OrganizationLevel,
                    JobTitle,
                    BirthDate,
                    MaritalStatus,
                    Gender,
                    HireDate,
                    SalariedFlag,
                    VacationHours,
                    SickLeaveHours,
                    CurrentFlag,
                    ModifiedDate
                FROM HumanResources.Employee
                WHERE SalariedFlag = {salariedFlag} AND CurrentFlag = {currentFlag}").ToList();

    //Do something with the result...
}

Este exemplo se parece muito com o anterior, mas inclui duas variaveis na query…. Desta forma, a variável que possui o comando SQL ja está com todos os valores necessarios.

Então…. sim! Este é o jeito errado de utilizar o Dapper! É exatamente isso que não deve ser feito.

Fazendo uma consulta utilizando argumentos… da maneira correta.

using (var conn = new SqlConnection(connectionString))
{
    conn.Open();
    var salariedFlag = 1;
    var currentFlag = 1;

    List<Employee> employees = conn.Query<Employee>($@"
                SELECT
                    BusinessEntityID,
                    NationalIDNumber,
                    LoginID,	                            
                    OrganizationLevel,
                    JobTitle,
                    BirthDate,
                    MaritalStatus,
                    Gender,
                    HireDate,
                    SalariedFlag,
                    VacationHours,
                    SickLeaveHours,
                    CurrentFlag,
                    ModifiedDate
                FROM HumanResources.Employee
                WHERE SalariedFlag = @salariedFlagArg AND CurrentFlag = @currentFlagArg",
                new { salariedFlagArg = salariedFlag, currentFlagArg = currentFlag }).ToList();
	//Do something with the results....
}

Ok, a query acima é a mesma do exemplo errado e os valores são os mesmos, mas a forma de utilizar está diferente.

  1. Adicionei variáveis na query. Elas são exatamente isso: variaveis, então é seguro utilizá-las. No caso, como estou utilizando Sql Server, coloquei a @ como prefixo. Todavia, isso varia de acordo com o banco de dados. O Oracle, por exemplo, exige que você utilize : (dois pontos).
  2. Na sequencia, adicionei outro argumento na chamada da função. Ele é um objeto dinâmico, onde cada propriedade tem o nome de uma variável que eu coloquei na query (sem a @) e cada uma destas propriedades recebe seu respectivo valor.

É isso. Facil, não?

Não precisa se preocupar em converter tipos de variáveis, preparar DateTimes ou strings… O Dapper vai fazer isso tudo para você.

Utilizando parametros dinamicos para fazer uma consulta utilizando argumentos… (também da maneira correta)

using (var conn = new SqlConnection(connectionString))
{
    conn.Open();
    var queryArgs = new DynamicParameters();
    queryArgs.Add("salariedFlagArg", 1);
    queryArgs.Add("currentFlag", 1);

    List <Employee> employees = conn.Query<Employee>($@"
                SELECT
                    BusinessEntityID,
                    NationalIDNumber,
                    LoginID,	                            
                    OrganizationLevel,
                    JobTitle,
                    BirthDate,
                    MaritalStatus,
                    Gender,
                    HireDate,
                    SalariedFlag,
                    VacationHours,
                    SickLeaveHours,
                    CurrentFlag,
                    ModifiedDate
                FROM HumanResources.Employee
                WHERE SalariedFlag = @salariedFlagArg AND CurrentFlag = @currentFlagArg", queryArgs).ToList();
	//Do something with the result.
}

Neste exemplo, a query funciona da mesma forma. A diferença está na forma de montar os parâmetros. Ao invés de criar um objeto dinâmico com as variáveis e seus valores, utilizo um objeto do tipo DynamicParameters.

Com estes objetos, a lógica é a mesma. A função Add recebe dois argumentos: O primeiro é o nome da variaveis que você utilizou na query e o segundo é o valor dela.

É um processo bem simples, mas já é o suficiente para deixar sua aplicação mais eficaz e segura.

No proximo post sobre o assunto, vou mostrar como utilizar o Dapper para selecionar objetos compostos.

Espero ter ajudado!