The worst possible way to guard against SQL injections

I shouldn’t need to stress the importance of sanitising user input on web forms. I also shouldn’t need to stress this importance of government websites being secure.

I also shouldn’t need to stress the insecurity of client-side code.

However, it seems Cadw (“the historic environment service of the Welsh Assembly Government”) seems to be stuck a bit too far in the past before people started exploiting websites for fun or profit, as I recently discovered from this tweet:

Now, don’t get me wrong – JavaScript is a really useful way to make websites look better and provide cool interactive experiences.

However, all too often I see JavaScript being used in one or both of the worst possible uses for it:

  1. Security
  2. Adding functionality

Both of these points obviously need exploring further.

Adding functionality

JavaScript is commonly used to add functionality to websites without problems. For example, JavaScript is used to provide most editing toolbars on web-based editors like Google Mail, Wikipedia, and WordPress. This functionality is “extra”, not critical to the operation of the site – you can survive and use the site properly even on a non-JavaScript capable browser (these days, it’s mainly screen readers which fall into this category).

However, when you start adding functionality which doesn’t have a non-JS fallback, such as Facebook (just try using it with JavaScript disabled in your browser), the site becomes completely unusable to some people – a huge discrimination against those who are not as able as others to browse the web (for example, those with screen readers). Therefore, using JavaScript to add critical functionality is a very bad idea if you want your site to be open to all and actually usable.

Security

More importantly, JavaScript code is downloaded to the client computer, and executed there.

The client has the option to execute it or not, or even to modify the code first then execute it (with a little know-how).

This means any security checks you put into the page with JavaScript CANNOT be relied upon to actually work.

As the source code of the above site is sanitising user input with JavaScript in a rather poor way, there are several potential ways around this. Let’s take a look at their web site’s source code:

Several things immediately spring to mind:

  1. SQL keywords and syntax in a “bad list”:
    "select", "drop", ";", "--",
     "insert", "delete", "update", "char(", "`", "varchar"
  2. Weird stuff, possibly passwords or other language constructs?
    "/", ":", "?", "|", "declare", "convert", "@@",
     "2D2D", "4040", "00400040", "[", "]"
  3. “xp_” – perhaps a computer name prefix for systems running Windows XP?

Addressing the points in reverse order, a quick bit of poking (just a standard HTTP HEAD request!) the web server reveals:

Trying 62.254.243.215...
Connected to www.cadw.wales.gov.uk.
Escape character is '^]'.
HEAD / HTTP/1.1
Host: www.cadw.wales.gov.uk

HTTP/1.1 200 OK
Date: Wed, 30 Mar 2011 04:29:53 GMT
Server: Microsoft-IIS/6.0
X-Powered-By: ASP.NET
Content-Length: 20796
Content-Type: text/html; Charset=ISO8859-1
Set-Cookie: ASPSESSIONID......GDEM; path=/
Cache-control: private

(I’ve removed the actual cookie set :P)

Ooh look! We’re running IIS 6.0 as the web server. This gives is two likely suspects for the operating system of the server: Windows Server 2003 (aka WinXP server edition), or Windows XP Professional x64 Edition. Basically, XP.

With only talking to their web server, I’ve now got a likely prefix on machine names – chances are the names are just numbered after that, and given their network is running Windows servers, it’s likely to be on a Windows domain. That simple knowledge gives me the hostname of a large number of workstations: xp_1.cadw.wales.gov.uk (or maybe xp_01.cadw.wales.gov.uk or xp_001.cadw.wales.gov.uk, or perhaps even xp_01.wales.gov.uk etc). It would be trivial to find out which of these naming schemes existed – probably by just pinging their DNS server.


At this point, this information is getting scary. I’d like to remind my readers that everything I have done so far, I have documented here. I have done nothing else. I’d also like to remind folks that this is a government computer system, and any vulnerabilities I find I am not going to touch, as I don’t have permission to do so. Information I have found so far is either public information that they may or may not have inadvertently published (such as POTENTIAL machine names), or information that would be retrieved by software such as web browsers every time you loaded the site. Getting that information manually by simulating a (poor and slow) browser just happens to be easier than messing around inside my browser (chrome) config at the moment (for firefox users, the extension Firebug will nicely show this information for you). If you choose to use the information I have published here, then you do so at your own risk. My aim in this is to point out bad security practice in the hope that others will heed the warnings and not make the same mistakes.

The weird stuff which makes up my second point could be anything, a bit of googling might tell you why they’re dangerous, or explicitly prevented.

Lastly, the first point. Let’s take a look at the main items from the SQL-specific part of their “naughty words”:

"select", "drop", ";", "--", "insert", "delete", "update", "`"

(I’m going to quickly point out they convert the words to lowercase to check them against the list.)

So, we can’t retrieve or modify the data. We can’t delete data from the table, but truncate table isn’t restricted. We can’t use comments. We CAN use quotes, but not the table-style backtick quotes. We can’t drop tables or columns, but we could add new tables and columns if we wanted.

The fact that only the backtick (`) is in the list could be an indication of a style of quoting, which we could make use of.

Of course, this is all before we make the obvious suggestion: “IT’S JAVASCRIPT! Turn it off!”.

Oops, did we just turn off all protection against SQL injection attacks on your database for ourselves, with a simple checkbox in the browser settings? How inconvenient of me!

Usability for tech noobs

One last point just to round off the whole thing, one on usability.

Let’s say I want to search their site (using their tiny search box) for “how do I select a place to visit?”, my search query gets cut off at “…vis”. Assuming the user is smart enough to realise the computer doesn’t like long searches, they might rephrase to “how do I select a place?”.

This is the error message I get:

Would you understand it if you were a tech noob? I doubt it. After all, what’s wrong with “select”?

Conclusion

YOU’RE DOING IT WRONG.

xkcd.com - Voting Machines

CADW: If you ever see this post, fire your web developer, and take your site offline until it can be fixed by someone who’s actually competent.

Please, please, PLEASE let this be a lesson to other people how sanitising user input is a Good Thing™.

xkcd.com - Exploits of a Mom

I only hope that the Government in Westminster hasn’t made the same mistake, or this could be very costly… it appears someone has filled in the census form in a rather interesting way…