Benefits of use parameters instead of concatenatio

问题:

I am new to ASP.NET and C# programming.

I would like to know what is the difference and advantages plus disadvantages of using parameters instead of concatenation in SQL statements, as I heard that it is a better way to prevent SQL injection(?)

Below are sample INSERT statements which I have changed from using concatenation to parameters:

Concatenation:

string sql = string.Format("INSERT INTO [UserData] (Username, Password, ...) VALUES ('" + usernameTB.Text + "', '" + pwTB.Text + "',...);

Parameters:

cmd.CommandText = "INSERT INTO [UserData] (Username, Password, ...) VALUES (@Username, @Password, ...)";

cmd.Parameters.AddWithValue("Username", usernameTB.Text);
cmd.Parameters.AddWithValue("Password", pwTB.Text);

Thank you in advance for any knowledge provided.

回答1:

• Safety. Concatenation opens you up to SQL-injection, especially when TB stands for Textbox. (Obligatory XKCD cartoon)
• Type safety. You solve a lot of DateTime and number formatting issues.
• Speed. The query does not change all the time, the system(s) may be able to re-use a query handle.

回答2:

Advantages

SQL Injection avoidance is the main one. It ensures a complete separation of user supplied data and executable code.

It also means that your application will work correctly when people innocently search for phrases like O'Brien without you needing to manually escape all these search terms.

Using datetime parameters for example avoids issues with ambiguous date formats in string representations.

If SQL Server it means better use of the plan cache. Rather than having loads of similar adhoc queries compiled and stored it just has one that is reused.

Disadvantages

None:

You may occasionally encounter parameter sniffing issues due to inappropriate re-use of a plan but that doesn't mean that you should not use parameterised queries in this event. In SQL Server you would typically add a RECOMPILE or OPTIMIZE FOR query hint to avoid this issue.

回答3:

One very good reason is to prevent SQL injection.

Imagine if your usernameTB.Text was equal to:

"'some  text', 'password') GO; DROP TABLE [USER DATA] GO;"

If you use parameter this string will be escaped correctly (e.g. ' replaced with ''), so it will become the value of the field.