4

What changes SqlCommand.Parameters.AddWithValue() does with the query?

I expect that:

  1. It replaces every ' character by '',

  2. If a parameter value is a string or something which must be converted to a string, it surrounds the value by ', so for example select * from A where B = @hello will give select * from A where B = 'hello world'.

  3. If a parameter value is something "safe" like an integer, it is inserted in a query as is, without quotes, so select * from A where B = @one would give select * from A where B = 1.

Is there any other changes I'm not aware of?

Arseni Mourzenko
  • 50,338
  • 35
  • 112
  • 199

2 Answers2

8

The ADO.NET SqlClient driver will not do any replacements! That's a common misconception - it avoids the trouble of replacing anything.

What it does is pass your query with the parameters @param1 ... @paramN straight to SQL Server, along with a collection of parameter name/value pairs. SQL Server then executes those using the sp_executesql stored proc.

No replacements are ever done, there's no "stringing together the complete SQL statement" on the client side - nothing like that. If that's what the ADO.NET runtime were doing, it, too, would be very susceptible to SQL injection attacks.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
0

The short answer is that using it adds a value to the end of the SqlParameterCollection, while making your parameter value safe from SQL Injection.

The MSDN documentation does not document the method's exact internal behaviors, and I doubt that it does what you describe. However, if you wish, you can view the source code for the method using Reflector and see exactly what it does.

Robert Harvey
  • 178,213
  • 47
  • 333
  • 501
  • 1
    Instead of reflector, use SQL Profiler. It's faster and you'll see exactly what hits the sql server. – NotMe Aug 31 '10 at 20:14
  • @Chris: That will give you the end product of course (the generated SQL statement), but it won't describe the algorithm by which that product gets assembled. – Robert Harvey Aug 31 '10 at 22:00