SQL Injection Attack Precautions

I try to remain perceptive and learn from my own mistakes as well as the mistakes of others. I was reading an article on Dark Reading called "The SQL Injection Disconnection," a fluff article just for clicks that briefly mentions SQL Injection attacks and how hackers are talking about them to the same degree as DDoS attacks.

I was the Web Manager at CertMag.com for a couple of years and noticed a lot of different ways someone could harm a website with poorly written code. We had a forum written in Argentina, another forum product called vBulletin, and had even written our own custom forum at one time. When I took over the technical operations to my surprise there were five extra user accounts in the Microsoft SQL server and one database called "test" in Japanese that contained all sorts of content related to the adult video industry. Needless to say we had been "pwnd." Our server was serving who knew what and we were a sitting duck. Shortly before we switched to our upgraded vBulletin forum we were being injected every 3-4 hours with someone claiming prize.

When I went out to the server farm to my surprise there were no filters on the firewall at all to even try and prevent some of the attacks taking place; an IT oversight. Also the web server was on the DMZ rather than being filtered by our Enterprise Level anti-virus firewall. When I left this was all "fixed."

The problem with the disconnect in regard to SQL Injection isn't that people aren't aware of them or that people aren't taking notes. There is usually a disconnect in human communication. There is no one person who in most situations would ever cover each of the steps to make certain an SQL injection attack was preventable. More often than not the issue falls across several people who all have to do their part in order to make a nice "safe" system to prevent or mitigate SQL Injection attacks. I obtained a special insight into the issue by being lead developer, web manager, network administrator, and IT manager all over the course of about 6 months. It was an eye-opening mind-altering adventure that really made me come up with some very dark options someone could do to take down a server.

What is SQL?
Basically SQL stands for Server Query Language and is used more specifically in communications with database servers. When someone needs to insert into or pull information from a database they more than likely use some form of SQL.

So what is an SQL injection attack?
There are several varieties of SQL injection but they all usually involve someone finding an exploit in a system and loading something into the database. This might be a snippet of code that runs when the page is redrawn (quite common) or an SQL script that either rewrites all of the content in the database with something else or wipes out all of the content altogether. It may also add something to all of the content (eg. pharmaceutical links).

How does this happen?
The more frequent occurrences happen because some off-the-shelf (or open source) application being used was not patched. For example someone downloads and installs a copy of WordPress. A patch is released to fix a known issue with the software, but the person who is responsible for applying the patch does not. Someone then goes on the web and searches for WordPress websites and through trial and error discovers the unpatched site. They apply their code (more than likely downloaded off of the web somewhere) and then the page, site, or database is compromised. I use WordPress as an example, but this happens with almost all open source (public) content management systems at one point or another.

Who is involved or more importantly responsible for the failure?
It depends on the environment of the site being taken over. If the site is corporate, there are several roles that could be responsible for the downfall of the server.

1. Webmaster or IT person: If incorrect server permissions are set (meaning someone has read and write access through something like a search box) then the attacker could take over the website by installing a backdoor. Then they download the passwords for the database from the code they've exploited, now they can create their own.
2. Webmaster or IT person: If separate accounts for the web server application or if the server is executed under a Root account then this could compromise the physical box itself (IT would need to provide a new box or wipe and reinstall in the event of a root kit).
3. DBA, Webmaster, or Web Developer: If the web browsing user account being used to communicate with the database by regular web users has full or elevated database privileges then new tables could be created, existing tables deleted, all data destroyed, or rewritten.
4. Web Developer: If website forms do not filter or clean the inbound content before being inserted into the database the content can be compromised.
5. Web Designer or Web Developer: If the site uses a prebuilt script from someone else's site (open source or shared code) and the code is not inspected, it may contain backdoors which could allow code injection. An example for this might be someone using an AJAX filter to check incoming content before a client submits, but blindly trusting the content. AJAX would insert the content on the check and the site would then be infected.
6. Web Designer or Web Developer: Assuming people will always input proper expected information into a form is bad practice. If someone can inject Javascript into a site they can inject AJAX, backdoors, or worse.
7. CEO, Owner, or Board of Directors: If budget for building a website or maintaining an IT department is cut, low, or non-existent this can lead to poor programming and administrative performance when trying to complete the project on deadline. This can lead to poor planning which can also lead to bad code being written, faulty code being reused, or anyone cutting corners from IT all the way through the web designers.

