. NET anti-SQL Injection Method

SQL statement

Using SqlCommand to pass parameters:

String strSQL = "SELECT * FROM [user] WHERE user_id = @ id ";
SqlCommand cmd = new SqlCommand ();
Cmd. CommandText = strSQL;
Cmd. Parameters. Add ("@ id", SqlDbType. VarChar, 20). Value = Request ["id"]. ToString ();

Filter the run prohibition method:

/// <Summary>
/// Filter SQL statements to prevent Injection
/// </Summary>
/// <Param name = "strSql"> </param>
/// <Returns> 0-No injection, 1-Yes </returns>
Public int filterSql (string sSql)
{
Int srcLen, decLen = 0;
SSql = sSql. ToLower (). Trim ();
SrcLen = sSql. Length;
SSql = sSql. Replace ("exec ","");
SSql = sSql. Replace ("delete ","");
SSql = sSql. Replace ("master ","");
SSql = sSql. Replace ("truncate ","");
SSql = sSql. Replace ("declare ","");
SSql = sSql. Replace ("create ","");
SSql = sSql. Replace ("xp _", "no ");
DecLen = sSql. Length;
If (srcLen = decLen) return 0; else return 1;
}

Stored Procedure

Because the type of the variable can be set in the stored procedure, no data operation is required.

Vulnerability Demonstration:
Http://xxx.xxx.xxx.xxx/xxx.asp? Id = 17; drop table D99_Tmp; create table D99_Tmp (subdirectory VARCHAR (100), depth VARCHAR (100), VARCHAR (100 ))

After the SQL statement is disabled, the characters "exec, master, delete, truncate, declare, create, xp _" cannot appear.

:::::::::::::::::::::::::::::::::::::::: :::::::::::::::::::::::::::::::::::::::: :::::::::::::::::::::::::::::::::::::::: :::::::::::::::::::::::::::::::::::

SQL injection attacks are a very annoying security vulnerability. All web developers, No matter what platform, technology, or data layer, need to be sure what they understand and prevent. Unfortunately, developers tend to spend less time on this and even their applications. Even worse, their customers are extremely vulnerable to attacks.

Michael suton recently published a very thought-provoking post about how common this problem is on the public network. He built a C # client program using Google's Search API to find websites vulnerable to SQL injection attacks. The procedure is simple:

Search for websites with query strings (for example, those URLs with "id =" in the URL)
Send a request to these websites that are determined to be dynamic, change the id = Statement and put an extra single quotation mark to try to cancel the SQL statement (for example, id = 6 ')
Analyze the returned responses and search for words such as "SQL" and "query". This often indicates that the application returns a detailed error message (which is also bad)
Check whether the error message indicates that the parameters sent to the SQL Server are not correctly encoded. If so, SQL injection attacks can be performed on the website.
Random sampling tests on the 1000 websites found through Google search found that 11.3% of them were vulnerable to SQL injection attacks. This is terrible. This means that hackers can remotely exploit the data in those applications to obtain any password or credit card data without hashed or encryption, and even log on to these applications as administrators. This is not only bad for developers who develop websites, but also worse for consumers or users who use websites, because they provide data for websites and think that websites are secure.

So what is SQL injection attack?

There are several situations that make SQL injection attacks possible. The most common reason is that you have constructed SQL statements dynamically, but you have not used the encoded parameter correctly. For example, considering the encoding of this SQL query, the objective is to query the author (Authors) based on the social security number provided by the query string ):

Dim SSN as String
Dim SqlQuery as String

SSN = Request. QueryString ("SSN ")
SqlQuery = "SELECT au_lname, au_fname FROM authors WHERE au_id = '" + SSN + "'"

If you have the same SQL code as the preceding segment, your entire database and application can be remotely hacked. Why? Under normal circumstances, a user uses a social insurance number to access the website. The Code is as follows:

'Url to the page containing the above code
Http://mysite.com/listauthordetails.aspx? SSN = 172-32-9999

'SQL Query executed against the database
SELECT au_lname, au_fname FROM authors WHERE au_id = '2017-32-9999'

This is what developers expected. They can use social insurance numbers to query the author's information in the database. However, because the parameter value is not properly encoded, hackers can easily modify the value of the query string and embed an additional SQL statement after the value to be executed. For example,

