CODEWITHSUNDEEP
c#sqlsql-servert-sqlc#-4.0
user2520528
user2520528

Reputation:

Why use SqlParameter[] and not embed parameters?

I have a sqlhelper class that includes an overloaded ExecuteNonQuery: one with just one parameter (commandText) and another one with two parameters (commandText, SqlParameter[]).

Assuming I have a stand-alone console application with no user interaction, and I will call a stored procedure that will just update a table with 3 parameters, what are the benefits of using SqlParameter[] if I can just as easily build the string and just send it as commandText?

In other words, why use the following:

SqlParameter[] parameters =
    {    
        new SqlParameter("parm1" SqlDbType.VarChar, 3),
        new SqlParameter("parm2", SqlDbType.VarChar, 8),
        new SqlParameter("parm3", SqlDbType.VarChar, 2),
        new SqlParameter("parm4", SqlDbType.VarChar, 4)
    };

parameters[0].Value = p1;
parameters[1].Value = p2;
parameters[2].Value = p3;
parameters[3].Value = p4;

When I can use something like this:

strQueryToRun = string.Format("exec updateTable {0}, {1}, {2}, {3}", p1, p2, p3, p4);

This is a stand-alone console application so there's no possibility of sql injection.

Thanks.

Upvotes: 1

Views: 9517

Answers (3)

Ali Adlavaran
Ali Adlavaran

Reputation: 3735

Simply because of two reasons:

  1. Parameters help to enforce strong data typing, which you lose using the string way!
  2. To avoid SQL injection

that's all.

Upvotes: 4

Adam Robinson
Adam Robinson

Reputation: 185593

The first and absolutely most important reason is so that your query does what you expect it to do, not what someone maliciously makes it do. Have a look at the Wikipedia article on SQL Injection.

In addition to mitigating (effectively eliminating) the risk of SQL injection, using parameters also allows SQL Server to take advantage of cached query plans. This is less of an issue in your specific instance (where you're simply calling a stored procedure, whose plan is almost certainly already compiled and cached), but this is a more general reason why you need to parameterize your queries.

Another reason (as pointed out by Ali in another answer) is that using the string.Format method is going to give your parameters whatever the string representation is of the native .NET type. For numerics, this is not an issue. For string types, you would have to enclose in single quotes and properly escape any embedded quotes (and likely other sanitizing routines). Using the parameter lets the SQL client libraries worry about how the data gets passed to the server.

That said, I would not use the code as you have written it above. I wouldn't construct an array of SqlParameters at all. There are a variety of ways to add a parameter to a SqlCommand (or DbCommand or whatever you're using), such as AddWithValue that provides a less verbose mechanism that is sufficient for most parameters that get added.

Even ignoring AddWithValue, I would still create individual variables for each parameter and name them something meaningful.

var parm1 = new SqlParameter("parm1", SqlDbType.VarChar, 3);
var parm2 = new SqlParameter("parm2", SqlDbType.VarChar, 8);
var parm3 = new SqlParameter("parm3", SqlDbType.VarChar, 2);
var parm4 = new SqlParameter("parm4", SqlDbType.VarChar, 4);

parm1.Value = p1;
parm2.Value = p2;
parm3.Value = p3;
parm4.Value = p4;

(Obviously a name like parm1 or parm2 is far from meaningful, but I would assume that your actual parameter names have more meaning than your example)

Upvotes: 6

Scott Chamberlain
Scott Chamberlain

Reputation: 127543

To avoid SQL injection. For example if I make my arguments be 1, 2, 3, 4;\nDROP PROCEDURE updateTable; it would cause the following to be executed by your sever.

exec updateTable 1, 2, 3, 4;
DROP PROCEDURE updateTable;

Upvotes: 1

Related Questions