CODEWITHSUNDEEP
c#sql.net
theGleep
theGleep

Reputation: 1187

in C# SQL queries, what is the advantage of Parameters.Add() over embedding the values directly in the query?

lately, I've seen a lot of people using the Parameters property of an SQLCommand to provide simple values to the query - like this:

var one = "one";
var two = "two";

DataTable results = new DataTable();
string cmdTxt = String.Format("select NAME from TABLE where NAME like @ONE or NAME like @TWO");
using (SqlCommand cmd = new SqlCommand(cmdTxt, Butch.connection))
{
    cmd.Parameters.Add("@ONE", SqlDbType.Char, 15).Value = "%" + one + "%";
    cmd.Parameters.Add("@TWO", SqlDbType.Char, 65).Value = "%" + two + "%";
    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
           adapter.Fill(results);
 }

Historically, I've just embedded the values in the query string like this:

var one = "one";
var two = "two";

DataTable results = new DataTable();
string cmdTxt = String.Format($"select NAME from TABLE where NAME like '%{one}%' or NAME like '%{two}%");
using (SqlCommand cmd = new SqlCommand(cmdTxt, Butch.connection))
{
    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
           adapter.Fill(results);
}

I know that there are situations where cmd.Parameters.Add() is specifically useful (like inserting binary data), so I'm not asking about those.

I'm specifically asking:

For a simple value, what is the advantage to using cmd.Parameters.Add() (as in the first code example) rather than embedding that value in the query string (as in the second example)?

Edit to add:

Some of the comments have referenced the fact that I declared variables one and two as hard-coded. I did that so that the example would be complete - naturally, the variables would come from somewhere else.

Upvotes: 1

Views: 109

Answers (2)

David Wright
David Wright

Reputation: 805

Using parameters is much better!

To process a query, SQL goes through several steps. First it parses the query text to check its syntax and create an AST representation. Then it gives the AST to the query planner to produce a plan that will be used to execute the query. Then it executes the plan and returns the result.

If you give it the same query text again, it will recognize that and skip the parsing and planning steps, just re-executing the plan. For many queries, parsing and planning take much longer than plan execution, so that can be a tremendous performance gain. When a query is parameterized, the plan itself accepts parameters, so it can be re-used. When values appear literally in the query text, the text needs to be re-parsed and re-planned every time the values change.

Protection against injection is another advantage. If you construct the query text as

command.CommandText = $"SELECT * FROM [Students] WHERE [Name] = '{name}'";

then someone who enters their name as "Robert'; DROP TABLE [Students]" can destroy your DB. But if you use parameterized, fixed query text and add the same name as a parameter,

command.CommandText = "SELECT * FROM [Students] WHERE [Name] = @name";
...
command.Paramers.Add("@name",name);

then the DB will do its job of looking for that name in the DB, without destroying it.

Upvotes: 3

sticky bit
sticky bit

Reputation: 37472

What the comments try to tell you: To counter attacks using SQL injection. And that relatively effortless for the application programmer.

The values are adequately quoted, escaped and transformed in the right way for the targeted DBMS. That hinders special symbols to be inserted in a way that can change the statement (in a malicious way). You don't have to do that yourself, you cannot forget it or do it wrong e.g. by missing one case.

Bonus: The code is easier to port to target another DBMS. As the logic of how values have to be transformed, in a way the targeted DBMS understands them, is in the driver and no longer the job of the application programmer. It's not always a 100% though.

Upvotes: 0

Related Questions