Ado. Net BASICS (Anti-SQL injection)

ArticleDirectory

• How to Prevent SQL injection attacks?

Web applications that interact with databasesProgramOne of the most serious risks is SQL injection attacks.

SQL injection is an unexpectedCodeWhen it is passed into the application, it makes the attack possible due to the poor design of the application, and only those applications that directly use the value provided by the user to build the SQL statement will be affected.

 

The problem is how the command is executed. The SQL statement is dynamically created using the string construction technology. The value of the text box is directly copied to the string, which may be like this:

 
StringSQL ="Select * from orders where customerid = '"+ Txtid. Text +"'";
 

In this example, you can tamper with SQL statements. Generally, the first target of an attack is to get an error message. If the error is not properly handled, the underlying information will be exposed to attackers. If you enter: alfki 'or '1' = '1 in the text box, then look at this SQL statement. It is like this:

 
StringSQL ="Select * from orders where customerid = 'alfki' or '1' = '1 '";
 

The consequence is that the current user's specific information is not displayed, but all information is provided to the attacker. If the screen displays sensitive information, such as social insurance numbers, birthday information, or credit card information, this will cause serious problems!

 

You can perform more complex attacks! For example, an attacker can use two Connection Numbers (--) to comment out the remaining part of the SQL statement. Although such attacks are limited to SQL Server, there is an equivalent solution for other types of databases. In addition, attackers can execute batch processing commands containing arbitrary SQL statements. for SQL Server, attackers only need to add semicolons (;). Attackers can also Delete contents of other tables in this way. It even calls the SQL Server System Stored Procedure xp_cmdshell to execute arbitrary programs on the command line.

 

The following is an SQL statement entered by an attacker in the text box to delete all rows in the MERs table: alfki '; Delete * from MERs:

 
"Select * from orders where customerid = 'alfki'; Delete * from MERs"
 

 

 

How to Prevent SQL injection attacks?

Preventive Measures:

• Use the textbox. maxlength attribute to prevent users from entering too long characters. This reduces the possibility of posting a large number of scripts.
• Use ASP. NET to verify data with control locking errors
• The error message is limited. When an exception is caught, only some common information is displayed, instead of the information in the exception. Message attribute. This will expose the attack points of the system.
• More importantly, be sure to remove special characters, suchReplace single quotes with two single quotes
• The best solution is to use parameterized commands or escape using stored procedures to prevent SQL injection attacks.

Use parameterized commands:

Parameterized commands use placeholder commands in SQL text. placeholders represent values that need to be replaced dynamically. They are transmitted through the parameters set of the command object.

For example, the following SQL statement:

Select * from MERs where customerid = 'alfki'

It can be written as follows:

Select * from MERs where customerid = @ custid

Placeholders are subsequently provided separately and automatically encoded.

 

 

Create a parameter object for each parameter. These objects are added to the command. parameters set. The following example overwrites the previous code to prevent possible SQL injection attacks:

 
StringConnstr = webconfigurationmanager. connectionstrings ["Northwind"]. Connectionstring;
 

Sqlconnection conn =NewSqlconnection (connstr );
 

 
// The parameterized SQL statement is used here.
 

 
StringSQL ="Select * from MERs where customerid = @ custid";
 

 
Sqlcommand cmd =NewSqlcommand (SQL, Conn );
 

 
// Configure the command. parameters set here
 

 
Cmd. Parameters. addwithvalue ("@ Custid", Txtid. Text );
 

Conn. open ();
 

 
Sqldatareader reader = cmd. executereader ();
 

If you attempt SQL injection again on the Modified PAGE, no records will be recorded. Because there is no order item whose customer ID value is equal to the alfki 'or '1' = '1' entered in the text box, this is what we expect.

 

Call Stored Procedure

A small part of the many commands that call the complete function stored procedure during parameterized commands.

The stored procedure is, of course, one or more SQL statements executed in batches stored in the database. They are good logical encapsulation bodies that can receive (input parameters) and return (output parameters) data.

Stored Procedures have many advantages:

• Easier maintenanceFor example, you can optimize the commands in the stored procedure without re-Compiling and using the program.
• More secure use of databasesFor example, a Windows account that runs the ASP. NET program can execute database stored procedures, but cannot access the base table.
• Improves performance: Because the stored procedure is a collection of multiple statements, you can do a lot to access the database once. If the database is on another computer (not a web server, this can greatly reduce the total time for executing complex tasks.

 

We use a complete example to learn this process and add a stored procedure to the northwind database:

 
Create ProcedureInsertemployee
 

 
@ TitleofcourtesyVarchar(25 ),
 

 
@ LastnameVarchar(20 ),
 

 
@ FirstnameVarchar(10 ),
 

 
@ EmployeeidInt Output
 

 
As
 

InsertIntoEmployees (titleofcourtesy, lastname, firstname, hiredate)
 

 
Values(@ Titleofcourtesy, @ lastname, @ firstname, getdate ());
 

 
Set@ Employeeid =@ Identity
 

This stored procedure has three input parameters and one output parameter. By the way,If the output Parameter Function of the stored procedure is not used, it is very troublesome to obtain the automatically generated Identifier from the inserted record.. Next, we create a program to call the stored procedure:

 
Protected VoidPage_load (ObjectSender, eventargs E)
 

{
 

 
StringConnstr = webconfigurationmanager. connectionstrings ["Northwind"]. Connectionstring;
 

 
Sqlconnection conn =NewSqlconnection (connstr );
 

 
// Call the stored procedure. You must specify command. commandtype
 

 
Sqlcommand cmd =NewSqlcommand ("Insertemployee", Conn );
 

 
Cmd. commandtype = system. Data. commandtype. storedprocedure;
 

 
// Pass parameters to the stored procedure
 

 
// You must specify the data type and parameter size to match the database details.
 

 
// Assign values using the Value Attribute of the Parameter
 

 
Cmd. Parameters. Add (NewSqlparameter ("@ Titleofcourtesy", Sqldbtype. nvarchar, 25 ));
 

 
Cmd. Parameters ["@ Titleofcourtesy"]. Value = title;
 

 
Cmd. Parameters. Add (NewSqlparameter ("@ Lastname", Sqldbtype. nvarchar, 20 ));
 

Cmd. Parameters ["@ Lastname"]. Value = lastname;
 

 
Cmd. Parameters. Add (NewSqlparameter ("@ Firstname", Sqldbtype. nvarchar, 10 ));
 

 
Cmd. Parameters ["@ Firstname"]. Value = firstname;
 

 
// You can add the output parameters in the same way.
 

 
// But its direction attribute must be specified as output
 

 
Cmd. Parameters. Add (NewSqlparameter ("@ Employeeid", Sqldbtype. Int, 4 ));
 

 
Cmd. Parameters ["@ Employeeid"]. Direction = parameterdirection. output;
 

 
// Execute database commands
 

 
Using(Conn)
 

 
{
 

 
Conn. open ();
 

 
IntRTV = cmd. executenonquery ();
 

 
Label1.text =String. Format ("Inserted <B >{0} </B> record (s) <br/>", RTV );
 

 
// Obtain the output parameters of the stored procedure
 

 
IntEmpid = (Int) Cmd. Parameters ["@ Employeeid"]. Value;
 

 
Label1.text + ="New ID :"+ Empid. tostring ();
 

 
}
 

 
}
 

 

Addwithvalue () for a convenient method of the parameters set (). This method receives the parameter name and its value, but does not include the data type information. Instead, it guesses the data type based on the provided data. Obviously, this is invalid for the output parameter because you do not provide a value for the output parameter.