What SqlCommand.Parameters.AddWithValue really does?

Question

Hi,

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?

Answer 1

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.

Answer 2

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.