'Url to the page containing the above code
Http://mysite.com/listauthordetails.aspx? SSN = 172-32-9999 '; drop database pubs --

'SQL Query executed against the database
SELECT au_lname, au_fname FROM authors WHERE au_id = ''; drop database pubs --
Note: No. I can add the "'; drop database pubs --", and terminate the current SQL statement through the ";" character after the SSN query string value, then, I added my own malicious SQL statement and commented out other parts of the statement using the "--" string. Because we manually construct an SQL statement in encoding, we finally pass this string to the database. The database will first query the authors table and then delete our pubs database. The database is gone with a bang!

In case you think that anonymous hackers have very bad results in deleting your database, but unfortunately, in fact, this is a good case in the case of SQL injection attacks. A hacker can execute a JOIN statement to obtain all the data in your database and display the data on the page to allow them to obtain the user name instead of simply destroying the data, password, credit card number, and so on. They can also add the UPDATE/INSERT statement to change the product price and add a new Administrator Account to screw up your (screw up your life. Imagine that the actual number of products in your warehouse is different from the number reported by your account system at the end of the month...

How can you protect yourself?

You need to worry about SQL injection attacks. No matter what web programming technology you use, all web frameworks need to worry about this. You need to follow several basic rules:

1) when constructing dynamic SQL statements, you must use the type-safe parameter encryption mechanism. Most data APIs, including ADO and ADO. NET, with this support, you can specify the exact type (such as string, integer, date, etc.) of the provided parameters, you can ensure that these parameters are properly escaped/encoded, to prevent hackers from using them. These features must be used from the beginning to the end.

For example, for dynamic SQL statements in ADO. NET, you can rewrite the preceding statements as follows to ensure security:

Dim SSN as String = Request. QueryString ("SSN ")

Dim cmd As new SqlCommand ("SELECT au_lname, au_fname FROM authors WHERE au_id = @ au_id ")
Dim param = new SqlParameter ("au_id", SqlDbType. VarChar)
Param. Value = SSN
Cmd. Parameters. Add (param)
This will prevent attempts to secretly inject other SQL expressions (because of ADO. NET knows how to encode the string value of au_id, and avoid other data problems (for example, incorrectly converting the value type ). Note that the TableAdapter/DataSet Designer built in VS 2005 automatically uses this mechanism, as does the ASP. NET 2.0 data source control.

A common misperception is that if you use a stored procedure or ORM, you are completely immune from SQL injection attacks. This is incorrect. You still need to be sure that you are very cautious when passing data to the stored procedure, or when Using ORM to customize a query, your approach is safe.

2) always perform security review before deploying your application ). Establish a formal security process to review all codes every time you update them. It is particularly important later. Many times I have heard that the development team will perform a very detailed security review before the official launch (going live), and then they will make small updates several weeks or months later, they will skip the security review and say, "It is a small update. We will review the code later ". Always stick to the security review.

3) do not store sensitive data in plaintext in the database. My personal opinion is that passwords should always be stored after one-way hashed, and I do not even like to store them after encryption. By default, the ASP. NET 2.0 Membership API automatically performs this operation for you, and implements safe SALT randomization behavior (SALT randomization behavior ). If you decide to create your own member database, I suggest you check the source code of our Membership provider published here. At the same time, you are sure to encrypt the credit card and other private data in your database. In this way, even if your database is intruded into (compromised), at least your customers' private data will not be used by others.

4) check that you have compiled an automated unit test to verify that your data access layer and applications are not vulnerable to SQL injection attacks. This is very important and helps catch "is a small update, and there will be no security issues, to provide additional security layers to avoid accidental introduction of bad security defects to your application.

5) locking your database security only gives the minimum permissions required to access the web application functions of the database. If the web application does not need to access some tables, make sure that it has no permission to access these tables. If a web application only needs read-only permission to generate reports from your account payable table, make sure that you disable the insert/update/delete permission on the table.

SQL injection attacks

SQL injection attacks are designed to exploit vulnerabilities, running SQL commands on the target server and other attacks dynamically generate SQL commands without verifying user input data is the main cause of the successful SQL injection attack.

For example:
If your query statement is select * from admin where username = "& user &" and password = "& pwd &""

Then, if my user name is: 1 or 1 = 1

Then, your query statement will become:

Select * from admin where username = 1 or 1 = 1 and password = "& pwd &""

