In addition to stored procedures: SQL Injection in-depth defense

A few years ago, you may be blind to mention "SQL injection" to developers or require "in-depth defense" measures. Nowadays, more and more people have heard of SQL injection attacks and are beginning to pay attention to the potential risks caused by these attacks. However, most developers still lack the knowledge about how to prevent SQL injection attacks, when asked how their applications defend against SQL injection, they usually say, "It's easy to use stored procedures ". We can foresee that using stored procedures is a good start for your defense strategy, but this step alone is not enough. You need to adopt an in-depth defense strategy.

If you are not familiar with SQL injection attacks and their potential dangers to your application software, see the MSDN article "SQLInjection" (http://msdn2.microsoft.com/en-us/library/ms161953.aspx ).

The problem that relies solely on stored procedures and cannot perform an in-depth defense is that you really only want stored procedure developers to provide you with security. Similar to the following SQL server code, it is very common to identify a user's stored procedure:

Alter procedure LoginUser
(
@ UserID [nvarchar] (12 ),
@ Password [nvarchar] (12)
)
AS
SELECT * FROM Users WHERE UserID = @ UserID AND Password = @ Password
RETURN
 

This stored procedure looks safe, but consider this:

Alter procedure LoginUser
(
@ UserID [nvarchar] (12 ),
@ Password [nvarchar] (12)
)
AS
EXECUTE ('select * FROM Users WHERE UserID = ''' + @ UserID + '''
AND Password = ''' + @ Password + '''')
RETURN
 

By creating a special SQL statement and placing it in the EXECUTE function of the stored procedure code, we can actually generate a Stored Procedure SQL injection. This is easier when you use control code to write a stored procedure. It is supported in a new way in Microsoft SQL Server 2005:

[Microsoft. SqlServer. Server. SqlProcedure]
Public static void LoginUser (SqlString userId, SqlString password)
{
Using (SqlConnection conn = new SqlConnection ("context connection = true "))
{
SqlCommand selectUserCommand = new SqlCommand ();
SelectUserCommand. CommandText = "SELECT * FROM Users WHERE UserID = '"
+ UserId. Value + "AND Password = '" + password. Value + "'";
SelectUserCommand. Connection = conn;

Conn. Open ();
SqlDataReader reader = selectUserCommand. ExecuteReader ();
SqlContext. Pipe. Send (reader );
Reader. Close ();
Conn. Close ();
}
}
 

Even if you are the person who writes the stored procedure, you are not sure that others will change the code after you configure the application software. This is especially possible for Web applications, and this is why an in-depth defense policy is needed.

Obviously, the solution to this problem is to adopt an in-depth defense strategy. You should continue to use stored procedures and parameterization for any necessary queries, but you should also try to establish your in-depth defense policy to ensure that the parameters passed to these stored procedures and query verification. In our above user authentication example, "bobsmith" may be a valid user ID, but "SELECT * FROM tblCreditCards" may not. A good way to verify that user input uses your in-depth defense policy is to adopt standardized expression rules for it. You can use. web. UI. the RegularExpressionValidator control in the WebControls namespace is used to verify Web table data, and you can use it in theSystem. text. the Regex class in the RegularExpressions namespace is used to verify text data of any category. Here is an example of a Web table that is verified before passing user input to the database.

Protected void Page_Load (object sender, EventArgs e)
{
If (Page. IsPostBack)
{
// We allow only alphanumeric input
Regex allowRegex = new Regex ("^ [a-zA-Z0-9] * $ ");
If ((! AllowRegex. IsMatch (textBoxUserId. Text ))
(! AllowRegex. IsMatch (textBoxPassword. Text )))
{
LabelErrorMessage. Text = "Invalid user ID or password .";
Return;
}
Else
{
// Call the login stored procedure
...
}
}
}
 

A more thorough and in-depth defense strategy is to use a allowed input joint mode (also known as a "preferred List") and reject input.