How can I keep someone from executing malicious SQL code on my website?
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.
Become a Patron of Ask Leo! and go ad-free!
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
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
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
has a good overview article:
SQL Injection Attacks – Are You Safe?.