0

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.

aads
  • 2,255
  • 4
  • 24
  • 25

2 Answers2

2

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.

deroby
  • 5,902
  • 2
  • 19
  • 33
  • 1
    You really should read the Related article [about Bobby Tables](http://stackoverflow.com/questions/332365/how-does-the-sql-injection-from-the-bobby-tables-xkcd-comic-work?rq=1), it explains things much better and references lots of other related information. – deroby Oct 03 '13 at 10:41
  • So this implies .Parameters.Add() will avoid sql injection and we dont want to go for Sps always to tackle it? – aads Oct 03 '13 at 10:53
  • 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. – deroby Oct 03 '13 at 10:58
0

No it doesn't treat parameters as reserve words. Using parametrized stored procedures is best way to avoid sql injection.

Dusht
  • 4,712
  • 3
  • 18
  • 24
  • Thanks for your reply, please provide me a reference where it states Placeholders wont help to prevent sql injection – aads Oct 03 '13 at 09:46
  • Oh... I'm sure I can come up with a parameterised stored procedure that is vulnerable to a SQL Injection Attack. :) – Colin Mackay Oct 03 '13 at 10:43