The simplest way for SQL Server to prevent SQL Injection Attack

SQL injection attacks are hard to prevent. According to Microsoft's solution, stored procedures are used. However, if each SQL operation is implemented using a stored procedure, this is too troublesome.

What is a simple solution? Of course.
That's -- using stored procedures ...... Pai_^
Fart! Isn't that true?

Do not worry. by tracking the executenonquery, executescalar, and beginexecutereader of sqlcommand, if the SQL statement contains sqlparameter, the system

Sp_executesql is automatically called for processing. If there is no sqlparameter, the system will directly execute this SQL statement.
For example
String SQL = "select userid, username, email from users where userid = 'c054965 '";
Sqlcommand cmd = new sqlcommand (SQL, connection );
Try
{
Cmd. Connection. open ();
Sqldatareader reader = cmd. executereader ();
//
//.....
//
}
Finally
{
Cmd. Connection. Close ();
}

At this time, through tracking, it is found that SQL Server directly executes the SELECT statement
However, the following statements are different.
String SQL = "select userid, username, email from users where userid = @ userid ";
Sqlcommand cmd = new sqlcommand (SQL, connection );
Cmd. Parameters. Add (New sqlparameter ("@ userid", "c054965 "));

Try
{
Cmd. Connection. open ();
Sqldatareader reader = cmd. executereader ();
//
//.....
//
}
Finally
{
Cmd. Connection. Close ();
}

The SQL Server system executes
Exec sp_executesql n'select userid, username, email from users where userid = @ userid', n' @ userid nvarchar (7) ', @ userid =

N 'c054965'

Now let's perform SQL injection attack.
String SQL = "select userid, username, email from users where userid = @ userid ";
Sqlcommand cmd = new sqlcommand (SQL, connection );
Cmd. Parameters. Add (New sqlparameter ("@ userid", "c054965; Create Table AA (a int );--"));
//
//....
//

At this time, the system will not return the correct data
Because SQL Server executes
Exec sp_executesql n' select email, userid, username from users where userid = @ userid and Pwd = @ PWD ', n' @ userid nvarchar (33 )',

@ Userid = n' c054965; Create Table AA (a int );--'

Besides, because the stored procedure is executed, the subsequent create table statement is not executed.

This effectively avoids SQL injection attacks.

Summary:

By adding sqlparameter to the commandtext of sqlcommand, You can effectively prevent SQL injection attacks without writing Special stored procedures. To a large extent

Improve our development efficiency and business logic layer flexibility.
In fact, we are still using stored procedures, but sqlcommand helps us implement them automatically.

Note:
The dongliorm platform uses this technique extensively when reading and writing data. All the SQL statements automatically generated are based on sqlparameter.
For example
Select userid, username from users where userid = @ userid
Insert into users (userid, username) values (@ userid, @ username)
Delete from users where userid = @ userid
Update users
Set username = @ username
Where userid = @ userid_old

This effectively avoids SQL injection attacks.
Introduction to dongliorm:
Http://www.cnblogs.com/Yahong111/archive/2007/06/06/774236.html