SQL injection attack on a PostgreSQL database (t_jiaozhu)

For the first time I have been in a position to realize that a machine was attacked from an outside source in a production enviroment. A web server running Apache 2 and PostgreSQL was successfully attacked using a SQL injection vulnerability. I first noticed there was a new table in one of our PostgreSQL databases named 't_jiaozhu'.

public t_jiaozhu table postgres

The table wasn't something that myself or our developer had created so I immediately went into WTF mode. First I googled for the term 't_jiazhu' and found that there was only one English result that mentioned SQL injection attacks with the previously mentioned table name. At this point we searched the PostgreSQL log files but didn't turn up much but with the advice of a security guru I know (John Sawyer) we checked out the Apache web server log files and found the attack.

# grep t_jiaozhu *fred-access_log:219.153.131.99 - - [25/Mar/2007:11:59:32 -0400] "HEAD /showemploymentopportunity.php?id=38;create%20table%20t_jiaozhu(jiaozhu%20varchar(200)) HTTP/1.1" 200 - "-" "Mozilla/3.0 (compatible; Indy Library)"

Sawyer also came up with a possibility that the IP in which the attack came from may have been a bot using an IDS.

"After the table was created, there were several hits from that IP that had the following user agent "Mozilla/3.0 (compatible; Indy Library)". A little digging shows that it might be a Chinese spambot."

Our developer quickly discovered that we weren't checking varibles that were being passed. A quick addition of code fixed the problem.

if (!is_numeric($id))
$id = 0;

Running UAC and some other tricks to keep your computer running smoothly

Some of these may be obvious but much of the time are overlooked.



Most users that I know run Microsoft products. A few of you may benefit from some basic tips to keep your computer out of BestBuy or your local computer vendor for repairs. The first and probably most important is also the most difficult to get people to abide by. Use UAC (user access controls). By default Windows XP uses the administrator account which is convenient when an operating system is first loaded but most users load all of their programs on a PC in just a short time. After you get everything installed run as a 'user' account and not an administrative context. This will prevent most spy ware and viruses from trashing your system. Even if you accidentally download some malware it will most likely at the worst trash the user profile but not the system which is a pretty easy fix.



Vista by default has UAC turned on. This is annoying at first but is a positive action by Microsoft in order to cut down on end-users trashing their systems. UAC may be disabled but I wouldn't recommend it. A majority of computers that become compromised with spy ware is because malware or viruses entered through a profile that had administrative privileges and then self installed.



Antivirus must be installed. Most computers I come across don't have it installed or it's so out of date it might as well not be installed. It's a small fee to pay or even free to avoid the headache of infecting your computer or worse other computers.



Scripting attacks may be prevented by staying out of crappy sites. One problem is some popular sites still seem to host ads from vendors that are known to install malware. Using a registry based block lists is a quick and free way to avoid these pitfalls.



Peer2Peer software is another way to trash a system. Installing poorly written software for the purpose of downloading music and whatnot is a pretty sure fire way to hose a system. While in college most of the computers I have seen that run poorly are because a Napster type of software was installed and some of the files downloaded from the network were virus ridden. The peer sharing software themselves sometimes have ad-ware built in for the purpose of bombarding your computer with trash. So the alternative sucks but pay for it using iTunes or something along those lines.



I don't want to even start with email. Basically if it looks too good to be true then it probably is. Don't click on the links or download the images from it, just delete it or report it as spam.