A couple of days ago, I put up a post about the SANS/CWE list of 2009's Top 25 Most Dangerous Programming Errors, and noted I would cover some of the items individually.

Today, I will briefly cover a couple that are somewhat related, in regards to the title of this entry. What I hope to get across is this; When you release an application (desktop or Web/Intranet), a programmer must always remember that malicious users will get access to the client side code. As a result, they can - and will - exploit whatever holes have been left. My goal with this article is to add something to the 'blogosphere that describes why these issues are important for programmers to consider at all times. If you are not a programmer, and never intend to be, perhaps this will be rather boring! Heck - even if you are, it may still be boring.

So the items which apply here are; Improper Input Validation, Improper Encoding or Escaping of Output, Failure to Preserve SQL Query Structure (aka 'SQL Injection') and Error Message Information Leak. All of these errors create a gold mine for people who want to use your hard work for their own nefarious purposes. These are very common and easily-made errors by inexperienced programmers, especially because these errors can be made in relatively simple web site code, where less-experienced programmers might more often find themselves creating something that will be available to be accessed by those seeking 'low-hanging fruit' to exploit. A common misconception is, "I'm not exactly creating another Amazon.com or a national bank's online-banking system; Who is going to try to hack my web site?" The answer to that question; Anyone looking for a group of users to exploit, a web server to send e-mail spam from, or a free place to host illegal content.

Brief Definitions

These are purposefully brief and simplistic. Anyone interested can read the links above to the CWE information on the errors.

Improper Input Validation

A program should check that data entered is of the correct data type, and is within appropriate parameters. For example; on a shopping cart web site, entering anything but 0 or a positive number should give you an appropriate error message for the quantity you want of an item. Malicious or mischievous input should be disallowed, with an appropriate error message.

Improper Encoding or Escaping of Output

Coupled with the above error, this can lead to lots of Bad Things™. When user input will at some point in turn be output to some other system, it's vital to be sure that the information is encoded/escaped properly, to avoid unintended script code from being executed, for example. As an example; in a form for entering a 'blog comment, it may be fine to enter > and < signs, because you may need them as literal text (as I just did there). However, if those are not escaped properly, they will be seen by the eventual reader's web browser as HTML code - not literal text. Proper escaping is why you just saw the literal > and < signs right there.

SQL Injection

SQL (Structured Query Langauge) is the language used by all the big relational database products, to allow programs to insert, update, and retrieve information. Just like all high-level programming languages, SQL is at the root a plain-text language, with other languages used to 'build' SQL queries to send to the database. If the application does not properly validate input, and escape/encode the output that it's sending to SQL, a malicious user can caused the database to do anything they want.

Error Message Information Leak

When an error occurs, it is important for a program to give the user an error message that is helpful and informative to them, while not revealing dangerous detail. Some web sites, for example, actually reveal back-end database usernames, IP addresses and even passwords in error messages that any normal user might see. Other error information leaks are not so obviously harmful, but a cracker trying to take advantage of an SQL Injection error can make great use of error messages that reveal far too much information. Error-handling code should report technical details securely to the administrators/developers of a program, but then just give a 'friendly' error message to the user.

The items I have mentioned here are included together because all of them are often used together for common web application exploits today. A web site does not properly validate input, then does not properly escape the output that it sends to SQL-enabled code, which itself is not properly handling input parameters, and then finally, error messages are revealing too much information. The process of discovering how to take advantage of errors like these often involved some trial-and-error, and error messages with technical detail included just make that process almost trivial in its ease.

A Personal Example

As I noted in my previous post, I am not immune to making coding errors. If I claimed ever to be, you would be correct to call me a liar! Like most everyone else, I learned from experience - and much too often, the hard way.

A very, very simply SQL query might look like this: SELECT * FROM ProductsTable WHERE ProductDescription = 'widget'; That SQL code would search the ProductsTable for all information (the *) on items whose ProductDescription is widget. This is an unrealistic search for many reasons, but represents correct SQL formatting. Of particular note is how the literal text you are searching for is noted: by the single quote marks. Only text within those quotes is considered part of the WHERE condition. Anything outside of it would be considered to be part of the SQL code.

A web site that uses such a search might present you with a text box to enter your search term widget, and then it would insert the word inside the single quotes at the appropriate place in the SQL query it builds. SQL queries that expect a numeric search term also have a similar problem; single quotes should not exist at all, and further commands can be included fairly easily, if proper protections are not in place.

But, what would happen if you were searching for a product whose description was, say, Andrew's widget. The resulting query would be: SELECT * FROM ProductsTable WHERE ProductDescription = 'Andrew's widget';. Note the single quote (apostrophe) in the 'middle' of the text. SQL does not know that is the 'middle' of the text. It things that Andrew is the entire search term, and that s widget' is supposed to be the rest of the SQL code. If you guessed that this particular thing would result in an SQL syntax error, you would be correct. Such an error, if it were to occur, should never be reported back to an end user, but privately reported to the developer, with a 'user-friendly' error message sent to the user.

