Technology in terms you understand. Sign up for the Confident Computing newsletter for weekly solutions to make your life easier. Click here and get The Ask Leo! Guide to Staying Safe on the Internet — FREE Edition as my thank you for subscribing!

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

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
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?.

Subscribe to Confident Computing! Tech problem solving & safety tips & a weekly confidence boost in your inbox every week.

I'll see you there!

10 Reasons Your Computer is Slow

Slow Computer?

Speed up with my special report: 10 Reasons Your Computer is Slow, now updated for Windows 10.

NOW: name your own price! You decide how much to pay -- and yes, that means you can get this report completely free if you so choose. Get your copy now!

1 thought on “How can I keep someone from executing malicious SQL code on my website?”

  1. As a budding web programmer, I am sure some instructor along the way would have mentioned this. Rest assured I will be using JavaScript to validate data entered by site visitors.

    Reply

Leave a reply:

Before commenting please:

  • Read the article.
  • Comment on the article.
  • No personal information.
  • No spam.

Comments violating those rules will be removed. Comments that don't add value will be removed, including off-topic or content-free comments, or comments that look even a little bit like spam. All comments containing links and certain keywords will be moderated before publication.

I want comments to be valuable for everyone, including those who come later and take the time to read.