Por que sempre preferimos usar parâmetros em instruções SQL?

Eu sou muito novo para trabalhar com bancos de dados. Agora posso escrever os SELECT , UPDATE , DELETE e INSERT . Mas tenho visto muitos fóruns onde preferimos escrever:

 SELECT empSalary from employee where salary = @salary 

…ao invés de:

 SELECT empSalary from employee where salary = txtSalary.Text 

Por que sempre preferimos usar parâmetros e como usá-los?

Eu queria saber o uso e os benefícios do primeiro método. Eu já ouvi falar de injeção de SQL, mas não entendo completamente. Eu nem sei se a injeção de SQL está relacionada à minha pergunta.

O uso de parâmetros ajuda a evitar ataques de injeção de SQL quando o database é usado em conjunto com uma interface de programa, como um programa ou site da Web.

No seu exemplo, um usuário pode executar diretamente o código SQL em seu database criando instruções em txtSalary .

Por exemplo, se eles escrevessem 0 OR 1=1 , o SQL executado seria

  SELECT empSalary from employee where salary = 0 or 1=1 

pelo qual todos os empSalaries seriam devolvidos.

Além disso, um usuário poderia executar comandos muito piores em seu database, incluindo a exclusão dele se eles escrevessem 0; Drop Table employee 0; Drop Table employee :

 SELECT empSalary from employee where salary = 0; Drop Table employee 

O employee mesa seria então excluído.


No seu caso, parece que você está usando o .NET. Usar parâmetros é tão fácil quanto:

C #

 string sql = "SELECT empSalary from employee where salary = @salary"; using (SqlConnection connection = new SqlConnection(/* connection info */)) using (SqlCommand command = new SqlCommand(sql, connection)) { var salaryParam = new SqlParameter("salary", SqlDbType.Money); salaryParam.Value = txtMoney.Text; command.Parameters.Add(salaryParam); var results = command.ExecuteReader(); } 

VB.NET

 Dim sql As String = "SELECT empSalary from employee where salary = @salary" Using connection As New SqlConnection("connectionString") Using command As New SqlCommand(sql, connection) Dim salaryParam = New SqlParameter("salary", SqlDbType.Money) salaryParam.Value = txtMoney.Text command.Parameters.Add(salaryParam) Dim results = command.ExecuteReader() End Using End Using 

Editar 2016-4-25:

Conforme o comentário de George Stocker, eu mudei o código de exemplo para não usar AddWithValue . Além disso, geralmente é recomendável que você inclua IDisposable s em instruções.

Você está certo, isso está relacionado à injeção de SQL , que é uma vulnerabilidade que permite que um usuário do malicioius execute declarações arbitrárias em seu database. Este antigo quadrinho XKCD favorito ilustra o conceito:

insira a descrição da imagem aqui


No seu exemplo, se você acabou de usar:

 var query = "SELECT empSalary from employee where salary = " + txtSalary.Text; // and proceed to execute this query 

Você está aberto para injeção de SQL. Por exemplo, digamos que alguém insira txtSalary:

 1; UPDATE employee SET salary = 9999999 WHERE empID = 10; -- 1; DROP TABLE employee; -- // etc. 

Quando você executa esta consulta, ela executará um SELECT e um UPDATE ou DROP , ou o que eles quiserem. O -- no final simplesmente comenta o resto da sua consulta, o que seria útil no ataque se você estivesse concatenando algo depois de txtSalary.Text .


