Posts Tagged ‘SQL’
When a website’s database has been compromised with a SQL injection attack, it is important to clean it up as soon as possible. An attack of this nature passes SQL commands through a web site into a database. In the following case, many different HTML code statements were spread throughout multiple tables and columns. In short, the way to clean this type of attack is to identify the kind of injection, search for the offensive code, and then remove it.
Recently a company contacted us when they noticed a problem with their page titles. There was unknown HTML code, a script tag, showing up in the titles linking to ur.php, an obvious problem, so I began my investigation.
The first task was to find information about other sites which had also been attacked with the same HTML injection. A quick web search turned up the so-called LizaMoon attack which has been showing itself around the internet since at least autumn of 2010. LizaMoon has impacted well over 1 million different websites by inserting malicious code.
The next task was to determine a way to find all variances of the injected code. Besides looking for the URLs that had been known to be inserted (listed on many websites), I wanted to determine what other variants might be hidden in the good data of the database. To do this I compiled a list of strings that are not likely to be widespread in that database, including:
.js, php, script, .pl, .info, cfm, .inc
In order to search through all the tables and columns, I used the SQL script found here:
I modified it to include text and ntext columns and then started searching.
Even though the SQL script was finding what I wanted, it still took time to look at some data by hand to determine exactly what to remove. I made a backup of the database before I updated anything, always a good idea before a mass update, and then started removing the injected code. Here are the strings I ended up removing:
<a style=”display: none;” href=”hXXp://bookavio.cXm”>book</a>
(“http” replaced with “hXXP” and “.com” replaced with “.cXm” for purposes of this posting only.)
There were ten variations of bookXXXX.com that I found in the data (where XXXX was always a set of 4 other letters). As you can see, besides the script tag, all of the links contained the word “book” and the “display:none” CSS. So that knowledge helped me find additional variants.
Once I started seeing the injected code snippets, I needed to get rid of them. I found a great global search and replace that I also modified for my needs to use text and ntext columns.
Here is a list of additional strings I searched for based on what I had found from the known injections:
Since injection code viruses like LizaMoon, and others like it are not likely to disappear anytime soon, it is important to know how to walk through the steps of deleting such code from databases. There is no need to panic if you are faced with one of these SQL injections. With the right information it can be no more than an irritation.
If you have 5000 products in database, you may like to show them in pages on the screen. It’s a common request to only retrieve the records on a certain page. This query must be quick and efficient if we are talking about millions of records in a table for an AJAX control.
In Oracle, we can do this:
SELECT * from
SELECT *, ROWNUM as rownumber from Product order by name
where rownumber between (PageNbr - 1)*PageSize + 1 and PageNbr*PageSize
As you can see, this query needs to fetch all rows first then narrows down the result. A more efficient query is:
from ( select a.*, rownum rnum
from ( select * from product order by name) a
where rownum <= PageNbr*PageSize)
where rnum >= (PageNbr - 1)*PageSize + 1
Oracle will optimize this query, so it is not as resource intensive as the first one.
In SQL Server 2005, we can do similar query:
WITH Temp AS
SELECT row_number() OVER (ORDER BY [name]) AS rowNum, * from Product
SELECT * FROM Temp
WHERE rowNum between (@PageNbr - 1) * @PageSize + 1 and @PageNbr * @PageSize
ORDER BY [name]
The query had been optimized in SQL Server as well.