CODEWITHSUNDEEP
parametersado.net
dtv8997
dtv8997

Reputation: 1

Why should you use parameters?

I see a lot on how to use parameters in ADO.net and the great website that got me started in c# http://csharp-station.com/Tutorial/AdoDotNet/Lesson06, says..

        // don't ever do this
        // SqlCommand cmd = new SqlCommand(
        // "select * from Customers where city = '" + inputCity + "'");

but I can’t seem to get the answer to why you should not do this. No doubt there is a good reason but i can’t seem to find it. Could someone fill me in please so i can answer this to new comers in the future (I like to help guys at work learn code). To a new guy learning c#, I think most would see that it's fewer lines of code, which seems like a win. Is it simply because adding strings together is more taxing than using parameters?

Upvotes: 0

Views: 86

Answers (1)

Joel Coehoorn
Joel Coehoorn

Reputation: 416081

Think carefully about what would happen if someone entered the following into the city textbox in your app:

';DELETE FROM Customers;--

You would end up constructing an sql string that looks like this:

select * from Customers where city = '';DELETE FROM Customers;--'

It just so happens that works out to two statement and a comment, and your sql provider is perfectly happy to execute both of them. Now, it's also possible the database connection is running as user without the privileges needed to delete from that table... but it may have other privileges, such as reading data from other tables to get things like social security numbers or credit cards, or perhaps inserting a new account record with administrative privileges. A skilled attacking can use this to do almost anything.

It may be tempting to write a simple escaping or sanitization function instead. Don't do this. First of all, it doesn't fully solve the problem. Query parameters completely segregate the user data from the sql code, such that even the database always treats the user input like a variable value, and never put's it directly into a query string. Using an escape function puts you into an arms race with the crackers.

Another reason is for performance. Databases go to a lot of trouble to turn queries like into execution plans for how to retrieve the data. So much so, that they often cache the plans, with a hash of the query text as the key. If you don't use parameterized queries, you end up with a cache miss every time.

Finally, I just plain find parameterized queries easier to work with. For complicated queries, what I might do is have a comment above the query, like this:

/*DECLARE @someVariable varchar(12);
  DECLARE @OtherVariable varchar(50);
  DECLARE @thirdVariable int;
  ...*/
var sql = 
"SELECT <columns>
 FROM table t"
 INNER JOIN othertable o ON t.ID = o.tableID"
 WHERE someColumn = @someVariable AND ...";

using (var cn = new SqlConnection(" ... "))
using (var cmd = new SqlCommand(sql, cn))
{
    cmd.Parameters.Add()
    //...

What this does is when it comes time to maintain this query, I can quickly copy/paste the query and it's variable declarations into Management Studio or other query tool, and when I'm done I can quickly move the whole mess back to the client code. It's not such a big deal for one liners, and really huge/complicated queries often end up as stored procedures or views, but there is this sweet spot where this is really helpful.. and it turns out more things end up in that sweet spot than you might think.

Upvotes: 1

Related Questions