Tips and tricks to prevent SQL Injection Attack-vulnerability research

"Original address" Tip/trick:guard against SQL injection attacks
"Original published date" Saturday, September, 2006 9:11 AM

SQL injection attacks are a very annoying security vulnerability that all Web developers, regardless of platform, technology, or data tier, need to be sure they understand and prevent. Unfortunately, developers tend to spend less time on this and their applications, and, worse, their customers are extremely vulnerable to attack.

Michael Sutton recently published a very sobering post about how pervasive the problem is on the public web. Using Google's search API, he built a C # client program to look for sites that are vulnerable to SQL injection attacks. The steps are simple:

Find sites with query strings (for example, query for URLs with "id=" in the URL)
Send a request to these dynamic Web sites, change the id= statements in them, and bring an extra single quote to try to cancel the SQL statements (for example, id=6 ')
Parse the returned reply, looking for words like "SQL" and "query", which often means that the application returns detailed error messages (which in itself is bad)
Check that the error message indicates that the parameters sent to the SQL Server have not been properly encoded, and if so, that the site can be injected with SQL injection attacks
For random sampling tests of 1000 sites found through Google search, he detected 11.3% of them susceptible to SQL injection attacks. It's very, very scary. This means that hackers can remotely exploit the data in those applications, get any passwords or credit card data that is not hashed or encrypted, and even have the possibility to log into these applications as an administrator. This is not only bad for developers who develop Web sites, but also worse for consumers or users who use them, because they provide data to the site and think that the site is safe.

So what exactly is a SQL injection attack?

There are several scenarios that make SQL injection attacks possible. The most common reason is that you have dynamically constructed the SQL statement without using the correctly coded (encoded) parameter. For example, consider the encoding of this SQL query, which is intended to query the author (Authors) based on the social security number provided by the query string (social):


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 one above, your entire database and application can be hacked away remotely. How could it be? Under normal circumstances, a user would use a social security number to access the site, and the code would be executed like this:


' 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 = ' 172-32-9999 '

This is what developers expect, through social Security numbers, to query the author's information in the database. However, because the value of the parameter is not properly added, the hacker can easily modify the value of the query string and embed the additional SQL statement after the value to be executed. Such as


' URL to the ' page containing the above code
http://mysite.com/listauthordetails.aspx?SSN=172-32-9999 ';D ROP DATABASE pubs--

' SQL Query executed against the database
SELECT au_lname, au_fname from authors WHERE au_id = ';D rop DATABASE pubs--
Note that no, I can add "';D ROP DATABASE pubs--" by ";" After the SSN query string value. Character to terminate the current SQL statement, then add my own malicious SQL statement, and then annotate the other part of the statement with the "--" string. Because we are manually constructing the SQL statement in the code, we finally pass this string to the database, the database will first query the authors table, and then delete our pubs database. "Bang (Bang)" A sound, the database is gone!

In case you think that anonymous hackers delete your database is bad, but unfortunately, in fact, this is in the context of SQL injection attack is relatively good. Instead of simply destroying the data, a hacker can execute a join statement to get all the data in your database, display it on the page, and allow them to get the username, password, credit card number, and so on. They can also add Update/insert statements to change the price of a product, add a new admin account, and really screw you (screw up your life). Imagine, by the end of the month, that the number of actual items in your warehouse is different from your account system (accounting system) when you check inventory.

So how do you protect yourself?

SQL injection attacks are something you need to worry about, no matter what web programming techniques you use, and all the Web frameworks you need to worry about. You need to follow a few very basic rules:

1 when constructing dynamic SQL statements, you must use the parameter-doubling mechanism of class security (Type-safe). Most data APIs, including ADO and Ado.net, have the support to allow you to specify the exact type of arguments provided (for example, strings, integers, dates, and so on) to ensure that these parameters are properly escaped/encoded to prevent hackers from exploiting them. Be sure to use these features from beginning to end.

For example, in ado.net for dynamic SQL, you can rewrite the above statement as follows to make it secure:

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 prevents someone from trying to sneak into another SQL expression (because ado.net knows the au_id string value) and avoids other data problems (such as improper conversion of numeric types, and so on). Note that the VS 2005 built-in Tableadapter/dataset Designer automatically uses this mechanism, as does the ASP.net 2.0 data source control.

A common misconception (misperception) is that if you use a stored procedure or ORM, you are completely protected from SQL injection attacks. This is not true, you still need to be sure that you are cautious when passing data to a stored procedure, or that you are safe when you customize a query with ORM.

2 always do a security review before deploying your Application (review). Establish a formal security process (formal) and review all coding every time you make an update. The latter point is particularly important. A lot of times I've heard that the development team will do a very detailed security review before the formal launch (going live) then, after a few weeks or months, when they make minor updates, they skip the security review, pleading, "Just a little update, we'll do the code review later." Please always adhere to the security review.

3 do not put sensitive data in the database to be stored in clear text. My personal opinion is that passwords should always be stored after one-way (one-way) hashed, and I don't even like to store them after they are encrypted. By default, the ASP.net 2.0 membership API automatically does this for you, and it also implements a safe salt randomization behavior (salt randomization behavior). If you decide to build your own membership database, I suggest you look at the source code of our own membership provider published here. Also make sure that you encrypt the credit card and other private data in your database. This way, even if your database is hacked (compromised), at least your client's private data will not be used by anyone.

4 Confirm that you have written automated unit tests to specifically validate your data access layer and applications from SQL injection attacks. This is important to help capture the negligence of "a small update, all without security problems", to provide additional layers of security to avoid accidentally introducing bad security flaws into your application, he said.

5 Lock the security of your database, giving only the minimum permissions required to access the database's Web application features. If the Web application does not require access to some tables, verify that it does not have permission to access the tables. If the Web application only requires read-only permission to generate a report from your Account Payables table, then confirm that you prohibit it from Insert/update/delete permissions on this table.

How to learn more about the relevant knowledge

Microsoft Prescriptive Architecture Guidance (PAG) product group published a number of excellent safety guidelines for the documentation, you should set aside some time to read:

ASP.net 2.0 safety designation policy
ASP.net 2.0 Security Deployment Checklist
ASP.net 2.0 Security Practices overview
Web Application Engineering Safety Index
How to Secure code review for managed encodings
ASP.net 2.0 Security Issues List
ASP.net 2.0 Security Training module (training Modules)
In addition, these other Pag how-to articles are useful for further understanding of 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 on asp.net security in my blog post on security and my asp.net tips and tricks page.

Update: Bertrand gave me a very good post about SQL injection attacks that he wrote 2 years ago and is well worth reading.

I hope this article is of some help to you,

Scott