SQL Injection
Wednesday, May 16, 2007The 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.
Subscribe to:
Post Comments
(
Atom
)
I write code, sometimes from a beach.
No comments :
Post a Comment