However, such an error should not happen in the first place. SQL has a way to deal with those single quotes, so that you can actually search for Andrew's widget. It is called an 'escape' sequence, which lets SQL know that, "this single quote is meant as part of the literal search term, and not as a delimiter of the end of the term". That escape is to precede the single quote by another one. Whenever SQL encounters two single quotes back-to-back, it knows that you mean "put a literal single quote here". So, the 'correct' query should be this: SELECT * FROM ProductsTable WHERE ProductDescription = 'Andrew''s widget';.

How it Becomes Bad

If not validation, escaping or other protections are used, imagine if I try to search for a 'product' with this name: widget'; DROP TABLE ProductsTable; --. Obviously, no such product exists. However, without escaping, validation, etc... the resultant SQL sent to the database would be this: SELECT * FROM ProductsTable WHERE ProductDescription = 'widget'; DROP TABLE ProductsTable; --';

A couple notes about SQL syntax. A semi-colon (;) means "this is the end of this command. An additional command may follow". The double dash (--) means, "ignore everything after this"... it's a 'comment'. The end result of the 'search term' here would be first, that the database will search for a product with a description of 'widget', and return any results it may have. Then it will immediately DROP (ie. delete) the entire ProductsTable, meaning the web site will no longer have any products on it at all.

This would be a "denial of service" type of exploit, rendering the web site useless until it can be restored from a backup. However, the "DROP TABLE..." code above could be replaced with something else. Perhaps, for example, adding 'products' to the database that contain links to malicious code, contain a political message, link to child porn or pirated software downloads, etc. Also, the cracker could silently add themselves a 'legitimate' logon to the database so they could maintain quiet control until they are ready to take some action.

Ways This Can be Stopped

Long ago (14 years - a lifetime in World Wide Web years!), I created a system that allowed people involved in a small online community I ran to select a user name to log in to a web site that let them maintain profile information so that others could see what they were doing, and how to contact them. Their user name could contain any characters (no validation), and the SQL query was built without any additional code to escape single quotes. Luckily, error messages were handled properly. However, the first time someone entered their username with a single quote, they reported they were not able to log on; The SQL query that was built was always invalid syntax, so it always failed. It was as if they did not exist in the system. Had my code not protected the error message reported, a hacker seeing the resulting error message would have been able to figure out how to do exploits like those I noted above.

At the time, I did not realize the import of this error. I saw it at the time only as a usage impediment, not as the security fault it really was. However, once I started to look up ways to avoid this, I began to realize what was at stake.

There are multiple things that can be done to stop this type of attack.

Input Validation

One option might have been to use input validation to prevent single quotes from both being used in user name and in queries for names. If a single quote existed in either, the web site would reject the input, and tell the user. It would have been important to have prevented single quotes in both instances. Also, if people could log in with a user number instead of a user name, input validation could have been used to assure that what the user entered only contained numbers - not letters or punctuation.

I could not prevent single quotes, as it was actually logical in our situation that people would use single quotes as user names. Of course, someone had also already created such a user name, and I did not want to inconvenience that person more. However, I could validate input for other invalid types of characters, such as the double-dash (--) and semi-colon (;), and other control or escape SQL characters.

Output Escape

I could also have used the available code to automatically 'escape' the single quotes when passing them in to a query, turning them into double-quotes, making them work as intended. In fact, this is what I did in this particular situation. In all cases where I had code that 'built' queries in this manner, I had standard functions I called which searched for special SQL characters, and escaped them properly. However, I no longer use this method, because instead there are...

Parameterized Queries

Most programming languages/environments that contain database access code do now, and have for quite a while contained the capability of performing type-safe parameterized queries. Using object-oriented programming practices, special queries are built where the type of data of each parameter is defined ahead of time, and built-in database code allows the programmer to set the input information as properties to an object instance. The object then uses the parameter information it has to do all of the validation and escaping necessary. Trying to set any string to a numeric parameter value returns an error, and never executes the SQL. Comments, single quotes, and other SQL control characters are stripped or escaped out of text parameters, so they are safe to use.

Today, essentially every programming language and environment with database access functionality contains some form of parameterized query mechanism, and they are generally not terribly difficult to learn how to use, and then actually use.

Error Message Sanitation

Even when using all of the appropriate mitigating factors above, error messages still need to be sanitized, so users only get a friendly, "We're sorry. Something bad happened, and the administrator has been notified" with no technical details.

So, What's the Problem?

The problem with the solutions here is that very often, novice (or even not-so-novice) programmers never get to the point where they learn about the programming patterns noted here. The SQL language is usually learned like most things, with the basics first. That means that programmers learn to 'build' SQL queries by 'hand' first, all the while seeing 'simple' examples of using them within the programming language of their choice. Parameterized queries can not truly be understood until you understand the basics going on underneath. But often, once someone knows how to 'do something', they stop there.

Also, even once they learn it, it does take a few extra steps to use parameterized queries or SQL escape functions than it does to simply build SQL by hand. In a rush, or lacking complete understanding of the dangers, some will simply forgo the required steps.

Often, an apparently talented and knowledgable programmer will just breeze past something so basic, and skip it entirely. Perhaps they begin building an application with easier methods, intending to add security-related code later. Simply; That tends not to happen.

You've Actually Read This Far?

If so, I'm surprised! I tried to make this simple, but it's hard to do, while accomplishing my original goal. If you read it, I hope it was useful. I welcome you to contact me if you'd like more information.