How can I keep someone from executing malicious SQL code on my website?

Home » Web » Web Site Management

Summary: SQL is a powerful language for accessing databases used by many web sites. Unfortuntely it's easy for hackers to exploit if used carelessly.

Many sites use variants of SQL to manage the databases that are integral to their sites. Unfortunately there are common coding oversights that can lead to a specific type of vulnerability commonly called "SQL Injection" which can allow a malicious user to very easily execute arbitrary SQL code. In case that doesn't worry you, it should - a database subject to this vulnerability could possibly be viewed, altered, or even deleted. In a worst case scenario, the server hosting the database can also be compromised.

•

To over-simplify how SQL Injection works, consider a form where you're asking your site visitor for their user name. You then use a fairly simple SQL statement to look up their entry:

SELECT * FROM dbUsers WHERE UserName = textEntered

textEntered in this case is a variable containing whatever the user entered as their user name. Seems pretty simple, right?

Unfortunately it's TOO simple.

If the user enters this string, for example:

'' OR 1=1

and we replace the variable textEntered with the text we just entered, our SQL statement looks like this:

SELECT * FROM dbUsers WHERE UserName = '' OR 1=1

which will return all fields of all records from the database dbUsers. The result? Any random person can see everything in the database, including any private information stored.

Because most variants of SQL allow for multiple statements on a line, and many support complex and powerful operations including deleting the database or shutting down the server, you can imagine the destructive possibilities.

The problem is that the design of my hypothetical page allows unvalidated user-entered data to be executed as SQL code. The general solution is to ALWAYS validate or otherwise cleanse the incoming data. For example:

• Escape or remove quotes to prevent the example I used above from resulting in valid SQL syntax.
• Look for and disallow certain keywords or character sequences - keywords like "SELECT" or sequences such as your SQL uses to delimit multiple statements on a single line.
 

You can further harden yourself against this type of attack by doing more aggressive field-specific validation prior to passing user data to SQL. For example usernames probably never have spaces, so reject any that do. Last names typically are alphabetic, though they might have spaces or apostrophes - limit them to that. The more you can enforce the already allowable restrictions on your data, the safer you'll be.

Finally, when your SQL supports it, make sure that the account that these SQL queries are being executed as does NOT have "sa" or superuser access. That's asking for trouble. Instead, create a SQL user account specifically to process web entries and restrict it to only those permissions it needs to perform the job your web page requires.

I've only touched on the problem and examples here. For slightly deeper discussion with more concrete examples of both exploits and solutions, sitepoint.com has a good overview article: SQL Injection Attacks - Are You Safe?.

Article C1839 - September 7, 2003