Ado.net 3 [SQL injection, DataAdapter, sqlParameter, DataSet],

Ado.net 3 [SQL injection, DataAdapter, sqlParameter, DataSet],

1. SQL Injection: the SQL injection attack is a security vulnerability in web applications. You can submit insecure data to the application so that the application can execute insecure SQL commands on the server. With this attack, you can easily log on to the application.

For example, if the administrator account password is xiexun, the correct SQL statement should be:

select * from Users where userName='xiexun'

If no processing is performed, enter (xuxian 'delete users --) in the login name text box and click "Log On". Two SQL statements are passed, after executing the query statement and executing the delete users statement, the entire table has no data, which makes the website quite insecure.

Select * from Users where userName = 'xiexun' delete users -- the annotation of the SQL statement, which is equivalent

 

Solution:

① [The stored procedure also uses the @ parameter passing method] And the sqlParameter method through @ parameter passing Method

Eg:

Public string Getswhere () {StringBuilder sb = new StringBuilder (); sb. append ("select ID, username, PWD, loginname, qq, classname from Users where 1 = 1"); // obtain the username string username = TxtUserName. text. trim (); if (! String. isNullOrEmpty (username) {// sb. append (string. format ("and username = '{0}'", username); // anti-SQL injection, sb by @ passing parameters. append (string. format ("and username = @ username"); // how to pass the value in, use the sqlParameter array // SqlParameter [] para = new SqlParameter [] // {// create a SqlParameter object (first name and second value) // new SqlParameter ("@ username", username )//}; // para [0] indicates adding the first part of the array object // para [0] = new SqlParameter ("@ username", username );
Para. Add (new SqlParameter ("@ username", username ));
} If (ddlsclass. selectedIndex> 0) {// sb. append (string. format ("and ClassName = '{0}'", ddlsclass. selectedValue); sb. append (string. format ("and ClassName = @ ClassName"); // para [1] = new SqlParameter ("@ ClassName", ddlsclass. selectedValue );
Para. Add (new SqlParameter ("@ ClassName", ddlsclass. SelectedValue);} return sb. ToString ();}

List <SqlParameter> para = new List <SqlParameter> ();

// Put it in list, and the add method is available.

Private void openDB ()
{
Con = new SqlConnection (conStr );
Con. Open (); // establish a connection with the database
// We can encapsulate these two sentences and call them directly.
}
// Execute the content on the page
Protected void Page_Load (object sender, EventArgs e)
{
BindUser ();
}

Public void BindUser () {try {openDB (); // obtain the SQL statement // string SQL = "select loginid, name, loginpwd, address, ClassName, mail from Users "; string SQL = Getswhere (); // execute the SQL statement using (cmd = new SqlCommand (SQL, con) // The object has, we need to execute the SQL statement {// call it through the object and add it to cmd through traversal. We will give the following value to cmd // if it contains content, we will make a loop for it if (para. count ()> 0) {foreach (var p in para) {cmd. parameters. add (p) ;}} using (dr = cmd. executeReader () {IdGri DView. DataSource = dr; IdGridView. DataBind () ;}} catch {Response. Write ("the website is being maintained .......! ");}}

Here, we use a simple figure to describe its running principle: the previous result is that the parameter is not directly interacted with the database through cmd through @, which is not safe; the other method is to add "Parameter"

We are now giving this value new SqlParameter ("@ username", username) to the sqlParameter array, sqlparameter to cmd, and cmd to execute, so that SQL injection can be avoided.

 

2. DataAdapter data adapter

1. Working principle: the DataAdapter data adapter is equivalent to the intermediate link [Man-in-the-middle]

I. Entrust the data adapter on the front-end page to interact with the database;

Ii. After database interaction, data is stored in the memory through the database adapter;

Iii. Then our webpage directly reads and writes the content in the memory. (Read can be read directly, and write can be added through the adapter). In this way, the database can also operate on data when it is in an abnormal connection.

The previous statement:

1. to interact with the database on the front-end page, you must first establish a connection (database connection );

2. release resources after use

The biggest benefit is that there is no need to connect every page to the database, reducing the pressure on the database.

② Use a data adapter for a query [adding a stored procedure in a program]

<Div> <asp: GridView ID = "IdGridView" runat = "server" AutoGenerateColumns = "False"> <Columns> <asp: boundField DataField = "id" HeaderText = "ID"/> <asp: BoundField DataField = "username" HeaderText = "username"/> <asp: boundField DataField = "PWD" HeaderText = "password"/> <asp: BoundField DataField = "loginname" HeaderText = "name"/> <asp: boundField DataField = "qq" HeaderText = "QQ"/> <asp: BoundField DataField = "classname" HeaderTe Xt = "class"/> <asp: TemplateField HeaderText = "details"> <ItemTemplate> <a href = "UserInfo. aspx? Id = <% # Eval ("ID") %> "target =" _ blank "> details </a> <% -- <a href = 'userinfo. aspx? Userid = <% # Eval ("UserId") %> 'target = "_ blank"> details </a <% -- <a href = "one. aspx? "> Details </a> -- %> </ItemTemplate> </asp: TemplateField> </Columns> </asp: GridView> </div>

Using System; using System. collections. generic; using System. configuration; using System. data. sqlClient; using System. linq; using System. web; using System. web. UI; using System. web. UI. webControls; using System. data; namespace _ 20160520 {public partial class egDataAp: System. web. UI. page {private string conStr = ConfigurationManager. connectionStrings ["mySchool"]. toString (); SqlConnection con = null; // equivalent to SqlCommand cmd = null; // execute the SQL statement SqlDataReader dr = null; // store query results // first create a DataSet ds = new DataSet (); protected void Page_Load (object sender, EventArgs e) {// use the data adapter to make a query con = new SqlConnection (conStr); string ssql = "select ID, username, PWD, loginname, qq, classname from Users "; using (cmd = new SqlCommand (ssql, con) {// create a DataAdapter and pass a cmd SqlDataAdapter da = new SqlDataAdapter (cmd); // fill in the application data adapter, enter da in ds. fill (ds); // specify the data source ,. tables [0] add the first table // IdGridView. dataSource = ds; IdGridView. dataSource = ds. tables [0]; IdGridView. dataBind ();}}}}

 

③. DataAdapter calls a stored procedure [Add a stored procedure call to the database]

Private string conStr = ConfigurationManager. connectionStrings ["mySchool"]. toString (); SqlConnection con = null; // equivalent to SqlCommand cmd = null; // execute the SQL statement SqlDataReader dr = null; // store query results // first create a DataSet ds = new DataSet (); protected void Page_Load (object sender, EventArgs e) {// use the data adapter to make a query con = new SqlConnection (conStr); // string ssql = "select ID, username, PWD, loginname, qq, classname from Users "; // The preceding method is used. Here we directly transmit a stored procedure name using (cmd = new SqlCommand (" procegDataAp ", con )) {// specify the CommandType of a sqlcommand (CommandType by default. text) is the name of the CommandType Stored Procedure cmd. commandType = CommandType. storedProcedure; List <SqlParameter> para = new List <SqlParameter> () {// Add it to cmd through the sqlParameter array. You must specify the name, type, value // fuzzy query new SqlParameter ("@ UserName", "%" + TxtsUserName. text. trim () + "%")}; foreach (var a in para) {cmd. parameters. add (a) ;}// create a DataAdapter and pass a cmd SqlDataAdapter da = new SqlDataAdapter (cmd); // fill the application data adapter with the da in ds. fill (ds); // specify the data source ,. tables [0] add the first table // IdGridView. dataSource = ds; IdGridView. dataSource = ds. tables [0]; IdGridView. dataBind ();}}

 

3. Differences between DataSet, DataTable, DataReader, and DataAdapter:

I. dtaSet is a method used for SQL connection. It means that a database copy is stored in an application, which is equivalent to a database in memory. When the application starts to run, save database-related data to DataSet.

Ii. DataTable indicates a table with data in memory. It is often used with DefaultView to obtain a custom view of a table that may include a filtering view or a cursor position.

III. The DataReader object is the simplest way to read the database. It can only read data but cannot write data. It also reads data from the beginning to the end and cannot read only a piece of data, but it occupies a small amount of memory and is fast.

IV. The DataAdapter object is used to read and write data from the database. A data entry is strongly operated, But it occupies more memory than DataReader and is slow. It is generally used with DataSet.

Note. DataSet indicates a DataSet, which is the cache of data in memory. The DataReader can contain multiple table datatables. DataSet connects data to non-connection-oriented databases, reads all tables to the cache pool in SQL statements, and disconnects the database. DataReader connects to the database for connection-oriented purposes. When reading a table, you can only read the table forward. After reading the data, the user decides whether to disconnect the table.