Shulou(Shulou.com)05/31 Report--

This article mainly introduces the relevant knowledge of "ADO.NET anti-SQL injection and the method of using parameters to add, delete, modify and check". The editor shows you the operation process through an actual case, and the operation method is simple, fast and practical. I hope that this article "ADO.NET anti-SQL injection and the use of parameters to add, delete, modify and check" article can help you solve the problem.

I. sql injection risk and solution

SQL injection refers to the behavior of deceiving the database server by injecting additional SQL statements into pre-defined SQL statements.

Example: make the member login function.

The login button code is as follows:

Private void btLogin_Click (object sender, EventArgs e) {/ / 1-defines the connection string string connStr = "server=.;database=DBTEST;uid=sa;pwd=123456"; / / 2-defines the connection object, opens the connection SqlConnection conn = new SqlConnection (connStr); conn.Open () / / 3-write sql statements (here string sql = string.Format ("select * from Member where MemberAccount=' {0} 'and MemberPwd=' {1}'", this.txtAccount.Text,this.txtPwd.Text); / / 4-data adapter extract information SqlDataAdapter adp = new SqlDataAdapter (sql, conn); DataTable dt = new DataTable () / / data table adp.Fill (dt); conn.Close (); if (dt.Rows.Count = = 0) MessageBox.Show ("wrong username or password!") ; else MessageBox.Show ("login succeeded!") ;}

Note: if you enter'or'1 roomroom1 in the user name and password input box at the same time, you can cause injection and log in successfully, because the meaning of the original sql statement has been changed, and there are constant conditions such as' 1roomroomroom1'in the query conditions.

There are the following solutions to the problem risks of the above login features:

Option 1:

To judge the dangerous characters, add the following code before the login code.

If (this.txtAccount.Text.IndexOf ("'") > = 0 | | this.txtPwd.Text.IndexOf ("'") > = 0) {MessageBox.Show ("illegal login!"); return;} scenario 2:

Optimize the SQL statement, first according to the user name query, the query has records in the password text box content comparison.

Private void btLogin_Click (object sender, EventArgs e) {/ / 1-defines the connection string string connStr = "server=.;database=DBTEST;uid=sa;pwd=123456"; / / 2-defines the connection object, opens the connection SqlConnection conn = new SqlConnection (connStr); conn.Open (); / / 3-writes the sql statement string sql = string.Format ("select * from Member where MemberAccount=' {0}'", this.txtAccount.Text) / / 4-data adapter extraction information SqlDataAdapter adp = new SqlDataAdapter (sql, conn); DataTable dt = new DataTable (); / / data table adp.Fill (dt); conn.Close (); if (dt.Rows.Count = = 0) MessageBox.Show ("user name error!") ; else {if (dt.Rows [0] ["MemberPwd"] .ToString () .Equals (this.txtPwd.Text)) MessageBox.Show ("login successful!") ; else MessageBox.Show ("wrong password!") ;}} Plan 3:

Write sql statements in a parameterized way

Private void btLogin_Click (object sender, EventArgs e) {/ / 1-define the connection string / / string connStr = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DBTEST;Data Source=."; / / 2-write the connection string (sql username password connection) string connStr = "server=.;database=DBTEST;uid=sa;pwd=123456"; / / 2-define the connection object, open the connection SqlConnection conn = new SqlConnection (connStr); conn.Open () / / 3-write sql statements string sql = "select * from Member where MemberAccount=@MemberAccount and MemberPwd=@MemberPwd"; / / 4-data adapter extract information SqlDataAdapter adp = new SqlDataAdapter (sql, conn); adp.SelectCommand.Parameters.Add (new SqlParameter ("@ MemberAccount", this.txtAccount.Text)); adp.SelectCommand.Parameters.Add ("@ MemberPwd", this.txtPwd.Text); DataTable dt = new DataTable () / / data table adp.Fill (dt); conn.Close (); if (dt.Rows.Count = = 0) MessageBox.Show ("wrong username or password!") ; else MessageBox.Show ("login succeeded!") Second, parameterized implementation of additions, deletions, modifications and queries

This example modifies on the basis of the previous project, mainly changing the added data and modified data into a parameterized way.

Business requirements:

(1) display data when the form is loaded.

(2) Click the "add data" button, pop up the new form, add the data in the new form, and automatically refresh the table data after adding.

(3) A row is selected with the mouse, and the delete menu pops up with the right button to delete the data.

(4) select a row with the mouse, click the "Edit data" button, pop up a new form, modify the data in the new form, and automatically refresh the table data after modification.

The implementation steps are as follows:

(1) query form display data code:

/ / data binding method public void BindData () {/ / 1-defines the connection string / / string connStr = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DBTEST;Data Source=."; / / 2-writes the connection string (sql username password connection) string connStr = "server=.;database=DBTEST;uid=sa;pwd=123456"; / / 2-defines the connection object and opens the connection SqlConnection conn = new SqlConnection (connStr); conn.Open () / / 3-write sql statements string sql = "select * from Member"; / / 4-data adapter extract information SqlDataAdapter adp = new SqlDataAdapter (sql, conn); DataTable dt = new DataTable (); / / data table adp.Fill (dt); this.dataGridView1.AutoGenerateColumns = false; / / automatic column cancellation this.dataGridView1.DataSource = dt; conn.Close () } private void FrmSelect_Load (object sender, EventArgs e) {BindData ();}

(2) delete the menu code:

Private void deletes ToolStripMenuItem_Click (object sender, EventArgs e) {DialogResult r = MessageBox.Show ("are you sure you want to delete?", "* system", MessageBoxButtons.YesNo); if (r = = System.Windows.Forms.DialogResult.No) {return;} int memId = int.Parse (this.dataGridView1.SelectedRows [0] .cells [0] .Value.ToString ()); string connStr = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DBTEST;Data Source=." SqlConnection conn = new SqlConnection (connStr); conn.Open (); string sql = "delete from Member where MemberId =" + memId; SqlCommand cmd = new SqlCommand (sql, conn); int rowCount = cmd.ExecuteNonQuery (); conn.Close (); if (rowCount = = 1) MessageBox.Show ("deletion succeeded!"); else MessageBox.Show ("deletion failed!"); BindData ();}

(3) add form code to members:

Private void btAdd_Click (object sender, EventArgs e) {/ / 1-write connection string (windows connection) / / string connStr = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DBTEST;Data Source=."; / / 2-write connection string (sql username password connection) string connStr = "server=.;database=DBTEST;uid=sa;pwd=123456"; / / 2-create connection object and open database connection SqlConnection conn = new SqlConnection (connStr) Conn.Open (); / / 3-write sql statements string sql = string.Format ("insert into Member (MemberAccount,MemberPwd,MemberName,MemberPhone) values (@ MemberAccount,@MemberPwd,@MemberName,@MemberPhone)", this.txtAccount.Text, this.txtPwd.Text, this.txtNickName.Text, this.txtPhone.Text); / / 4-define the object execution command SqlCommand cmd = new SqlCommand (sql, conn) Cmd.Parameters.Add (new SqlParameter ("@ MemberAccount", this.txtAccount.Text)); cmd.Parameters.Add (new SqlParameter ("@ MemberPwd", this.txtPwd.Text)); cmd.Parameters.Add (new SqlParameter ("@ MemberName", this.txtNickName.Text)); cmd.Parameters.Add (new SqlParameter ("@ MemberPhone", this.txtPhone.Text)); int rowCount = cmd.ExecuteNonQuery (); conn.Close () If (rowCount = = 1) MessageBox.Show ("add successful!"); else MessageBox.Show ("add failed!"); / / Refresh query form data and close the current form ((FrmSelect) this.Owner) .BindData (); this.Close ();}

(4) member edit form code:

Public int MemId {get; set;} / / accept the member number passed externally / / bind the member details to the text box private void BindDetail () {/ / 1-define the connection string string connStr = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DBTEST;Data Source=."; / / 2-define the connection object and open the connection SqlConnection conn = new SqlConnection (connStr); conn.Open () / / 3-write sql statement string sql = "select * from Member where MemberId =" + this.MemId; / /-extract data SqlDataAdapter adp = new SqlDataAdapter (sql, conn); DataTable dt = new DataTable (); adp.Fill (dt); conn.Close (); this.txtAccount.Text = dt.Rows [0] ["MemberAccount"]. ToString (); this.txtPwd.Text = dt.Rows [0] ["MemberPwd"]. ToString () This.txtNickName.Text = dt.Rows [0] ["MemberName"] .ToString (); this.txtPhone.Text = dt.Rows [0] ["MemberPhone"] .ToString ();} private void FrmEdit_Load (object sender, EventArgs e) {BindDetail ();} private void btUpdate_Click (object sender, EventArgs e) {string connStr = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DBTEST;Data Source=."; SqlConnection conn = new SqlConnection (connStr); conn.Open () String sql = "update Member set MemberAccount=@MemberAccount,MemberPwd=@MemberPwd,MemberName=@MemberName,MemberPhone=@MemberPhone where MemberId=@MemberId"; SqlCommand cmd = new SqlCommand (sql, conn); cmd.Parameters.Add (new SqlParameter ("@ MemberAccount", this.txtAccount.Text)); cmd.Parameters.Add (new SqlParameter ("@ MemberPwd", this.txtPwd.Text)); cmd.Parameters.Add ("@ MemberName", this.txtNickName.Text)) Cmd.Parameters.Add (new SqlParameter ("@ MemberPhone", this.txtPhone.Text)); cmd.Parameters.Add (new SqlParameter ("@ MemberId", this.MemId)); int rowCount = cmd.ExecuteNonQuery (); conn.Close (); if (rowCount = = 1) MessageBox.Show ("modify successful!"); else MessageBox.Show ("modify failed!") / / Refresh query form data and close the current form ((FrmSelect) this.Owner) .BindData (); this.Close ();}

(5) query the code of the "add data" and "Edit data" buttons of the form:

Private void btAdd_Click (object sender, EventArgs e) {FrmAdd frm = new FrmAdd (); frm.Owner = this; frm.Show ();} private void btEdit_Click (object sender, EventArgs e) {if (this.dataGridView1.SelectedRows [0] .cells [0] .Value.ToString (). Equals (")) {MessageBox.Show (" Please choose correctly! "); return } int memId = int.Parse (this.dataGridView1.SelectedRows [0] .cells [0] .Value.ToString ()); FrmEdit frm = new FrmEdit (); frm.MemId = memId; frm.Owner = this; frm.Show ();} III. Package DBHelper class class DBHelper {/ / SQL connection string-SQL authentication login public static string connStr = "server=.;database=DBTEST;uid=sa;pwd=123456;" / / SQL connection string-login / / public static string connStr = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DBTEST;Data Source=." by Windows authentication; / / read configuration file appSettings node read string (need to add reference System.Configuration) / / public static string connStr = ConfigurationManager.AppSettings ["DefaultConn"] .ToString () / / the corresponding configuration file is as follows: / read the configuration file ConnectionStrings node read string (need to add reference System.Configuration) / / public static string connStr = ConfigurationManager.ConnectionStrings ["DefaultConn"] .ConnectionString; / / the corresponding configuration file is as follows: / public static SqlConnection conn = null; public static SqlDataAdapter adp = null # region connects to the database / public static void OpenConn () {if (conn = = null) {conn = new SqlConnection (connStr); conn.Open ();} if (conn.State = = System.Data.ConnectionState.Closed) {conn.Open () } if (conn.State = = System.Data.ConnectionState.Broken) {conn.Close (); conn.Open () } # endregion # region prepare / prepare to execute a SQL statement / SQL statement to be executed public static void PrepareSql (string sql) {OpenConn (); / / Open database connection adp = new SqlDataAdapter (sql, conn) before executing the SQL statement } # endregion # region set and get the parameter of the sql statement / parameter name / parameter value public static void SetParameter (string parameterName, object parameterValue) {parameterName = "@" + parameterName.Trim (); if (parameterValue = = null) parameterValue = DBNull.Value Adp.SelectCommand.Parameters.Add (new SqlParameter (parameterName, parameterValue));} # endregion # region execute SQL statement / execute non-query SQL statement / number of affected rows public static int ExecNonQuery () {int result = adp.SelectCommand.ExecuteNonQuery (); conn.Close (); return result } / execute query SQL statement / DataTable type query result public static DataTable ExecQuery () {DataTable dt = new DataTable (); adp.Fill (dt); conn.Close (); return dt } / execute query SQL statement / query result of SqlDataReader type. SqlDataReader needs to manually close public static SqlDataReader ExecDataReader () {return adp.SelectCommand.ExecuteReader (CommandBehavior.CloseConnection). } / execute the query SQL statement / the first column of the first row of the query result public static object ExecScalar () {object obj = adp.SelectCommand.ExecuteScalar (); conn.Close (); return obj } # endregion} on the "ADO.NET anti-SQL injection and the use of parameters to add, delete and modify the method" is introduced here, thank you for reading. If you want to know more about the industry, you can follow the industry information channel. The editor will update different knowledge points for you every day.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.