By Sandy

2011-09-21 19:51:57 8 Comments

I am very new to working with databases. Now I can write SELECT, UPDATE, DELETE, and INSERT commands. But I have seen many forums where 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 would I use them?

I wanted to know the use and benefits of the first method. I have even heard of SQL injection but I don't fully understand it. I don't even know if SQL injection is related to my question.


@NullUserException 2011-09-21 19:59:19

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, a website maintained by a SO user.

@Bogdan Sahlean 2011-09-21 20:40:54

@Sandy 2011-09-21 20:48:43

great solution. But can you explain a bit more, why and how using parameters is safe. I mean it still looks like the sql command will be same.

@marc_s 2011-09-21 20:57:33

@user815600: a common misconception - you still believe that the query with parameters will take in the value and substitute the parameters for the actual values - right? No this is not happening! - instead, the SQL statement with parameters will be transmitted to SQL Server, along with a list of parameters and their values - the SQL statement is not going to be the same

@Sandy 2011-09-21 21:01:55

that means sql injection is being monitored by sql server internal mechanism or security. thanks.

@philw 2013-08-03 16:34:04

Much as I like cartoons, if you're running your code with sufficient privilege to drop tables, you probably have wider issues.

@CeOnSql 2015-10-16 06:42:18

great cartoon :D

@Andrea Girardi 2018-07-25 14:59:01

Best answer I have seen in SO

@Arvin Amir 2017-06-07 00:02:59

Old post but wanted to ensure newcomers are aware of Stored procedures.

My 10ยข worth here is that if you are able to write your SQL statement as a stored procedure, that in my view is the optimum approach. I ALWAYS use stored procs and never loop through records 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 EXECUTE permission only, thus reducing security risks.

Your stored procedure is inherently paramerised, and you can specify input and output parameters.

The stored procedure (if it returns data via SELECT statement) can be accessed and read in the exact same way as you would a regular SELECT statement in your code.

It also runs faster as it is compiled on the SQL Server.

Did I also mention you can do multiple steps, e.g. update a table, check values on another DB server, and then once finally finished, return data to the client, all on the same server, and no interaction with the client. So this is MUCH faster than coding this logic in your code.

@bbsimonbb 2017-02-08 15:35:46

Two years after my first go, I'm recidivating...

Why do we prefer parameters? SQL injection is obviously a big reason, but could it be that we're secretly longing to get back to SQL as a language. SQL in string literals is already a weird cultural practice, but at least you can copy and paste your request into management studio. SQL dynamically constructed with host language conditionals and control structures, when SQL has conditionals and control structures, is just level 0 barbarism. You have to run your app in debug, or with a trace, to see what SQL it generates.

Don't stop with just parameters. Go all the way and use QueryFirst (disclaimer: which I wrote). Your SQL lives in a .sql file. You edit it in the fabulous TSQL editor window, with syntax validation and Intellisense for your tables and columns. You can assign test data in the special comments section and click "play" to run your query right there in the window. Creating a parameter is as easy as putting "@myParam" in your SQL. Then, each time you save, QueryFirst generates the C# wrapper for your query. Your parameters pop up, strongly typed, as arguments to the Execute() methods. Your results are returned in an IEnumerable or List of strongly typed POCOs, the types generated from the actual schema returned by your query. If your query doesn't run, your app won't compile. If your db schema changes and your query runs but some columns disappear, the compile error points to the line in your code that tries to access the missing data. And there are numerous other advantages. Why would you want to access data any other way?

@Chad Levy 2011-09-21 19:54:15

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;

    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


        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.

@Sandy 2011-09-21 20:43:19

great help. just by reading this solution, the first thing i did is tried to hack my own program. and volla, i did it successfully. Thanks again

@Sandy 2011-09-21 20:49:04

great solution. But can you explain a bit more, why and how using parameters is safe. I mean it still looks like the sql command will be same

@Sandy 2011-09-21 20:56:33

can we add multiple parameters to sql command. Like we may require in a INSERT Command?

@Chad Levy 2011-09-21 20:57:18

SQL Server treats the text inside the parameters as input only and will never execute it.

@Chad Levy 2011-09-21 20:57:57

Yes, you can add multiple parameters: Insert Into table (Col1, Col2) Values (@Col1, @Col2). In your code you'd add multiple AddWithValues.

@Si8 2014-05-30 13:50:56

How can I use the C# code to allow multiple variable?

