Experience in ASP. NET website construction on the Godaddy server-anti-SQL Injection Attack (III)

Author: que rongwen (querw)

What are SQL injection attacks and what are the dangers?

Let's look at an example to explain how SQL injection attacks occur and what are the dangers.

For websites with user participation, the most important thing in all operations is to log on. the user is required to enter the user name and password, and then verify the input validity in the database.
I believe that many people (which I have always written) write code similar to the following:

(Code 1)
String strUserName = txtUserName. Text;
String strPwd = txtPwd. Text;
String strSql = "select * from UserTable where username = ";
StrSql + = "'" + strUserName "'";
StrSql + = "and password = '" + strPwd + "'";
...

Code that relies on user input to "piece together" SQL statements is extremely fragile. assume that the password entered by a user (maybe a hacker) is 'or '1' = '1 username, which does not matter. Let's see what this SQL statement will become:

Select * from UserTable where username = 'what ever you input' and password = ''or '1' = '1'

When this statement is executed, the entire UserTable table is returned, and the username of many website management accounts is admin, the tragedy cannot be avoided, intruders can easily Log On As administrators, no matter how long and complex your password is. in fact, intruders can exploit the above vulnerabilities to obtain much more information than you think. If he is proficient in SQL, the entire website may become his toys. this is probably the meaning of the so-called SQL injection attack. SQL injection attacks use normal WEB browsing tools, and firewalls cannot do anything about them. There are many ready-made attack tools that are easy to use. if the website has such a vulnerability, anyone may easily break your website.

How to prevent it?
Search for the "universal anti-injection" code on the Internet. The principle is to check the data submitted by visitors (including using the GET and POST methods, if the content contains some SQL-sensitive words, take corresponding preventive measures. for example, a keyword Dictionary of "General Anti-injection" is: '| exec | insert | select | delete | update | count | chr | truncate | char | declare | -- | script | * | char | set | (|) all submitted content contains any of the above words and is considered an attack attempt.

It is undeniable that this is a solution, but I think it is only a temporary solution for the following reasons:
1. the percussion surface is too large. For example, for a blog or news website, it is normal that a user's submission contains the above words. However, if it is filtered, it is considered an intrusion.
2. There is no fundamental solution to the problem, and the hacker technology is developing very fast. The so-called high foot, high foot. This keyword dictionary may never contain any dangerous keywords.

To solve the problem, find the root cause of the SQL injection vulnerability. The root cause is to "splice" the SQL statement with the visitor's input and execute it.Do not splice SQL statements.-Use parameters or stored procedures. Through parameters or stored procedures, SQL statements are controlled by developers/administrators. user input is restricted to a specific scope of use, as if the user's teeth are sharp, it cannot bite anything outside the cage.

(Code 2)
String strSql = "select * from UserTable where UserName = @ UserName and Password = @ Password ";
SqlParameter [] param = new SqlParameter []
{
New SqlParameter ("@ UserName", strName ),
New SqlParameter ("@ Password", strPwd)
};
DataSet ds = db. OpenDataSetS (strSql, param );
...

At present, no matter What UserName or Password the intruder submits, it is passed to the database engine as the value of @ UserName and @ Password, without affecting the SQL statement itself.

In addition, it is always advantageous to filter properly. For example, such a link is http: // xxxx/userdetail. aspx? Userid = 100001
The QueryString method of the Request (or similar method) is used on the server page. The background of this article is ASP. NET/C #/SQL Server 2005) obtain the userid value. If you directly use userid as a part of the SQL statement, it will cause an SQL injection vulnerability. however, it is obvious that userid is a numeric value. If the string is always converted to a numeric value before use, the attack can be avoided. there are many other similarities, such as the maximum number of characters for a specified user name. You can check it when receiving the input from the visitor. all in all, logical detection will make the program more rigorous and error controllable.

Postscript
My website has experienced SQL injection attacks. Before that, I used "Patchwork" SQL statements to write code, which gave me a painful lesson. the website is installed with shell, and an important mailbox is stolen (the mailbox password is set to the same as the website admin password, another painful lesson .)
The domain name has been modified. (the domain name password and the admin password are also set to the same, so be patient ...) fortunately, the "hacker" finally gave me all the passwords for the sake of our fellow siblings. I was beaten to take a lesson. thank you for your kindness. If you encounter a malicious attacker... unimaginable.

In addition, I found a saying on the Internet that using parameters does not absolutely prevent SQL injection attacks, but I did not give a reason. I am not very clear, if you know, can you tell me (querw@sina.com )? Thank you very much.

Appendix:OpenDataSetS () function in Code 2

Public System. Data. DataSet OpenDataSetS (string strSql, SqlParameter [] param)
{
// Determine whether the connection string is null.
If (! Open () return null;

DataSet ds = null;
SqlDataAdapter da = new SqlDataAdapter ();
SqlCommand cmd = new SqlCommand ();
Cmd. Connection = "*** my SQL connection string ***";
Cmd. CommandType = CommandType. Text;
Cmd. CommandText = strSql;

If (param! = Null)
{
Foreach (SqlParameter p in param)
{
Cmd. Parameters. Add (p );
}
}
Da. SelectCommand = cmd;

Ds = new DataSet ();
Da. Fill (ds );

Cmd. Dispose (); cmd = null;
Da. Dispose (); da = null;
Return ds;
}