sql server consulting logo
sql server consulting is our only business

Free SQL Server Consultation

Sometimes you only need the answer to a question or two. If 15 minutes of our time can help you solve a SQL Server problem, we are happy to help. No charge, no strings attached.

Call 503.914.6442

Using SQL Server in the Enterprise - Archives

Sign up for your FREE subscription to this SQL Newsletter

Give this article a Google +1!

SQL Injection Damage Control

Issue 5 Number 2 February 2013

I have begun to get calls again about a SQL Injection attack that has compromised the caller's web site. A number of calls like this are often a warning that a new and successful attack is making the rounds. It may eventually compromise a huge number of web sites, so maybe it is time to write again about damage control.

The subject of preventing penetration of your site through SQL Injection is complex. However, the subject of preventing damage from the successful penetration can be pretty simple.

Much has been written about defending against SQL Injection and I don't intend to repeat it here. This is not an article about guarding against SQL Injection. It is about limiting or even eliminating the damage when a hacker does get through your defenses.

The More Things Change, the More They Stay the Same

Although hackers continue to discover new vulnerabilities, the most common form of attack hasn't changed much in terms of how it does its dirty work. The most common objective of injection attacks is to plaster the victim site with a URL that points visitors to a web site or a malicious bit of code.

The attacker does this by inserting the text of the malicious URL into the character columns in your database, usually appending it to the existing contents of the column. This causes the URL to appear wherever data from those columns is used in your web pages. This exploit.defaces your site and puts visitors to your site in danger when they accidentally or purposely click the malicious URL.

It takes time to remove all of this text from your database. Typically, as soon as you get it cleaned up, the attacker defaces it again (reinforcing the need to patch the vulnerability itself, not just control the damage)

Implementing Damage Control

First ask yourself, "how does the attacker know the names of the tables and columns to deface?" In the middle of posing the question, you will probably realize that you already know the answer as well as the key to damage control.

In order to work the evil magic, the attacker has to be able to look up the table names, column names and column data types in various system tables. The simple solution is to deny access to those tables to the login account or accounts that can connect from the application to the database. In many cases, the application always connects using the same login which simplifies things a bit.

Execute this SQL code to keep the hacker out of the critical system tables, but first make certain that your application does not need access to those views in order to do its job. It is very rare that an application would need to access these views but it could happen.

  • GO
  • GO
  • DENY SELECT ON sys.columns TO myuser
  • GO
  • DENY SELECT ON sys.tables TO myuser
  • GO
  • DENY SELECT ON sys.objects TO myuser
  • GO

Replace myuser with your user account that connects from the application to the database. If you have a lot of user accounts to protect, you might apply this code to a custom security group rather than an individual login. Put all of the application logins in the group.

You will find that to change permission on the INFORMATION_SCHEMA views you have to add that user account to the Master database and execute the DENY command on Master. The other commands must be run in the application database that you are trying to protect.

A Word to the Wise

Remember that this method ONLY helps control damage. It does not fix the underlying problem. It buys you a little time, but you still have to find and fix the vulnerability that let the attacker in. Remember also that this only works on the sort of attack that depends on access to these system views. However, that is a high percentage of the attacks currently making the rounds.

Attacks have a way of growing more sophisticated once they penetrate your defenses. Even if this solution worked in the first attack, there is no guarantee that it will continue to work. So long as the vulnerability is not fixed, you and your database are still in danger.

click here to tweet this newsletter

Afterward by the Author

I hope this information has been helpful to you. I would appreciate any feedback you can give me about this article or recommend future articles you would like to see. If you enjoyed reading this article, you might like to receive this monthly newsletter via Twitter. If so, follow me.

As always, if you have questions or comments give me a call at 503.914.6442 or send me email.

Kurt Survance, SQL Consulting, Inc.

Return to Top

Go to SQL Consulting Home

On this page:

SQL Injection Damage Control

Elsewhere on this site:

Call SQL Consulting
mcse microsoft certified systems engineer
mcdba microsoft certified database administrator