Parameters

  • Thread starter Thread starter Shawn Thompson
  • Start date Start date
S

Shawn Thompson

Why should we use the parameter object when it seems like duplicated code. The info is described in SQL Server as int, varchar, etc. so why do we have to redundantly use the parameter object when inserting a record rather than passing the parameter in an SQL query insert code? Just curious???
 
Sql Injection, formatting problems (what is the decimal delimiter?, what is the date format)...
 
There are many reasons if I understand your question correctly:

1- Parameter Direction. Using inline sql without parameters you would have a difficult time retrieving output parameters for instance. And using Output params and return values can be very beneficial to performance
2- Security. You can use UI elements to help safeguard against Injection attacks (for instance, only allowing 10 character passwords to be entered and not allowing statements like "Drop" or any other potentially mischevious stuff). However, Microsoft has already done this with parameters. There's nothing wrong with enacting such measures on your own as an additional safeguard and in fact you probably should, but by using parameters you get an added layer of security in that you don't have to worry about covering every possible situation. In fact, even knowing every possible attack can be quite difficult. And while i'm not saying that every possible scenario is necessarily covered by using
3- It indicates your intentions of the code much more clearly.

As far as performance, I've heard that even if you don't specify precision, for instance you use

cmd.Parameters.Add("@myParam").Value = whatever ;

that you don't lose performance if you're using sql server b/c it's very efficient in this respect but i havent ever confirmed this personally.

all in all though, it's worth it. sometime down the road you're going to get a name like O'Ryan that you forgot to escape and it'll cause some problems. and even if you always remember to escape them, some other developer will forget or not know and at that point, the customer will have already seen the problem. plus injection attacks are one thing that most people know about and in a security audit, it'll be one of the first things that's tried - and if they can inject something in there in an audit, you're going to look bad no matter what you're reason is. all in all you get a lot of bang for the buck here.
Why should we use the parameter object when it seems like duplicated code. The info is described in SQL Server as int, varchar, etc. so why do we have to redundantly use the parameter object when inserting a record rather than passing the parameter in an SQL query insert code? Just curious???
 
One other reason that hasn't been mentioned yet: Avoiding confusion when a
string field contains a single quote mark.

When you pass in a field, say a last name of O'Reilly for example, creating
a sql command would amount to:
select * from employee where last name = 'O'Reilly'

As you can see, the ' after the O will confuse the database. Passing in
O'Reilly as a parameter avoids this headache, and you don't have to write a
lot of code to check for ' inside your passed in strings.

Robert


Why should we use the parameter object when it seems like duplicated code.
The info is described in SQL Server as int, varchar, etc. so why do we have
to redundantly use the parameter object when inserting a record rather than
passing the parameter in an SQL query insert code? Just curious???
 
Back
Top