86073

What SqlCommand.Parameters.AddWithValue really does?

Question:

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

I expect that:

<ol><li>

It replaces every ' character by '',

</li> <li>

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'.

</li> <li>

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.

</li> </ol>

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

Answer1:

The ADO.NET SqlClient driver will <strong>not</strong> 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.

Answer2:

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 <a href="http://www.red-gate.com/products/reflector/" rel="nofollow">Reflector</a> and see exactly what it does.

Recommend

  • array length in JS
  • Unable to use crosstab in Postgres
  • Why can't I deliver this message to all of the frames running in a tab?
  • Set Google Calendar query parameters with google-java-api-client in Android
  • Neo4j Configuration
  • Local SQL Server installed by other user account, how to access from new user account
  • How to choose from two connection strings?
  • Microsoft.ACE.OLEDB.12.0 Current Recordset does not support updating error received when trying to u
  • Preg replace with words from an array
  • System.Data.SqlClient Namespace for MySQL?
  • Eclipse doesn't generate google cloud endpoint client library
  • Change Dapper so that it maps a database null value to double.NaN
  • File uploading and saving to database incorrectly
  • MySQL very slow query with custom function in spite of LIMIT
  • Differences between 0, -0 and +0 [duplicate]
  • Partial String Replacement using PowerShell
  • How to parse Java properties which contains variables?
  • Using docker environment -e variable in supervisor
  • Nginx rewrite equivalent to Apache RewriteRule that converts URL params into QueryString key/value p
  • Spark (Scala) Writing (and reading) to local file system from driver
  • Less Conflicting Session Manager for Zope 2
  • Add reference to ASP.NET 5 Class Library from Framework 4.5 Class Library Project
  • pip in virtualenv gets ConnectTimeoutError
  • OSX - always hide certain files
  • pyodbc doesn't report sql server error
  • Does Mobilefirst provide a provision to access web services directly?
  • Tamper-proof configuration files in .NET?
  • why xml file does not aligned properly after append the string in beginning and end of the file usin
  • htaccess add www if not subdomain, if subdomain remove www
  • JSON response opens as a file, but I can't access it with JavaScript
  • Installing Hadoop, Java Exception about illegal characters at index 7?
  • Different response to non-authenticated users and AJAX calls
  • Accessing IRQ description array within a module and displaying action names
  • Get object from AWS S3 as a stream
  • Cross-Platform Protobuf Serialization
  • WinForms: two way TextBox problem
  • Validaiting emails with Net.Mail MailAddress
  • 'TypeError' while using NSGA2 to solve Multi-objective prob. from pyopt-sparse in OpenMDAO
  • Do I've to free mysql result after storing it?
  • how does django model after text[] in postgresql [duplicate]