Why do we always prefer using parameters in SQL statements?

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

In your example, a user can directly run SQL code on your database by crafting statements in txtSalary.

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

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

whereby all empSalaries would be returned.

Further, a user could perform far worse commands against your database, including deleting it If they wrote 0; Drop Table employee:

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

The table employee would then be deleted.


In your case, it looks like you're using .NET. Using parameters is as easy as:

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

As per George Stocker's comment, I changed the sample code to not use AddWithValue. Also, it is generally recommended that you wrap IDisposables in using statements.


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

Her daughter is named Help I'm trapped in a driver's license factory.


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, 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 perform a SELECT and an UPDATE or DROP, or whatever they wanted. The -- at the end simply comments out the rest of your query, which would be useful in the attack if you were concatenating anything after txtSalary.Text.


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

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

With that, you can safely execute the query.

For reference on how to avoid SQL injection in several other languages, check bobby-tables.com, a website maintained by a SO user.