Benefits of using parameters instead of concatenation

Question

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

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

The following are examples of INSERT statements that I changed using parameters to concatenation:

Concatenation:

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

Options:

 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 the knowledge provided.

Answer 1

Concatenation is much more vulnerable than using parameters.

Have a look here: http://www.codinghorror.com/blog/2005/04/give-me-parameterized-sql-or-give-me-death.html

Answer 2

• Security. Concatenation gives you SQL injection, especially when TB denotes a text field. (Mandatory cartoon XKCD )
• Enter security. You solve a lot of problems with date and time formatting.
• Speed. The request does not change all the time; the system can reuse the request descriptor.

Answer 3

<strong> Benefits

SQL Injection Elimination is fundamental. It provides a complete separation of user-provided data and executable code.

It also means that your application will work correctly when people innocently search for phrases such as O'Brien , without requiring you to manually avoid all these search terms.

Using datetime parameters, for example, avoids the problem of ambiguous date formats in string representations.

If SQL Server means better use of the plan cache. Instead of loading similar complex queries that were compiled and saved, it only has one that is reused.

disadvantages

No:

Sometimes you may encounter problems sniffing parameters due to improper reuse of the plan, but this does not mean that you should not use parameterized queries in this case. In SQL Server, you usually add the RECOMPILE or OPTIMIZE FOR hint to avoid this problem.

Answer 4

One very important reason is to prevent SQL injection.

Imagine your usernameTB.Text was equal to:

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

If you use the parameter, this string will be escaped correctly (for example, "replaced by"), so it will become the value of the field.