SQL Injection Penetration Testing Tools

Thursday, May 24, 2007
I've already walked through the process of mitigating SQL Injection vulnerabilities in code, but even in the most skilled hands with the best code reviewers, you're going to miss something. We're aiming for a system that is as difficult to crack as possible. You can never achieve total security (short of disconnecting the network card and melting all of the parts into a big giant blob, but then I'm sure there's someone who will argue that they can hack the data from it!)

The final rule missing from that post was to perform penetration testing. Doing so can be difficult. If you have a security team that is skilled in the art, it's probably best to leave the real pen testing to them. If you don't, or if you just want to preliminarily test before passing it off to your security team, there are several tools that automate pen testing.

Disclaimer: Most security tools can be used for good and evil. Running these tools on software you've written, installed on your own servers, or on servers that you are responsible for securing is "good" -- assuming you're allowed to even use these tools in your organization (obvious, right?). Running this on your online banking site to see if they're "up to spec" will hopefully get you a visit by the feds, especially if you decide to take advantage of the vulnerabilities it finds. In other words, don't do it. And if you do, you deserve the consequences of your actions.

For those who don't work in security, the question is often asked: "Why should these tools even be available? They're gold in the hands of a malicious user. " Tow which the inevitable conclusion is "They should be banned!" This thinking is referred to as Security through Obscurity. It simply doesn't work. You can't ban a tool because secrets are not kept well on the internet. Beyond that, these tools are often more useful to a pen tester than a black hat.
Bear in mind that these tools all have limitations since they're based on pre-programmed attack methods. A truly creative security engineer or black hat might find another method that your tool does not check for.

That said, I ran across this link on security-hacks.com entitled the Top 15 free SQL Injection Scanners. Some are designed for specific databases, some are general purpose. You'll find that more than one tool will prove useful and since they're free, you're out nothing if you give them each a shot. Of course, use them at your own risk. Never test live production servers unless you're operating within whatever maintenance window you have setup and have full (and tested) backups. You have no idea what these tools might actually do.

In addition, as with all penetration testing software, make sure you do a lot of additional research on the tool itself. You wouldn't buy a gun from a "guy on the street" (maybe you would? I wouldn't). If it's open-source, that's a good start. Check the community behind it and if you're skilled enough, review the source code yourself. Always consult Google. If it's commercial, avoid it unless you know the brand very well. Look for any hints of added malware. Unfortunately, you may find some security sites rank the tools as malware because they work as advertised so make sure you research well. If it's only fault is that it's designed to hack a web application, that's fine. Remember, we're trying to break into our applications so we can plug the holes and prevent someone else from discovering them. Nobody wants to get that frantic cell phone call at 3:00AM on a Saturday.

GET vs. POST - Save your users!

Friday, May 18, 2007
This is one thing I've seen gotten so wrong so often and resulted, I felt compelled to put some of my thoughts on the subject down here.

First, a little explanation. GET and POST are commands issued to a web server. Your browser uses the commands to request a web page, and provide information to a web application.

In the case of GET, this is often done via a specially formatted query string. The text and information of this query string is visible to the user assuming your application resides in a normal browser window and not one where the address has been hidden from view.
GET can be used as a method for sending form data, and it is used often in the case of Search Engines. Simply search for "dog" on Google and you'll find this URL:


GET is convenient. As you can see from the above URL, I could just as easily encode a link like this and you'd be sent away from my blog to a search about a dog. In fact every link that goes to a web page is a GET operation, not just forms like Google's Search form that use the method GET.