@George Stocker 2015-03-11 15:41:12

Please don't use AddWithValue! It can cause implicit conversion issues. Always set the size explicitly and add the parameter value with parameter.Value = someValue.

@LuckyLikey 2015-09-09 09:31:20

Doing it the way you do alaways throws me an OdbcException the @key scalar-variable needs to be declared. I don't get why. I've doublechecked that the key is the same and in the query its written with a leading '@'. May it be because it's a DateType or because im using an OdbcConnection instead of sql?

@LuckyLikey 2015-09-09 12:09:27

Got it.. ODBC does only support "?" as parameter. The inserted Parameter is distinguished by parameters-order. look here

@Andrew Morton 2017-03-16 19:30:17

You should really use salaryParam.Value = CDec(txtMoney.Text): SQL Server money is Decimal in .NET: SQL Server Data Type Mappings. And the parameter name needs the "@", as in "@salary".

@Ian 2018-08-30 13:01:31

There's an override to SqlParameter.Add() that allows you to do all of the above parameter-adding on one line. The three lines would become command.Parameters.Add("salary", SqlDbType.Money).Value = txtMoney.Text;

@Oleg 2015-06-10 21:27:40

In addition to other answers need to add that parameters not only helps prevent sql injection but can improve performance of queries. Sql server caching parameterized query plans and reuse them on repeated queries execution. If you not parameterized your query then sql server would compile new plan on each query(with some exclusion) execution if text of query would differ.

More information about query plan caching

@James 2017-01-12 17:10:24

This is more pertinent than one might think. Even a "small" query can be executed thousands or millions of times, effectively flushing the entire query cache.

@bbsimonbb 2014-12-05 09:09:29

Other answers cover why parameters are important, but there is a downside! In .net, there are several methods for creating parameters (Add, AddWithValue), but they all require you to worry, needlessly, about the parameter name, and they all reduce the readability of the SQL in the code. Right when you're trying to meditate on the SQL, you need to hunt around above or below to see what value has been used in the parameter.

I humbly claim my little SqlBuilder class is the most elegant way to write parameterized queries. Your code will look like this...


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

Your code will be shorter and much more readable. You don't even need extra lines, and, when you're reading back, you don't need to hunt around for the value of parameters. 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)
    return this;
public SqlBuilder Value(Object value)
    string paramName = "@SqlBuilderParam" + _seq++;
    _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();

@Dave R. 2015-05-05 16:48:02

Naming your parameters certainly helps when profiling the queries the server is running.

@bbsimonbb 2015-06-04 09:09:11

My boss said the same thing. If meaningful parameter names are important to you, add a paramName argument to the value method. I suspect you're needlessly complicating things.

@Adam Calvet Bohl 2017-01-25 07:45:30

Bad idea. As it was said before, AddWithValue can cause implicit conversion issues.

@bbsimonbb 2017-01-25 08:19:15

@Adam you're right, but that doesn't stop AddWithValue() from being very widely used, and I don't think it invalidates the idea. But in the meantime, I've come up with a much better way of writing parameterized queries, and that doesn't use AddWithValue() :-)

@Adam Calvet Bohl 2017-01-25 08:31:19

Right! Promise I'm going to look at that soon!

@Emaad Ali 2011-09-21 20:00:37

In Sql when any word contain @ sign it means it is variable and we use this variable to set value in it and use it on number area on the same sql script because it is only restricted on the single script while you can declare lot of variables of same type and name on many script. We use this variable in stored procedure lot because stored procedure are pre-compiled queries and we can pass values in these variable from script, desktop and websites for further information read Declare Local Variable, Sql Stored Procedure and sql injections.

Also read Protect from sql injection it will guide how you can protect your database.

Hope it help you to understand also any question comment me.

Related Questions

Sponsored Content

42 Answered Questions

[SOLVED] How to return only the Date from a SQL Server DateTime datatype

45 Answered Questions

16 Answered Questions

[SOLVED] How can I do an UPDATE statement with JOIN in SQL?

7 Answered Questions

28 Answered Questions

[SOLVED] How can I prevent SQL injection in PHP?

25 Answered Questions

33 Answered Questions

[SOLVED] How do I UPDATE from a SELECT in SQL Server?

25 Answered Questions

[SOLVED] How do I perform an IF...THEN in an SQL SELECT?

4 Answered Questions

[SOLVED] Inserting multiple rows in a single SQL query?

37 Answered Questions

Sponsored Content