"2017-04-20" SQL string injection attack and defense

String attack

The so-called SQL string injection attack is the input of the user input interface through a specially crafted string containing some instruction, to change the SQL statement in C # to execute the database, thus attacking the database.

Enter a ' in the user input interface; update Student Set Sname = ' Iraq ';--

Then the database sname a column is changed to Yiyi

Principle: The user-entered code replaces the sname in the SQL statement in C # and becomes the SQL statement that the hacker below wants to execute.

INSERT into Student values (' "+sno+" ', ' a '); update Student Set Sname = ' Iraq ';--')

Attack defense

The string concatenation in the SQL statement executed by the operations database in C # is replaced with a placeholder by using this placeholder for anti-string attacks, so that the placeholder represents just a string, without the code meaning

Defending against a placeholder does not alter the rest of the table, it saves the code intact in the database without altering the SQL statements in C #.

It is best to clear the placeholder first to prevent the previous placeholder from being affected

Development project three-tier architecture: interface layer, business logic layer, data access layer

Where the data access layer is divided into:

1. Entity class

The table in the database is mapped to a class with the class name consistent with the table name. Each column in the table is a member variable and a property under that class, which is the simplest package

Change the table name in the database to the class name of the class.

Turn each column in the database into a member variable and a property in the entity class

The column name is consistent with the property name. Member variable name: Precede the column name with an underscore

usingSystem;usingSystem.Collections.Generic;usingSystem.Linq;usingSystem.Text;namespaceconsoleapplication1.app_code{ Public classUsers {Private int_ids;  Public intIds {Get{return_ids;} Set{_ids =value;} }        Private string_username;  Public stringUserName {Get{return_username;} Set{_username =value;} }        Private string_password;  Public stringPassWord {Get{return_password;} Set{_password =value;} }        Private string_nickname;  Public stringNickname {Get{return_nickname;} Set{_nickname =value;} }        Private BOOL_sex;  Public BOOLSex {Get{return_sex;} Set{_sex =value;} }        PrivateDateTime _birthday;  PublicDateTime Birthday {Get{return_birthday;} Set{_birthday =value;} }        Private string_nation;  Public stringNation {Get{return_nation;} Set{_nation =value;} }    }}

2. Data Access Class

Writes a table's database operations into a single method that is put into this class for external invocation

usingSystem;usingSystem.Collections.Generic;usingSystem.Linq;usingSystem.Text;usingSystem.Data.SqlClient;namespaceconsoleapplication2.app_code{ Public classUsersdata {SqlConnection conn=NULL; SqlCommand cmd=NULL;  PublicUsersdata () {conn=NewSqlConnection ("server=.; Database=data0216;user=sa;pwd=123"); CMD=Conn.        CreateCommand (); }         PublicList<users>SelectAll () {List<Users> list =NewList<users>(); Cmd.commandtext="Select *from Users"; Conn.            Open (); SqlDataReader Dr=cmd.            ExecuteReader ();  while(Dr. Read ()) {Users U=NewUsers (); U.ids= Convert.ToInt32 (dr["IDs"]); U.username= dr["UserName"].                ToString (); U.password= dr["PassWord"].                ToString (); U.nickname= dr["Nickname"].                ToString (); U.sex= Convert.toboolean (dr["Sex"]); U.birthday= Convert.todatetime (dr["Birthday"]); U.nation= dr["Nation"].                ToString (); List.            ADD (U); } conn.            Close (); returnlist; }         Public voidInsert (Users u) {cmd.commandtext="INSERT into Users values (@username, @password, @nickname, @sex, @birthday, @nation)"; Cmd.            Parameters.clear (); Cmd. Parameters.addwithvalue ("@username", U.username); Cmd. Parameters.addwithvalue ("@password", U.password); Cmd. Parameters.addwithvalue ("@nickname", U.nickname); Cmd. Parameters.addwithvalue ("@sex", U.sex); Cmd. Parameters.addwithvalue ("@birthday", U.birthday); Cmd. Parameters.addwithvalue ("@nation", u.nation); Conn.            Open (); intA=cmd.            ExecuteNonQuery (); if(A >0) Console.WriteLine ("Add Data Success"); ElseConsole.WriteLine ("failed to add data"); Conn.        Close (); }    }}

"2017-04-20" SQL string injection attack and defense