Tuesday 16 April 2019

ado.net - SqlParameter Object C#











I'm very new in C# and I want to know.



When building an SQL string in C#, why do we need to use an SqlParameter object to represent user's input instead of directly passing in the string?


Answer



I'm assuming you mean why it's better to write:




command.Text = "SELECT LastName FROM MyUsers WHERE FirstName = @FirstName";
// Or whichever form...
command.Parameters.AddParameter("@FirstName").Value = input;


rather than



command.Text = "SELECT LastName FROM MyUsers WHERE FirstName = '" + input + "'";



The latter form has three problems:




  • It allows SQL Injection Attacks unless you're very careful about escaping - which the code above isn't. Imagine what the SQL would look like if the user put input of:



    ' OR 'x' = 'x

  • It mixes code and data. You can't see a clean representation of what you're trying to do, whereas the first form shows which bits are fixed and which are variable input


  • While not a problem for strings so much, parameters avoid unnecessary data conversions. For example, when using a date or date/time value, with the second approach you end up needing to worry about which text formats the database will accept, even though you've started with a DateTime value (say) and the database will end up with a value of some appropriate date/time type. Going via a string representation causes nothing but trouble.





Additionally, in some situations the first approach may improve performance, allowing the database to cache a query execution plan. There's quite a lot of nuance around that though, and it's quite database-specific.


No comments:

Post a Comment

php - file_get_contents shows unexpected output while reading a file

I want to output an inline jpg image as a base64 encoded string, however when I do this : $contents = file_get_contents($filename); print &q...