. NET Programming Tips: Using Ado.net's set of parameters to effectively prevent SQL injection vulnerabilities

SQL injection vulnerability is an old topic, in the past to do the development of ASP, often need to use a string of worry, such as the way to solve the problem, but sometimes do not thoroughly enough, often let hackers drill a loophole.

So now in us. NET, you can use Ado.net to connect to a database either with WinForm development or with WebForm, and in Ado.net, you can set and get parameters of the command object to effectively prevent SQL injection problems. However, many of the asp.net on the internet to see a lot of anti-injection posts are used in the old way, the string of worry, here, we recommend the use of ado.net in the parameters of this method, I hope to help you.

First, let's take a look at the SQL Injection vulnerability:

SQL injection is where a user requests a GET or post way to commit the SQL statement to the server, tricking the servers into executing malicious SQL statements. Like what:

Make a user's login SQL statement:

String strSQL = "SELECT * from Users Where username= '" +name+ "' and userpwd

' +pwd + ';

If you enter something like or 1=1 at logon, the value of name and PWD will change, so let's take a look at this SQL statement:

SELECT * from Users WHERE UserName = ' or ' 1 ' = ' 1 ' and userpwd = ' or ' 1 ' = ' 1 '

This can be directly traced to the record, it will naturally login successfully, such a program is simply undefended: And, of course, there are many ways SQL injection vulnerabilities, we are not here in detail, our theme is how to. NET asp.net or winfrom use ado.ent to solve this problem.

Solution to the problem:

Or the users table that we just had, now I'm going to write a method to insert a record for this list, as follows:

public int Insert (string name, string pwd)
{
                string strSQL = Insert into Users (username,userpwd) VALUES (' +name+ "', '" "+pwd+";
                OleDbCommand cmd = new OleDbCommand (strsql,conn);
                Conn.Open ();
                int I=cmd. ExecuteNonQuery ();
             Conn.close ();
             return i;
}

There is a problem with SQL injection in the above methods, so how do you use the ado.net in a set of three?

Next, look at the code:

public int Insert (string name, string pwd)
{
                string strSQL = ' Insert into Users ' (username,userpwd) VALUES (@name, @pwd) ";
                OleDbCommand cmd = new OleDbCommand (strsql,conn);
                Cmd. Parameters.Add ("@name", OleDbType.VarChar). Value = name;
                Cmd. Parameters.Add ("@pwd", OleDbType.VarChar). Value = pwd;
                Conn.Open ();
                int I=cmd. ExecuteNonQuery ();
             Conn.close ();
             return i;
}

The above method uses a set of parameters, which effectively avoids the problem of SQL injection, and then we take a look at the Parameters collection:

In a ado.net command object, you can use a collection of parameters such as: (for example, Parameters) The collection contains a set of types SqlParameter, OleDbParameter, OdbcParameter, or OracleParameter The object. For each parameter that needs to be passed, there is a corresponding parameter object in the collection. The data type of the parameter is specific to the. NET Framework data Provider. If the type is specified, the value is converted to a. NET Framework data provider type before the value of the Parameter is passed to the data source. You can also specify the type of Parameter in a generic form by setting the DbType property of the Parameter object to a specific DbType. The. NET Framework Data provider type of the Parameter object is inferred from the DbType of the. NET Framework type or Parameter object of the Value of the Parameter object. Some friends may want to know how to use parameters in a DataAdapter object, but you can also use parameter as a parameter, as follows:

Public DataTable List (int id)
{
                string strsql= ' Select * from Users Where id= @id ';
                OleDbCommand cmd = new OleDbCommand (strsql,conn);
                Cmd. Parameters.Add ("@id", SqlDbType.Int). Value = ID;
                OleDbDataAdapter da= new OleDbDataAdapter (cmd);
                DataTable dt = new DataTable ();
                Da. Fill (DT);    
             Retrun dt;
}

There are a lot of ways to use this and parameter, but here's a simple example for everyone to apply.