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

Source: Internet
Author: User
Tags sql injection

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.