So this is more of a systemic issue overall since there are several avenues that could lead to a server being exploited. The main issues start at the top in regard to deadlines and fall onto the people who set up the server, the user accounts, the database accounts, and the permissions on the server, the database, and the files for the website. Once this is passed over to the development team, planning and security strategies need to be implemented in order to create a site that functions and does not trust outside users ever.

Things to keep in mind.

1. If a person is able to inject code, but the account doesn't have the proper privileges then the code will not work.
2. If the code is written but restricted to a non-web-accessible directory, the end user cannot execute the code and the site will be a little "safer."
3. Not everyone who attacks your website will use your frontend code. They may not even use a browser. If you're a programmer and checking the validity of an insert, turn of JavaScript and see if you can exploit the site. (eg. If a phone number field for insertion into the database allows something other than a phone number it may throw an error showing your directory structure or bringing down your server or code briefly.)
4. People attack websites for various reasons to name a few: Web real estate (for serving content), prestige ("script kiddies"), competition (corporate or political), religious or idealist reasons (eg. Anti-American), and just because they're bored (hobbyists).
5. If a site is attacked, restoring the database doesn't remove the exploit, only the temporary blemish. If the site is not patched quickly the attackers are given time to experiment and take over more control of the server.

The best way to stop some of these attacks are for everyone involved to do their part completely. The following goes into more technical detail.

Steps to take to secure a server:

1. When the server is being installed or set up, the Web Server needs to have proper directory permissions assigned. There are several articles about this on the web.
2. When the database server is installed it should have its own user account.
   (This is usually the default for MySQL)
3. The administrative account(*NIX) for the web server should not be accessible from the web at all. (No Root SSH) I know it sounds like a no-brainer, but it happens.
4. In regard to MySQL, the administrative account for the database server should not be accessible from the web at all. (No web queries from Root or the main Admin account)
5. Ideally if the site has a backend (CMS) and a frontend then on the backend (which should be secured and NOT in a folder called "Admin" or "controlpanel") the site's interface should use an account that has the ability to select and insert... maybe even delete (not drop). No other permissions should be allowed. On the front-end of the site if there is some reason to insert (like statistical tracking) then THAT user needs to have Insert-Only access to that one specific table or special database. It should not be able to access any other accounts or databases.
6. If the site has no reason to insert from the frontend, then the frontend user needs select-only access.
7. When allowing the site to upload files into the database ALL content must be screened, filtered, and checked. For example just because someone uses a feature like mysql_real_escape_string in PHP, it doesn't mean that all content entered into the database is safe for display on the site. That function simply prevents someone from escaping SQL statements and concatenating their own statement to alter the database itself. They can still write a backdoor when the code is visible on the site again (redisplayed using PHP). Something like strip_tags or a language like Regular Expressions would need to be used to filter code on insertion AS WELL AS on execute.
8. The backend of the website should be as hardened or more-so than the frontend of the site. Many times developers will figure someone who is on the backend has been authenticated, but if someone compromises the system by leaving themselves logged in or by logging in from an unsecure location then the whole site could be destroyed.
9. Ideally no changes to the live website would happen in real time from an interface on the backend. There should be a staging site for maintaining back-ups, a higher level of security, also for code testing to make sure someone outside doesn't see underlying issues with the site while it is under development.
10. Everyone who touches the website and web server needs to be on the same page in regard to safety. Downtime costs money all the way to the top.

That's all for now.