SqlParameter Object C#
Possible Duplicate:
XKCD SQL injection — please explain
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?
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.
To be simple, to avoid SQL injection attacks and to some extent it may also improve performance as we specify the datatype, size etc for that parameter..
If you just want to avoid SQL injection, I think you may just use
command.Text = "SELECT LastName FROM MyUsers WHERE FirstName = '" + input.Replace("'","''") + "'";
上一篇: 注入C＃SQL
下一篇: SqlParameter对象C＃