What Is Passing Parameters to SQL and Why Do I Need It

What is passing parameters to SQL and why do I need it?

Passing parameters to SQL saves you from having to build a dynamic SQL string.

Building dynamic SQL statements is a HUGE security risk because people can inject their own SQL code into your application, possibly executing undesirable commands against your data.

There are some good samples of possible SQL Injection attacks at:

SQL Injection Attacks by Example

There are two ways of passing parameters to SQL statements. One is to use Stored Procedures like you mentioned. The other is to use parameterized queries (which is actually what I prefer).

A parameterized query is actually quite easy in .NET:

using(SqlConnection conn = new SqlConnection(connString))
{
    SqlCommand command = 
        new SqlCommand("SELECT * FROM Users WHERE Username = @Username", conn);

    command.Parameters.Add(new SqlParameter("@Username", "Justin Niessner"));

    SqlDataAdapter adapter = new SqlDataAdapter(command);
    DataTable dt = new DataTable();

    adapter.Fill(dt);
}

In that example, the parameter was @Username and we used the Parameters collection of the SqlCommand object to pass in the value.

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.

From what do sql parameters protect you?

Parameterized queries generally quote the parameter if it is a string behind the scene so that the normal SQL operators are not interpreted as such. This means that even if a user enters potentially malicious data it is simply treated as a string input and not interpreted as SQL operators/commands.

There may be technical differences in how it is implemented in the various frameworks, but the basic idea (and result) is the same.

Passing parameters to a SQL view

You can use the view inside stored procedure and filter based on parameter

CREATE PROCEDURE s_emp
(
    @enoNumber INT
) 
AS 
BEGIN
   SELECT *
   FROM VIEW_NAME
   WHERE COLUMN_NAME =  @enoNumber
END

Passing parameter to IN SQL statement

Its a common mistake - you are passing a single value (expression) of type string to IN operator but IN expects a comma delimited list of values (expressions) and not a single string variable.

What you need to do here is to have a function that would split the given parameter into a multiple values based on given delimiter and then use that list with IN keyword. For example,

SELECT DISTINCT Details from tbData WHERE Name IN (SELECT Val FROM dbo.efn_Split(@svt, ',')) AND Address=@ser

where efn_Split is a table value function that will split comma-separated values into a table. See these various SO questions for implementation of such function:

Split function equivalent in T-SQL?

How to split string using delimiter char using T-SQL?

Yet another alternative is to construct the SQL statement and execute with sp_executesql.

How to pass parameters to Microsoft SQL Server query?

DECLARE @someVariable varchar(n)
SET @someVariable = 'variable'

SELECT col1, col2
FROM table           
WHERE col3 = someVariable 

---

Related Topics