Reinforce SQL parameters and stored procedure _ MySQL

Reinforce SQL parameters and stored procedures bitsCN.com

Developers may have some knowledge about injection attacks, but it is difficult to prevent injection attacks by grasping some important links and technologies.

This section describes how to use ADO. NET parameter object and stored procedure technology to prevent injection attacks, in order to achieve the separation of user interface input and original SQL, so that hackers cannot splice SQL statements.

SQL parameters and stored procedures

SQL parameters are a step that developers can easily ignore. generally, SQL statements are directly completed and then passed to the database for execution. This method is simple, but it also paves the way for injection attacks. To avoid injection attacks, you must filter SQL statements. However, if you directly use SQL parameter objects, you can skip the preceding steps.

The Parameters set in SQL provides type check and length verification. If developers use the Parameters set, the input is treated as a text value for processing, and SQL does not regard it as executable code. Another advantage of using the Parameters set is that you can perform type and length checks. if the value exceeds the range, an exception is triggered. This is a good example of in-depth prevention. Use Stored procedures whenever possible, and call them through the Parameters set.

The following code uses several lines to describe how to use the parameter set Object. readers note that the @ au_id parameter will be treated as a text value rather than executable code. Similarly, the parameter type and length are checked. In the following example, the input value cannot be longer than 11 characters. If the data does not comply with the type or length defined by the parameter, an exception occurs.

The demo code of the Parameters set is as follows:

　　

SqlDataAdapter myCommand = new SqlDataAdapter("AuthorLogin",conn);　　myCommand.SelectCommand.CommandType = CommandType.StoredProcedure;　　SqlParameter parm = myCommand.SelectCommand.Parameters.Add(　　"@au_id",SqlDbType.VarChar,11);　　parm.Value = Login.Text;

Please note that using stored procedures does not necessarily prevent SQL injection. It is important to use parameter objects in stored procedures. If no parameter is used, the stored procedure is vulnerable to SQL injection attacks when unfiltered input is used. For example, the following code snippet has a problem:

　　

SqlDataAdapter myCommand = new SqlDataAdapter("LoginStoredProcedure '" +　　Login.Text + "'", conn);

The correct code snippets are as follows:

　　

SqlDataAdapter myCommand = new SqlDataAdapter(　　"SELECT au_lname, au_fname FROM Authors WHERE au_id = @au_id",conn);　　SqlParameter parm = myCommand.SelectCommand.Parameters.Add("@au_id",　　SqlDbType.VarChar,11);　　parm.Value = Login.Text;

The above is the content of reinforcing SQL parameters and stored procedure _ MySQL. For more information, see PHP Chinese network (www.php1.cn )!