Does sqlCommand.Parameters.Add(sqlParam) checks reserve words such as 'Table', 'Drop' etc. Basically i want to know by using above how we can avoid Sql Injection what is the mechanism over there.
2 Answers
It all depends on what you plan to do with the parameters in the SQL you are executing. The good thing about using .Parameters.Add() is that the values are passed seperately and not part of 1 big chunk-o-sql. Off course it's up to you to decide what to do with them then.
Assuming you do something like this:
SELECT * FROM myTable WHERE customer_nr = @customer_nr
Then it doesn't really matter if a 'hacker' passed something along the lines of ';DROP TABLE myTable --. The query will simply not return anything because no customer is named `';DROP TABLE myTable --'
However, if you're going to use it like this:
SELECT @sql = 'SELECT * FROM myTable WHERE customer_nr = ''' + @customer_nr + ''''
EXEC (@sql)
then you defeat the purpose of the system and the hacker WILL be able to do some SQL-Injection-ish stuff.
3 Comments
deroby
You really should read the Related article about Bobby Tables, it explains things much better and references lots of other related information.
aads
So this implies .Parameters.Add() will avoid sql injection and we dont want to go for Sps always to tackle it?
deroby
Parameters.Add() will indeed help you out with avoiding SQL-Injection. Whether you do the actual SQL commands directly in the .CommandText or rather via a stored procedure doesn't really matter on that part (although I prefer stored procedures!). Simply don't fall in the trap of converting the parameters back to dynamic SQL, either in the .CommandText or in the stored procedure, like I stated above.
No it doesn't treat parameters as reserve words. Using parametrized stored procedures is best way to avoid sql injection.
2 Comments
aads
Thanks for your reply, please provide me a reference where it states Placeholders wont help to prevent sql injection
Colin Mackay
Oh... I'm sure I can come up with a parameterised stored procedure that is vulnerable to a SQL Injection Attack. :)