Ado. NET Review Summary (3)--Parameterized SQL statements

1. SQL Injection

2, using the parameterized way, can effectively prevent SQL injection, using class parameter implementation class SqlParameter

The command's property, parameters, is a collection of parameters.

3. For example < Inquiry students number of student list >

Code:

usingSystem;usingSystem.Collections.Generic;usingSystem.ComponentModel;usingSystem.Data;usingSystem.Drawing;usingSystem.Linq;usingSystem.Text;usingSystem.Windows.Forms;usingSystem.Data.SqlClient;namespacewindowsformsapplication2{ Public Partial classForm1:form { PublicForm1 () {InitializeComponent (); }        Private voidButton1_Click (Objectsender, EventArgs e) {            using(SqlConnection conn =NewSqlConnection ("server=.; Database=dbtest;uid=sa;pwd=123") )            {                stringsql ="Select Count (*) from userinfo where Username= '"+ TextBox1.Text +"'"; SqlCommand cmd=NewSqlCommand (Sql,conn); Conn.                Open (); inti =Convert.ToInt32 (cmd.                ExecuteScalar ());            MessageBox.Show (i.ToString ()); }        }    }}

View Code

The database is:

Note: The run code is the result of

Execute the code once in the database:

The results are executed correctly, without problems,

But see the following query (SQL injection principle: One way to attack a database):

In the Query box, enter:

A'  or 1=1 or 1='

The code in the database is (plus single quotation marks):

Select  from where username='a'1=11='--this sentence is always true 

4, the implementation of parameterization

usingSystem;usingSystem.Collections.Generic;usingSystem.ComponentModel;usingSystem.Data;usingSystem.Drawing;usingSystem.Linq;usingSystem.Text;usingSystem.Windows.Forms;usingSystem.Data.SqlClient;namespacewindowsformsapplication2{ Public Partial classForm1:form { PublicForm1 () {InitializeComponent (); }        Private voidButton1_Click (Objectsender, EventArgs e) {            using(SqlConnection conn =NewSqlConnection ("server=.; Database=dbtest;uid=sa;pwd=123") )            {               //String sql = "Select Count (*) from userinfo where username= '" + TextBox1.Text + "'";                stringsql ="Select COUNT (*) from userinfo where [email protected]";//parameterization ofSqlCommand cmd =NewSqlCommand (Sql,conn); //Add parameter: Cmd's Parameters property, one parameter with the Add methodcmd. Parameters.Add (NewSqlParameter ("@name", TextBox1.Text)); Conn.                Open (); inti =Convert.ToInt32 (cmd.                ExecuteScalar ());            MessageBox.Show (i.ToString ()); }        }    }}

View Code

Parameterized statement execution procedure:

(1) Open Database Tools ->profier tool (Database Analysis monitoring tool)

(2) Execution code: Enter a ' or 1=1 or 1 = '

After clicking the button

(3) Then look at the profiler.

EXEC sp_executesql n'Select COUNT (*) from userinfo where [email protected]', N'  @name nvarchar', @name =n'a'  or 1=1 or 1='--The code underneath

Ado. NET Review Summary (3)--Parameterized SQL statements