Security for passing parameters into commands

[Skilleddreamer]

I've read that when passing parameters to the commandtext method for the IDbcommand object I shouldn't build the command string as

command.CommandText = _
"SELECT * FROM CUSTOMERS WHERE CITY = '" & _
inputCity + "'";

but instead use the SqlParameter object as this is more secure

command.CommandText = _
"SELECT * FROM CUSTOMERS WHERE CITY =@City"

my question is, why is this more secure because surely I'm assigning a string value to my parameter anyway. Are the two methods majorly different?

[si_the_geek]

There are a few reasons they are more secure, and several other reasons that they are better too.

For an explanation of some of the reasons, see the article Why should I use Parameters instead of putting values into my SQL string? from our Database Development FAQs/Tutorials (at the top of this forum).

[abhijit]

There is also one more advantage to using parameters and that is performance.

To understand this advantage, you need to know what happens when you a query on the database.
The query is parsed by the database.
In an Oracle Database, this is done in two phases.
1) Hard Parse
2) Soft Parse

The database must perform a hard parse instead of a soft parse if the parsed representation of a submitted statement does not exist in the shared pool. The hard parse takes more time and is more taxing compared to the softparse.

When you choose to use command parameters, for subsequent sql queries, the DB does not have to do a hard-parse. So the execution speed increases.


If you're interested in learning about binding / soft parse / hard parse, I suggest you read this book.