POST is a method that is primarily designed for sending form data (it can be used in many other ways, but we'll skip those for now). POST data does not appear in the address bar like GET data. It also cannot easily be encoded into a link.

So which should I use and when?

The answer is simple, but the solution is sometimes painful. GET should be used only when the operation it is performing is considered safe. Safe means an operation that is looking up data. I've even heard it said an operation that "asks a question", or "navigates somewhere". In the case of a search engine, you're asking a question. In the case of a link, you're navigating somewhere.

What's more important is knowing when GET should not be used.

Rule #1: Never use GET in a login form or to send sensitive data
I know that this should be obvious, but I've seen it before. For those who are still scratching their heads. When you use GET in a login form, the user ID and password of the logged in user will appear in the address bar. This is called exposure. :-)
I've seen plenty of older e-commerce sites that send credit card data this way. This is "bad" on more levels than I can get into here. Just don't do it.

Rule #2: Don't use GET for any operation that modifies something.
This is where most people fail. I've logged into my banking site, and I'm looking at my account. Next to my account is a link that says "Delete". It links to the following URL:


My, how convenient is that to code? No form necessary, no javascript, no buttons, no hidden values, just an anchor reference. That's also leaner HTML.

Technically it works. Since I'm logged in and have an authenticated, encrypted session, it's thought to be secure (assuming your account number isn't in that querystring, it might actually be secure).

Unfortunately, some browser extensions and some alternative browsers do something called "Look Ahead". And it does exactly what it sounds like. It looks at the page you're on, and pre-loads all of the links on the page, figuring that you're probably going to click on one of them. The idea is that if the browser pre-loads all of the links, and you click one, it'll already be in your cache and navigation will run lightning fast. Unfortunately, the Look Ahead doesn't know anything about what the link actually does.

If mynicebank.com encoded the delete link that way, and I was running FasterFox, my bank account might be gone lightning fast even though I never clicked the link.

Rule #3: Never use GET to send large amounts of data
This is less of a rule and more of a suggestion. Some older web servers and browsers limited the length of a GET request. Most, nowadays, don't. I've even seen some clever hacks that use TinyURL to store entire files.

While writing this, I discovered a great resource over at the w3c. If you're still curious, check it out. Those guys are a lot smarter than me and probably came up with something I missed.

SQL Injection

Wednesday, May 16, 2007
Editors Note: I wanted to start off with a bit about how I hate needles, but I don't actually have a fear of needles. It would have probably sounded as dumb as the sentence I've just written about why I left the pun out of this entry. I'll stop now . . .

The Injection you really should fear: The SQL Injection.
(sorry, couldn't resist)

To understand why, you must understand what SQL is. SQL stands for Structured Query Language. An SQL Server is a database server that implements some form of this language. When people think of databases, they tend to think of them as simple storage and retrieval mechanisms. Non-programmers do not realize that power of the database resides in the language used to query or manipulate that data.

The fact that interaction with the database is done via a separate language is where the problem resides. You have one set of code (your web language, php, C#, java) writing code in another language (SQL).
This is how a simple web app might look:

User fills out form element, clicks submit which sends the data to a page to display results.
That page connects to the database, and inserts the search string into a query command, gets the data from the database and displays the results.

The above italics is where injection can occur. In this case, developers often forget that the query command is interpreted code. A common (and wrong) way of doing this is to create a string with the code and add the data from the form field to it, in this manner:

Command = "SELECT Name, ID, Description FROM Products_Table WHERE Name LIKE '%" + users_search_request + "%'";

That's the language mix. Command and users_search_request come from C#, the language the web page was written in, whereas the portion between the quotes is intended for interpretation by the SQL server. This works but it is insecure.

If the user types Dog Collars, the resulting command is SELECT Name, ID, Description FROM Products_Table WHERE Name LIKE '%Dog Collars%', and everything is fine. If the user, instead, types '; DROP TABLE Products_Table, in that same search box, the resulting command is SELECT Name, ID, Description FROM Products_Table WHERE Name LIKE '%'; DROP TABLE Products_Table. The command "DROP TABLE Products_Table" has now been executed, and Products_Table gone.
Of course, SQL is very powerful, so far more than table deletion can occur. And today's malicious users are less concerned about defacing, or destroying data, and more concerned about stealing it for fraudulent uses.

Like all things in security, there isn't one magic solution to this problem. Any one of these will reduce your exposure. Using all of them will nearly eliminate it.

Rule #1: Use Database IDs with limited rights. Limited Rights = Limited Exposure
If every piece of newly written code I've ever looked at is any indication, this is almost always skipped. Often developers start the coding process using a generic ID, figuring it'll save time during the development process. They'll get fewer errors due to rights issues and can focus on debugging the newly written code. Just before release, they can switch the accounts to use the limited rights account. Of course, that last step is usually missed, and the service goes production using that same near-administrator account.
In the above example, it is appropriate to use an ID that only has the ability to read the values from that table, and only those fields if necessary. I'm a big fan of using more than one ID to do different operations. IDs that need update ability should only be able to update the table(s). Be as granular as your SQL server will allow and ensure that the account used can do only what it needs to do.
Read operations should occur with a read-only ID that is limited to the tables it needs access to.
The reason this is Rule #1 is that it's one of the only things you can do at the database to protect yourself from bad code.
If your organization has a database management team that is separate from your web developers, they should apply this rule.

Rule #2: Used Built In Libraries for Querying
The easiest way to prevent code injection is to eliminate the manual writing of SQL code. Most modern languages used in web development expose methods to let you query without writing a query string. Even if you have to write a query string, you can usually do so using parameters, rather than string concatenation (as we did in the above example). The values for the parameters can then be defined using the language, where they are properly sanitized (assuming the language itself isn't plagued with security holes).
This is often seen by developers as the silver bullet that is impervious to SQL Injection. Unfortunately, languages can have security holes too. So you can't simply rely on this being your savior every time.

Rule #3: Never trust that a user's input is "safe"
Rule #2 should sanitize any data sent to the database, so if you have the option of using built in libraries, do it. You've already completed Rule #3. Don't re-sanitize, or you'll probably break your application. On the other hand, if you have to do concatenation to query, you must sanitize the information submitted by the user.
Most languages used for web design include built-in methods to sanitize input. At a minimum, the above should have been sanitized to "escape" the apostrophe. That would effectively keep the apostrophe from being used to close the quotes and the command.

Rule #4: Stop displaying error messages to your users.
Some clarification is in order here. It's OK to deliver an error to the user when an error occurs. It's not ok to allow your web server to deliver it's debug information with that error. This is usually not the default setting, but we developers certainly like it for debugging.
A common method used by malicious users is to inspect your system by intentionally sending malformed input with the hopes of getting an error.
In the above example, I could have written invalid SQL, in which case I would have likely received the line of code that it failed on, and some or all of the text of the SQL command. I could use this to further attack the database. With that error message, I now know the table name and the column names that the receiving page expects. I could then try attacking other common table names by using something like '; SELECT Name, 1 as ID, Password As Description FROM users, and continued "guessing" the table names until I found one that fits.

Optional: Use Stored Procedures if the SQL server you're using supports it
There's differing opinions on this, but when coupled with Rule #1, it can be very powerful. A stored procedure will not prevent the above problem, but if the ID only has rights to execute that stored procedure and no rights to do anything else, your malicious user is effectively stopped. Stored procedures can be designed to enforce business rules, as well, but they can make managing an application more difficult since the code for that application is now effectively spread between the database and the application.
I'm, personally, a fan of stored procedures as long as they're managed properly.

Optional: Automatically IP Ban suspect users
This is a weak protection method, but if the risk is high enough, it may be worth doing. Look for patterns in user input like '; DROP TABLE, and update your web servers security rules to ban the IP address. It won't stop them, but it will slow them down.

Best Practices for Helping Code Review
Documentation is boring, I know. As a wise developer said to me (today actually), you can spend time writing code, or you can spend time writing about code. This how most developers feel. Documentation is very important, but it always feels like it's time that could be better spent solving problems.
When documenting code, blocks that touch databases (or other areas where your code is "writing code") should be clearly commented. This allows you to do a detailed review and focus on the areas that are the most likely to be attacked.