(Special thanks to Neil Carpenter for helping out on this blog post)

Recent Trends

Beginning late last year, a number of websites were defaced to include malicious HTML <script> tags in text that was stored in a SQL database and used to generate dynamic web pages. These attacks began to accelerate in the first quarter of 2008 and are continuing to affect vulnerable web applications.

The web applications compromised share several commonalities:

• Application uses classic ASP code
• Application uses a SQL Server database
• Application code generates dynamic SQL queries based on URI query strings (http://consoto.com/widgets.asp?widget=sprocket)

This represents a new approach to SQL injection (http://msdn.microsoft.com/en-us/library/ms161953.aspx). In the past, SQL injection attacks were targeted to specific web applications where the vulnerabilities and the structure of the underlying database were either known or discovered by the attacker. This attack differs because it has been abstracted such that it is possible to attack virtually any vulnerability that is present in an ASP page creating dynamic SQL queries from URI query strings. Additional technical details and a walkthrough of the specifics are available at http://blogs.technet.com/neilcar/archive/2008/03/15/anatomy-of-a-sql-injection-incident-part-2-meat.aspx.

This attack does not exploit vulnerabilities in Windows, IIS, SQL Server, or other infrastructure code; rather, it exploits vulnerabilities in custom web applications running on this infrastructure. Microsoft has investigated these attacks thoroughly and determined that they are not related to any patched or 0-day vulnerabilities in Microsoft products. More information can be found at http://blogs.technet.com/msrc/archive/2008/04/25/questions-about-web-server-attacks.aspx.

As indicated above, these attacks have been accelerating through the year. This would appear to be related to at least two factors. First, there is a malicious tool that is in the wild that automates this. SANS discusses that tool here -- http://isc.sans.org/diary.html?storyid=4294. The tool uses search engines to find vulnerable sites to SQL injection.

The second factor is that one or more malicious bots are now launching SQL injection attacks as a way of spreading the bot further. SecureWorks discusses an example at http://www.secureworks.com/research/threats/danmecasprox/.

Once a server has been defaced using this attack, it will begin including a malicious <script> tag pointing to a .js file. While the contents of these files differ, they all attempt to exploit various vulnerabilities including already-patched Microsoft vulnerabilities and vulnerable third-party ActiveX controls. Since these scripts are hosted independently, it is possible that the scripts can be changed rapidly to exploit new client vulnerabilities and can be easily tailored to target on a “per browser” basis.

IT/database administrators Recommendations

There are a number of things that IT administrators and database administrators should do to limit risk and respond to possible incidents on the code and infrastructure they manage:

• Review IIS logs and database tables for signs of previous exploits

Since this exploit takes place via the URI query string, administrators can review IIS logs to find anomalous queries that may be attempts to exploit this. Information on how to do this manually is available at http://blogs.technet.com/neilcar/archive/2008/03/15/anatomy-of-a-sql-injection-incident-part-2-meat.aspx. A sample of an automated tool is available at http://www.codeplex.com/Release/ProjectReleases.aspx?ProjectName=WSUS&ReleaseId=13436.

If IIS logs show that the server has possibly been exploited, the next step would be to inspect tables in databases that are used by the associated web applications, looking for <script> tags appended to cells in text columns.

NOTE: IIS servers should never run in production with logging disabled. While the storage and administration requirements of IIS logging can be significant, the lack of IIS logs makes it very difficult to respond to security incidents.

• If running 3rd party code that uses a database back-end, consult ISV about susceptibility to SQL injection

In cases where 3rd party ASP web applications are being used, administrators should contact the application vendors to ensure that they are not susceptible to SQL injection attacks.

• Validate that the account(s) that are used from the web application have least possible privilege in the database

Administrators should make sure that the SQL users that the web application uses have the least privilege necessary. Web applications should never connect as users with administrative privilege such as “sysadmin” at the server level or “db_owner” at the database level. The white paper “best practices for setting up and maintaining security in SQL Server 2005” http://download.microsoft.com/download/8/5/e/85eea4fa-b3bb-4426-97d0-7f7151b2011c/SQL2005SecBestPract.doc provides recommendations for various aspects of SQL server security.

Web developers Recommendations

There are several good documents on how Web developers can prevent SQL injection attacks when writing code. Since these attacks leverage vulnerable web application code, the only way to completely prevent them is to resolve vulnerabilities in the code. Any place that the code dynamically generates a SQL query using data from an external source (and, particularly, from a URI query string) should be considered suspect. Once code vulnerabilities are identified, they need to be carefully resolved.

• Explained – SQL Injection, ASP.NET, ADO.NET:

http://msdn.microsoft.com/en-us/library/bb671351.aspx

Also, the above document contains a pointer to the following article “How To: Protect From SQL Injection in ASP.NET” http://msdn.microsoft.com/en-us/library/ms998271.aspx (which still applies to ASP)

A very useful video can be found here (this is the video refer on the previous article, but that link is currently broken): http://channel9.msdn.com/wiki/default.aspx/SecurityWiki.SQLInjectionLab

• Generic information about how SQL Injection works:

http://msdn.microsoft.com/en-us/library/ms161953.aspx

• SQL injections in ASP code (this is different from ASP.NET!):

http://msdn.microsoft.com/en-us/library/cc676512.aspx

How to call SQL Server stored procedures from ASP:

http://support.microsoft.com/kb/q164485

• The Microsoft Security Development Lifecycle (SDL) has specific guidance to defend against SQL injection. In simple terms there are three different strategies to eradicate SQL Injection attacks:

1. Using SQL parameterized queries

2. Using stored procedures

3. Using SQL execute-only permissions

Michael Howard covers those topics in http://blogs.msdn.com/sdl/archive/2008/05/15/giving-sql-injection-the-respect-it-deserves.aspx

Also, Writing Secure Code 2^nd has good guidance on how to prevent these type of attacks as well (see pages 399-411)

• SQL Injection Mitigation: Using Parameterized Queries (Part 1 & 2). The advantage of using parameterized queries is that it separates the executable code (ie, the SELECT statement) from the data (the dynamic information supplied by the application’s user). This approach prevents any malicious statements passed along by the user from executing.

Part 1: http://blogs.technet.com/neilcar/archive/2008/05/21/sql-injection-mitigation-using-parameterized-queries.aspx

Part 2: http://blogs.technet.com/neilcar/archive/2008/05/23/sql-injection-mitigation-using-parameterized-queries-part-2-types-and-recordsets.aspx

• Filtering SQL injection form Classic ASP code (or blacklisting keywords), we considered the below as temporary workarounds since in reality it does not fix the root cause of the bugs (i.e. the code is still vulnerable and it might be reachable even after the filtering)

Nazim from the IIS team explains how to do the filtering in detail here: http://blogs.iis.net/nazim/archive/2008/04/28/filtering-sql-injection-from-classic-asp.aspx

If you are still not sure where to start, all web code that accesses a database with a particular focus on ASP code and areas of code which use user-supplied data should be review first.

End User Recommendations

End users should review the information at http://www.microsoft.com/protect/default.mspx; in addition, here are some specific steps that you can take to protect yourself.

• As always, browse responsibly — but be aware that this could also affect websites that the user trusts

While responsible browsing limits your exposure to vulnerability, it is possible that even websites you trust may have been compromised. Watch for unusual behavior, be aware of the risk, and implement the other recommendations in this section.

• Keep up to date on security updates, both Microsoft and 3rd party

Since the malicious scripts are exploiting known vulnerabilities, you should make sure that you are running the latest Microsoft and 3^rd party security updates. Microsoft security updates are available via http://update.microsoft.com. Additional information is available at http://www.microsoft.com/protect/computer/updates/OS.aspx.

• Disable unneeded ActiveX controls and Internet Explorer add-ons

You should disable any unneeded ActiveX controls and add-ons in Internet Explorer. To do this on Windows XP Service Pack 2 or later, follow these steps from KB883256 (http://support.microsoft.com/kb/883256):

1. Start Internet Explorer.
2. On the Tools menu, click Manage Add-ons.
3. Click the name of the add-on.
4. Use one of the following methods:
• Click Update ActiveX to replace the add-on with the current version. This option is not available for all add-ons.
• To enable an add-on, click Enable, and then click OK.
• To disable an add-on, click Disable, and then click OK.

You may have to restart Internet Explorer for the changes to take effect after you enable or disable an add-on.

For earlier operating systems, follow the instructions in KB154036 (http://support.microsoft.com/kb/154036).

• Take steps to reduce attack surface of 3rd party browsers if you are using them

If you are using an Internet browser other than Internet Explorer, you should ensure that you have installed the latest security updates and that you disable unneeded extensions and add-ons. Information for popular browsers can be found at:

Firefox - http://support.mozilla.com/en-US/kb/Firefox+Support+Home+Page
Opera - http://www.opera.com/support/
Safari - http://www.apple.com/support/safari/

• Run up-to-date anti-malware software

End users should ensure that they have anti-virus and anti-spyware software installed and that it is up to date. More information can be found at http://www.microsoft.com/protect/computer/antivirus/OS.aspx and http://www.microsoft.com/protect/computer/antispyware/OS.aspx. You can get a 90-day trial copy of Windows Live OneCare anti-virus/anti-spyware software at http://onecare.live.com/standard/en-us/install/install.htm.

- Security Vulnerability Research & Defense Bloggers

分类统计时候，我们可能经常会碰到这样的需求，每个分类按照一定顺序，取几条数据，然后在一起显示。

这个问题的解决方法，我们通过搜索引擎，可以找到很多中。但是不是SQL语句过于复杂，就是在数据量比较大时候，性能特别成问题。

今天我就碰到这样一个需求。而我自己的解决方案就是SQL过于复杂，或者性能比较差的。为此我在CSDN论坛发了个帖子，看有没有更好的解决方案。

http://topic.csdn.net/u/20080504/14/5c5866c3-8b91-45ef-ab17-f994f88f8e42.html

CSDN的 SQL Server 板块  不愧是高手云集，问题发出不到半小时，就获得了近10种解决方案。经过测试，我把性能最高，且SQL不复杂的方案整理出来。特别感谢 jinjazz 的解答。

经常发生 “数据库连接过多的错误” 这样的错误，但是却又不清楚当前的连接数为多少，大致的总结了几种方法。1.通过系统的“性能”来查看：开始->管理工具->性能（或者是运行里面输入 mmc）然后通过 添加计数器添加 SQL 的常用统计 然后在下面列出的项目里面选择用户连接就可以时时查询到数据库的连接数了。

PowerDesigner生成report   结果那些表的顺序都是乱的，并非按照命名排列

解决方案：

右键Table %ITEM% ,selection,define  sort and Filter 属性窗口,将选择排序选择NAME字段，可实现左侧按表名排列

右键List of columns of the table %PARENT% ,selection,define  sort and Filter 属性窗口,将选择排序字段全部去掉，可实现表字段按默认顺序排列

实际上，在SQL 2000中完全可以使用ROWCOUNT关键字解决这个问题。

ROWCOUNT关键字的用法在联机帮助中有比较详细的说明，这儿就不罗嗦了。谈谈体会。

1、使用ROWCOUNT查询前几行结果。