Why do we always prefer to use parameters in SQL statements?

Question

I am very new to working with databases. Now I can write SELECT , UPDATE , DELETE and INSERT . But I saw many forums on which we prefer to write:

 SELECT empSalary from employee where salary = @salary 

... instead of:

 SELECT empSalary from employee where salary = txtSalary.Text 

Why do we always prefer to use parameters and how to use them?

I wanted to know the use and advantages of the first method. I even heard about SQL injection, but I do not quite understand this. I don’t even know if SQL injection is related to my question.

Answer 1

Using parameters helps prevent SQL Injection attacks when the database is used in conjunction with a software interface such as a desktop program or website.

In your example, the user can directly run SQL code in your database by creating instructions in txtSalary .

For example, if they were to write 0 OR 1=1 , the executable SQL would be

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

as a result, all empSalaries will be returned.

In addition, the user can execute much worse commands against your database, including deleting it if they wrote 0; Drop Table employee 0; Drop Table employee :

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

Then the employee table will be deleted.

---

In your case, it looks like you are using .NET. Using parameters is as simple as:

FROM#

 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 

---

Edit 2016-4-25:

According to a comment by George Stocker, I changed the code sample to not use AddWithValue . In addition, it is usually recommended to wrap IDisposable in using statements.

Answer 2

You are right, this is due to SQL injection , which is a vulnerability that allows the user malicioius to execute arbitrary statements for your database. This old favorite XKCD comic strip illustrates the concept:

---

In your example, if you just use:

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

You are open to SQL injection. For example, let's say someone enters txtSalary:

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

When you execute this query, it will execute SELECT and UPDATE or DROP , or whatever. -- At the end, it simply comments on the rest of your request, which would be useful in an attack if you combine something after txtSalary.Text .

---

The correct way is to use parameterized queries like (C #):

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

In doing so, you can safely complete the request.

To learn how to avoid SQL injection in several other languages, visit bobby-tables.com , a user-maintained SO site.

Answer 3

In addition to the other answers, it must be added that the parameters not only help prevent SQL injection, but can improve query performance . The sql server caches parameterized query plans and reuses them when executing repeated queries. If you have not parameterized your query, then the sql server will compile a new plan for each query (with some exceptions) if the query text is different.

Additional Information About Query Plan Caching

Answer 4

In Sql, when any word contains the @ sign, it means that it is a variable, and we use this variable to set the value in it and use it in the number field on the same sql script, because it is limited to only one script at a time how can you declare many variables of the same type and name in many script. We use this variable in a batch of stored procedures because the stored procedure is a pre-compiled query, and we can pass the values ​​in this variable from the script, desktop and websites for more information: Declare a local variable , Stored Sql and sql procedure injection .

Also read Protect Against SQL Injection , this will help you protect your database.

Hope it helps you understand that any question has commented on me.

Answer 5

Two years after my first option , I’m coming back ...

Why do we prefer options? SQL injection is obviously a big reason, but it may be that we are secretly seeking to return to SQL as a language . SQL in string literals is already a strange cultural practice, but at least you can copy and paste your query into a management studio. SQL, dynamically built with conditional host expressions and control structures when SQL has conditional and control structures, is simply level 0 barbarism. You must run the application in debugging or using tracing to see which SQL it generates.

Do not stop only with options. Go all the way and use QueryFirst (disclaimer: what I wrote). Your SQL lives in a .sql file. You are editing it in a fabulous TSQL editor window with syntax and Intellisense checks for your tables and columns. You can assign test data in the special comments section and click "play" to run your request right there in the window. Creating a parameter is as simple as setting "@myParam" in your SQL. Then each time you save, QueryFirst creates a C # wrapper for your query. Your parameters pop up, are strongly typed, as arguments of Execute () methods. Your results are returned in an IEnumerable or List of strongly typed POCOs, types created from the actual schema returned by your request. If your request is not running, your application will not compile. If your db schema changes and your query executes, but some columns disappear, a compilation error points to a line in your code that is trying to access the missing data. And there are many other benefits. Why do you want to access data in any other way?

Answer 6

Other answers relate to why parameters are important, but there are drawbacks! There are several methods in .net for creating parameters (Add, AddWithValue), but all of them require you to worry, regardless of the parameter name, and all of them reduce the readability of SQL in the code. That's right, when you try to meditate on SQL, you need to hunt around or below to find out what value was used in the parameter.

I humbly argue that my small SqlBuilder class is the most elegant way to write parameterized queries. Your code will look like this:

FROM#

 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(); 

Your code will be shorter and much more readable. You do not even need extra lines, and when you read back, you do not need to look for parameter values. The class you need is here ...

 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(); } } 

Answer 7

Old post, but wanted newbies to know about stored procedures .

My value of 10 cents is that if you can write an SQL statement as a stored procedure, then, in my opinion, this is the best approach. I ALWAYS use stored procedures and never iterate over entries in my main code. For example: SQL Table > SQL Stored Procedures > IIS/Dot.NET > Class .

When you use stored procedures, you can restrict the user to only EXECUTE permission, thereby reducing security risks .

Your stored procedure is essentially parameterized, and you can specify input and output parameters.

A stored procedure (if it returns data using SELECT ) can be accessed and read in the same way as a regular SELECT in your code.

It also works faster since it is compiled on SQL Server.

I also mentioned that you can take several steps, for example, update table, check the values ​​on another database server, and then finally finish returning the data to the client, all on the same server, and not interact with the client. So this is MUCH faster than coding this logic in your code.