SqlParameter Object C#

Question

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?

Please take a look at that question. It explains it all, in terms more generic than C# SqlPatameter, but still good. — MPelletier, Oct 14 '12 at 06:30
First, C# is a language and has nothing to do with databases. The actual technology is called ado.net, and what you're asking about is parameterized queries. — Erik Funkenbusch, Oct 14 '12 at 06:32

score 2 · Accepted Answer · answered Oct 14 '12 at 06:31

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.

I find a lot of people think "Oh, my little web site won't be a target for SQL Injection, so why bother?". The problem with that line of thinking is that the internet is a very unsafe place, and there are roving bots that try every possible IP address out there, and try various SQL Injection attacks. Your site may be compromised by a robot, and then used to compromise other sites or users who visit your site. Even worse, it may be turned into a spam zombie, and used to spam others. Don't make the mistake of thinking you're too small to be attacked, because it's not true. — Erik Funkenbusch, Oct 14 '12 at 06:37
You can also learn a very expensive lesson, if your site is hosted on a commercial hosting system where you are charged for bandwidth. You could find that you suddenly owe a lot of money because you didn't think you were important enough to attack. Important to remember: When dealing with the internet, always assume the worst. — Erik Funkenbusch, Oct 14 '12 at 06:39

score 0 · Answer 2 · answered Oct 14 '12 at 06:42

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("'","''") + "'";

SqlParameter Object C#

2 Answers2