In this way, your query statements are passed and you can access your management interface.

Therefore, you need to check user input for defense purposes. Special characters, such as single quotes, double quotation marks, semicolons, commas, colons, and connection numbers, are converted or filtered.

Special characters and strings to be filtered include:

Net user
Xp_mongoshell
/Add
Exec master. dbo. xp_mongoshell
Net localgroup administrators
Select
Count
Asc
Char
Mid

:
"
Insert
Delete from
Drop table
Update
Truncate
From
%

The following code prevents injection attacks for your reference!

Code for preventing SQL injection attacks in js:

　　
<Script language = "javascript">
<! --
Var url = location. search;
Var re =/^ \? (. *) (Select % 20 | insert % 20 | delete % 20 from % 20 | count \ (| drop % 20table | update % 20 truncate % 20 | asc \ (| mid \ (| char \ (| xp_mongoshell | exec % 20master | net % 20 localgroup % 20administrators | \ "|: | net % 20user | \ | % 20or % 20 )(. *) $/gi;
Var e = re. test (url );
If (e ){
Alert ("the address contains invalid characters ~ ");
Location. href = "error. asp ";
}
// -->
<Script>

Code for asp to prevent SQL injection attacks ~ :

[Code start]
<%
On Error Resume Next
Dim strTemp

If LCase (Request. ServerVariables ("HTTPS") = "off" Then
StrTemp = "http ://"
Else
StrTemp = "https ://"
End If

StrTemp = strTemp & Request. ServerVariables ("SERVER_NAME ")
If Request. ServerVariables ("SERVER_PORT") <> 80 Then strTemp = strTemp & ":" & Request. ServerVariables ("SERVER_PORT ")

StrTemp = strTemp & Request. ServerVariables ("URL ")
If Trim (Request. QueryString) <> "Then strTemp = strTemp &"? "& Trim (Request. QueryString)

StrTemp = LCase (strTemp)
If Instr (strTemp, "select % 20") or Instr (strTemp, "insert % 20") or Instr (strTemp, "delete % 20 from") or Instr (strTemp, "count (") or Instr (strTemp, "drop % 20 table") or Instr (strTemp, "update % 20") or Instr (strTemp, "truncate % 20 ") or Instr (strTemp, "asc (") or Instr (strTemp, "mid (") or Instr (strTemp, "char (") or Instr (strTemp, "xp_{shell ") or Instr (strTemp, "exec % 20 master") or Instr (strTemp, "net % 20 localgroup % 20 administrators") or Instr (strTemp ,":") or Instr (strTemp, "net % 20 user") or Instr (strTemp, "") or Instr (strTemp, "% 20or % 20") then
Response. Write "<script language = javascript>"
Response. Write "alert (Invalid Address !!); "
Response. Write "location. href = error. asp ;"
Response. Write "<script>"
End If
%>
[Code end]

C # Check strings to prevent SQL injection attacks
This example is tentatively set to = and No.
Bool CheckParams (params object [] args)
{
String [] Lawlesses = {"= ",""};
If (Lawlesses = null | Lawlesses. Length <= 0) return true;
// Construct a regular expression. For example, if Lawlesses is a = sign and a number, the regular expression is. * [=}]. * (for details about the regular expression, see MSDN)
// In addition, because I want to make a general and easy-to-Modify function, I have added a step from a character array to a regular expression. In actual use, I can directly write a regular expression;

String str_Regex = ".*[";
For (int I = 0; I <Lawlesses. Length-1; I ++)
Str_Regex + = Lawlesses [I] + "| ";
Str_Regex + = Lawlesses [Lawlesses. Length-1] + "]. *";
//
Foreach (object arg in args)
{
If (arg is string) // if it is a string, directly check
{
If (Regex. Matches (arg. ToString (), str_Regex). Count> 0)
Return false;
}
Else if (arg is ICollection) // if it is a set, check whether the element in the set is a string or not.
{
Foreach (object obj in (ICollection) arg)
{
If (obj is string)
{
If (Regex. Matches (obj. ToString (), str_Regex). Count> 0)
Return false;
}
}
}
}
Return true;

The following are simple preventive methods. These are all familiar methods. I just post them. I hope I can help you a little bit ~

It is mainly used to transmit numeric variables:

Id = Request. QueryString ("id ")

If Not (isNumeric (id) Then

Response. Write "Invalid Address ~ "

Response. End

End If

The following is the normal display code ~

How to Prevent SQL injection attacks during re-Encoding

1. What is an SQL injection attack? We know that Microsoft's SQL Server database supports multiple SQL statements in one database query. For example, in the query manager, We can enter an SQL statement.
Select * from table1 select * from table2
If table1 and table2 exist in the selected database, the SQL statement can be executed successfully and the correct results can be returned. At the same time, we know that many systems or websites published on the Internet now, for convenience, administrators generally directly use the sa identity to connect to the database, the SQL injection attack mainly captures the system's vulnerabilities in these two aspects. Attackers can insert SQL statements into web form input or query strings on the page, this way, the server is spoofed to execute malicious SQL commands to attack the server.

2. A detailed explanation of injection attacks SQL Below we will take a simple user login as an example, with a detailed explanation of the Code SQL injection attacks, and their preventive measures. The possible code for a simple user login is as follows:
Try
{
String strUserName = this.txt UserName. Text;
String strPwd = this.txt Pwd. Text;
String strSql = "select * from userinfo where UserName = '" + strUserName + "' and Password = '" + strPwd + "'";
SqlConnection objDbConn = new SqlConnection ("database connection string ");
SqlDataAdapter objAdapter = new SqlDataAdapter (strSql, objDbConn );
DataSet objDataSet = null;
ObjAdapter. Fill (objDataSet); // TODO determines the obtained data.
}
Catch (System. Exception e)
{
This. lblMsg. Text = e. Message;
This. lblMsg. Visible = true;
}
In the above Code, if the user input is a normal user name and password, the execution will be normal, but if the user name is entered, if you enter "Johnny" -- ", the statement executed in SQLServer will be" select * from userinfo where UserName = 'Johnny '--' and Password = 'Password '", as long as the user johny exists in the database, the statement can be successfully executed regardless of the password and can be successfully logged in. What's more, we know that SQLServer has some system stored procedures that can execute many commands of the operating system, such as xp_mongoshell. If the above user logs on, in the user name section, enter "johny 'exec xp_cmdshell 'format d:/s' --". What are the consequences? For malicious users, you only need to slightly modify the 'format d:/s' command to do a lot of illegal things.

3. how to Prevent SQL injection attacks since we have explained why SQL injection attacks are generated, let's talk about how to prevent this situation and there are many solutions. Let's talk about it one by one.

A) do not use sa to connect to the database during system deployment. Create a new account in the database to restrict the permissions of this account, you can only execute the ADD and modify permissions in the specified database, so that we can effectively prevent the server from being attacked by executing xp_mongoshell.

B) use stored procedures to execute all queries. The SQL parameter transmission method prevents attacks by using single quotes and hyphens. In addition, it allows the database permission to be limited to only allow execution of specific stored procedures. All user input must comply with the security context of the called stored procedure, in this way, it is difficult to launch injection attacks again. It may be said that I only want to execute a simple query. Do I need to write a stored procedure? Is it too troublesome, but it doesn't matter, we only need to use SqlParameter in ado.net to slightly modify the above Code, as shown below:
Try
{
String strUserName = this.txt UserName. Text;
String strPwd = this.txt Pwd. Text;
String strSql = "select * from userinfo where UserName = @ UserName and Password = @ Password ";
SqlConnection objDbConn = new SqlConnection ("database connection string ");
SqlDataAdapter objAdapter = new SqlDataAdapter (strSql, objDbConn );
ObjAdapter. SelectCommand. Parameters. Add ("@ UserName", strUserName );
ObjAdapter. SelectCommand. Parameters. Add ("@ Password", strPwd );
DataSet objDataSet = null;
ObjAdapter. Fill (objDataSet); // TODO determines the obtained data.
}
Catch (System. Exception e)
{
This. lblMsg. Text = e. Message;
This. lblMsg. Visible = true;
}
In the above code, we can better prevent SQL injection attacks because there is a system stored procedure of sp_executesql in SQLServer. As long as the SQL statement uses SqlParameter In ado, the SQL statement will be executed through sp_executesql. You can use the Profile in SQL Server to track the specific situation. By using this method, we do not need to write a stored procedure, so that we can effectively prevent SQL injection attacks and verify user input, escape the single quotes in the user input (replace one single quotes with two single quotes), and filter out the comments and special commands.