Tips and tricks to prevent SQL injection attacks

[Original address] Tip/Trick: Guard Against SQL Injection Attacks
[Original article publication date] Saturday, September 30,200 6 AM

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 response returned, and search for words such as "SQL" and "query". This often indicates that the application returns a detailed error message (which is also very 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 that no, I can add the "'; drop database pubs --" character after the SSN query string value to terminate the current SQL statement through the ";" character, 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. With a bang, the database is gone!

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 the negligence caused by the situation that "it 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.

How to Learn More

The Microsoft Prescriptive Architecture Guidance (PAG) product group has published many excellent security guidelines and documents. You should set aside some time to read them:

ASP. NET 2.0 Security Policy
ASP. NET 2.0 security deployment list
Overview of ASP. NET 2.0 security practices
Web Application Engineering Security Index
How to review managed Codes
ASP. NET 2.0 security question list
ASP. NET 2.0 Security Training Unit (Training Modules)
In addition, these other PAG How-To articles are useful for further understanding How To protect you from injection attacks:

How to Prevent form injection attacks in ASP. NET
How to Prevent SQL injection attacks in ASP. NET
You can also find useful information about ASP. NET Security on my post on security and my ASP. NET tips and tips.

Update: Bertrand told me that he wrote a great post about SQL injection attacks two years ago, which is worth reading.

I hope this article will help you,

Scott