Talking about the data access layer from SQL injection

What is SQL injection?

SQL injection is what the developer of the application didn't expect. The SQL statement is passed into the application, and the application that constructs the SQL statement directly using the user input values is likely to be attacked by SQL injection. Especially in browser-based network applications, SQL injection attacks are more common.

The simple thing is to use the values entered by the user to build the SQL statements such as:

string sql= "select * from UserBase where id= '" +txtid.text+ "' and pwd= '" +txtpwd.text+ "' /c6> "; //Use "" to wrap user-entered values to build SQL statements

Demo

At this point we just enter "' or 1=1--" In the text box "txtID" so that regardless of whether he enters the correct account and password, he can log in to your application, and can even build SQL statements in this way directly to your system-level database

parameterized commands

The way the SQL statements are built is vulnerable to SQL injection, and using parameterized commands can effectively prevent SQL injection attacks.

As follows:

  1string sql = "select * from UserBase where [email protected] and [email protected]"; //Using placeholders for placeholders   2//assigns a value of   3 sqlparameter sp1=new SqlParameter ("@Id" to the placeholder, Txtid.text);   4 SqlParameter sp2=new SqlParameter ("@pwd", Txtpwd. Text);   5//Associate the parameter value with the command object   6 cmd. Parameters.Add (SP1);   7 cmd. Parameter.add (SP2);

SQL statements for parameterized commands

The above example uses placeholders to indicate values that need to be replaced dynamically during the program's run, by adding the @ symbol before the SQL text parameter.

Ado. NET command objects use a collection to hold discrete parameter types (3, 4 lines of code), which is the Parameters collection.

Users can add as many parameter objects as possible and map to placeholders in SQL statements (6, 7 lines of code).

Common properties of the SqlParameter class

DbType	Gets or sets the DbType of the parameter
Direction	Gets or sets a value that indicates whether the parameter is input-only, output-only, or bidirectional stored procedure return-value parameters
Isnulllable	Gets or sets a value that indicates whether the parameter receives a null value
ParameterName	Gets or sets the name of the SqlParameter
Size	Gets or sets the maximum value of the data in the column
SqlDbType	Gets or sets the DbType of the parameter
Value	Gets or sets the value of this parameter

Calling a stored procedure with ADO

1. Writing stored procedures on the data side
2. Call a stored procedure
1. Specify a stored procedure name for the Command object

     1 cmd.commandtext= "Name";

   Demo
2. Specifies the command type of the commands object

     1 cmd.commandtype=commandtype.storedprocedure;

   Demo
3. Stored Procedure return value

If the stored procedure has an output parameter, the value of the output parameter can be obtained by accessing the parameter collection of the command object again after executing the stored procedure

Talking about the data access layer from SQL injection