A maneira correta é usar consultas parametrizadas, por exemplo (C #):

 SqlCommand query = new SqlCommand("SELECT empSalary FROM employee WHERE salary = @sal;"); query.Parameters.AddWithValue("@sal", txtSalary.Text); 

Com isso, você pode executar a consulta com segurança.

Para referência sobre como evitar a injeção de SQL em vários outros idiomas, verifique bobby-tables.com , um site mantido por um usuário SO .

Em Sql quando qualquer palavra contém @ sign significa que é variável e usamos essa variável para definir valor nela e usá-la na área numérica no mesmo script sql porque ela é restrita apenas no script único, enquanto você pode declarar muitas variables do mesmo tipo e nome em muitos scripts. Usamos essa variável no lote de stored procedures porque o procedimento armazenado é consultas pré-compiladas e podemos passar valores nessas variables ​​do script, desktop e sites para obter mais informações. Leia Declare Local Variable , Sql Stored Procedure e injeções sql .

Leia também Protect from sql injection irá guiar como você pode proteger seu database.

Espero que ajude você a entender também qualquer questão me comentar.

Outras respostas cobrem porque os parâmetros são importantes, mas há uma desvantagem! Em .net, existem vários methods para criar parâmetros (Add, AddWithValue), mas todos eles exigem que você se preocupe, desnecessariamente, com o nome do parâmetro, e todos eles reduzem a legibilidade do SQL no código. Quando você está tentando meditar no SQL, você precisa procurar acima ou abaixo para ver qual valor foi usado no parâmetro.

Humildemente afirmo que minha pequena class SqlBuilder é a maneira mais elegante de escrever consultas parametrizadas . Seu código ficará assim …

C #

 var bldr = new SqlBuilder( myCommand ); bldr.Append("SELECT * FROM CUSTOMERS WHERE ID = ").Value(myId); //or bldr.Append("SELECT * FROM CUSTOMERS WHERE NAME LIKE ").FuzzyValue(myName); myCommand.CommandText = bldr.ToString(); 

Seu código será mais curto e muito mais legível. Você nem precisa de linhas extras e, quando está lendo de volta, não precisa procurar pelo valor dos parâmetros. A turma que você precisa está aqui …

 using System; using System.Collections.Generic; using System.Text; using System.Data; using System.Data.SqlClient; public class SqlBuilder { private StringBuilder _rq; private SqlCommand _cmd; private int _seq; public SqlBuilder(SqlCommand cmd) { _rq = new StringBuilder(); _cmd = cmd; _seq = 0; } public SqlBuilder Append(String str) { _rq.Append(str); return this; } public SqlBuilder Value(Object value) { string paramName = "@SqlBuilderParam" + _seq++; _rq.Append(paramName); _cmd.Parameters.AddWithValue(paramName, value); return this; } public SqlBuilder FuzzyValue(Object value) { string paramName = "@SqlBuilderParam" + _seq++; _rq.Append("'%' + " + paramName + " + '%'"); _cmd.Parameters.AddWithValue(paramName, value); return this; } public override string ToString() { return _rq.ToString(); } } 

Além de outras respostas precisam adicionar que os parâmetros não só ajudam a evitar a injeção de SQL, mas podem melhorar o desempenho das consultas . Sql server caching parametrizou os planos de consulta e os reutilizou na execução de consultas repetidas. Se você não parametrizasse sua consulta, o SQL Server compilaria um novo plano em cada consulta (com alguma exclusão) se o texto da consulta fosse diferente.

Mais informações sobre o armazenamento em cache do plano de consulta

Dois anos depois da minha primeira vez , estou recidivando …

Por que preferimos parâmetros? Injeção de SQL é obviamente uma grande razão, mas será que estamos secretamente desejando voltar ao SQL como uma linguagem . O SQL em literais de string já é uma prática cultural estranha, mas pelo menos você pode copiar e colar sua solicitação no estúdio de gerenciamento. O SQL construído dinamicamente com condicionais de linguagem de host e estruturas de controle, quando o SQL tem condicionais e estruturas de controle, é apenas barbarismo de nível zero. Você precisa executar seu aplicativo na debugging, ou com um rastreamento, para ver o que o SQL gera.

Não pare apenas com parâmetros. Vá todo o caminho e use QueryFirst (disclaimer: que eu escrevi). Seu SQL mora em um arquivo .sql . Você o edita na fabulosa janela do editor TSQL, com validação de syntax e Intellisense para suas tabelas e colunas. Você pode atribuir dados de teste na seção de comentários especiais e clicar em “reproduzir” para executar sua consulta ali mesmo na janela. Criar um parâmetro é tão fácil quanto colocar “@myParam” no seu SQL. Em seguida, cada vez que você salvar, o QueryFirst gera o wrapper C # para sua consulta. Seus parâmetros aparecem, fortemente typescripts, como argumentos para os methods Execute (). Seus resultados são retornados em um IEnumerable ou Lista de POCOs fortemente tipados, os tipos gerados a partir do esquema real retornado pela sua consulta. Se sua consulta não for executada, seu aplicativo não será compilado. Se seu esquema de db mudar e sua consulta for executada, mas algumas colunas desaparecerem, o erro de compilation apontará para a linha em seu código que tenta acessar os dados ausentes. E existem inúmeras outras vantagens. Por que você deseja acessar dados de outra maneira?

Post antigo, mas queria garantir que os recém-chegados estejam cientes dos stored procedures .

Meu 10c vale a pena aqui é que se você é capaz de escrever sua instrução SQL como um procedimento armazenado , que na minha opinião é a melhor abordagem. Eu sempre uso procs armazenados e nunca loop através de registros no meu código principal. Por exemplo: SQL Table > SQL Stored Procedures > IIS/Dot.NET > Class .

Quando você usa stored procedures, pode restringir o usuário a EXECUTAR somente a permissão, reduzindo assim os riscos de segurança .

Seu procedimento armazenado é inerentemente parametrizado e você pode especificar os parâmetros de input e saída.

O procedimento armazenado (se ele retornar dados via SELECT ) pode ser acessado e lido da mesma maneira que você faria com uma SELECT regular em seu código.

Ele também é executado mais rapidamente conforme é compilado no SQL Server.

Também mencionei que você pode executar várias etapas, por exemplo, update uma tabela, verificar valores em outro servidor de database e, depois de concluído, retornar os dados para o cliente, todos no mesmo servidor, sem interação com o cliente. Portanto, isso é MUITO mais rápido do que codificar essa lógica em seu código.