Asp.net (C #) example of using stored procedure parameters in Access Database (Anti-SQL injection)

Enter the account and password in the text box, and press OK to check whether the account and password are correct from the ACCESS database. If yes, "Login successful" is displayed ", if the error occurs, "Logon Failed" is displayed ". we need to use the stored procedure to implement this.

First, we need to create a table, open the ACCESS database, and enter in the SQL view under the query:

Create Table admin_table (
Id autoincrement primary key,
Acc_name string (10 ),
Acc_password string (10)
)

In this way, we have created a table named admin_table.

Run the following SQL statement to add a record with the account name Admin and password admin888.

Insert into admin_table (acc_name, acc_password)
Values ('admin', 'admin888 ')

Note: You can use the designer to create tables and add records!

Next we will make two text boxes in default. aspx, one login button, as shown below:

Use our previous practices in default. aspx. CS,CodeAs follows:

Using system;
Using system. Data;
Using system. configuration;
Using system. Web;
Using system. Web. Security;
Using system. Web. UI;
Using system. Web. UI. webcontrols;
Using system. Web. UI. webcontrols. webparts;
Using system. Web. UI. htmlcontrols;
Using system. Data. oledb;

Public partial class _ default: system. Web. UI. Page
{
Protected void page_load (Object sender, eventargs E)
{
If (! Page. ispostback)
{
This. tbxname. Text = "";
This. tbxpassword. Text = "";
}}

Protected void button#click (Object sender, eventargs E)
{

String mystring = "provider = Microsoft. Jet. oledb.4.0; Data Source =" + server. mappath ("app_data/mydata. mdb ");
String plain text = "select * From admin_table where acc_name = '" + this. tbxname. Text + "' and acc_password = '" + this. tbxpassword. Text + "'";
Oledbconnection conn = new oledbconnection (mystring );
Conn. open ();
Oledbcommand cmd = new oledbcommand (plain text, Conn );
Int result = convert. toint32 (CMD. executescalar ());
If (result> 0)
{
Response. Write ("<SCRIPT> alert ('login successfully'); </SCRIPT> ");
}
Else
{
Response. Write ("<SCRIPT> alert ('logon failed'); </SCRIPT> ");
}

}
}

After we enter the correct admin and admin888, it will pop up:

However, many beginners may make some serious mistakes due to lack of experience, which greatly reduces the system security. If the account is admin, And the password is

Admin 'or '1 = 1

The result is:

Why? The reason is simple.

String plain text = "select * From admin_table where acc_name = '" + this. tbxname. Text + "' and acc_password = '" + this. tbxpassword. Text + "'";

So what you entered

Admin 'or '1 = 1

Equivalent:

String plain text = "select * From admin_table where acc_name = 'admin' and acc_password = 'admin' or '1 = 1 '";

See it! This is an SQL injection attack.ProgramWhen developing and constructing a where clause with user input. of course, we can solve this problem by filtering illegal characters, but obviously this is not the most effective way. We will use oledbcommand. the parameter value passing Implementation of the parameters attribute filters out invalid characters.

The modified code is as follows:

Using system;
Using system. Data;
Using system. configuration;
Using system. Web;
Using system. Web. Security;
Using system. Web. UI;
Using system. Web. UI. webcontrols;
Using system. Web. UI. webcontrols. webparts;
Using system. Web. UI. htmlcontrols;
Using system. Data. oledb;

Public partial class _ default: system. Web. UI. Page
{
Protected void page_load (Object sender, eventargs E)
{
If (! Page. ispostback)
{
This. tbxname. Text = "";
This. tbxpassword. Text = "";
}
}

Protected void button#click (Object sender, eventargs E)
{
// Define the connection string
String mystring = "provider = Microsoft. Jet. oledb.4.0; Data Source =" + server. mappath ("app_data/mydata. mdb ");
Oledbconnection conn = new oledbconnection (mystring );
Conn. open ();
Oledbcommand cmd = new oledbcommand ("select * From admin_table where acc_name =? And acc_password =? ", Conn );
Oledbparameter paraname = new oledbparameter ("? ", Oledbtype. varchar, 10 );
Paraname. value = This. tbxname. text;
Cmd. Parameters. Add (paraname );
Oledbparameter parapassword = new oledbparameter ("? ", Oledbtype. varchar, 10 );
Parapassword. value = This. tbxpassword. text;
Cmd. Parameters. Add (parapassword); int result = convert. toint32 (CMD. executescalar ());
If (result> 0)
{
Response. Write ("<SCRIPT> alert ('login successfully'); </SCRIPT> ");
}
Else
{
Response. Write ("<SCRIPT> alert ('logon failed'); </SCRIPT> ");
}

}
}

The following code is the most critical:

Oledbparameter paraname = new oledbparameter ("? ", Oledbtype. varchar, 10 );
Paraname. value = This. tbxname. text;
Cmd. Parameters. Add (paraname );
Oledbparameter parapassword = new oledbparameter ("? ", Oledbtype. varchar, 10 );
Parapassword. value = This. tbxpassword. text;
Cmd. Parameters. Add (parapassword );

Note the following "? "If the SQL database is different, we will not discuss it here.

Enter the following again this time:

Result:

 

Summary: using parameterization to use stored procedures is a must-have knowledge that can greatly improve website security. I hope more friends can discuss with me.