Author's Opinion

The views in this column are those of the author and do not necessarily reflect the views of iTWire.

Have your say and comment below.

Thursday, 13 November 2008 18:42

A real-world web site crack before your eyes

15 years ago Dan Farmer wrote a program called SATAN designed to help sysadminis detect vulnerabilities in their networks. He was criticised because of the massive potential for malice if used by "the wrong people" and was fired by his employer, SGI. Now, I'm not in Farmer's league but I'm going to risk my reputation here and now to practically explain SQL injection by cracking two publicly available web sites.

Chances are you've heard of SQL; that's an internationally-recognised standard language used for creating, querying and manipulating databases, whether by Microsoft, Oracle, IBM, Sun Microsystems or any other vendor.

SQL instruction are fairly robust when a programmer or database admin is entering them against a database directly. Yet, the vast bulk of database queries are performed by software, not by humans. Any program which persistently records information is most likely using a database in the background.

Consequently, the program is generating SQL commands on the fly, and building into them pieces of information which were supplied by an end user. And there's the risk; end users aren't always going to type in sane or sensible or safe things.

“SQL injection” is thus the term assigned to a particular form of hacking/cracking action that seeks to exploit such dynamically-generated SQL instructions by inserting extra commands.

Let's illustrate with an example. Suppose you have a database table called USERS with fields USERNAME and PASSWORD. A web site might prompt people to log in with a form that has two fields, txtUsername and txtPassword. The expectation is you type in your valid username and password, and that only legitimate users will do this, and that they won't try anything funky.

When the user clicks “Login” the backend of the web page validates your credentials by executing an SQL query like this:

“select * from USERS where USERNAME = '” + txtUsername.Text + “' and PASSWORD = '” + txtPassword.Text + “'”

This query requests the database divulge all the users who have a username and password matching that which has been entered by the web site visitor. Assuming the user entered a username of Tom and a password of gluc77 the SQL query actually sent to the database would be

select * from USERS where USERNAME = 'Tom' and PASSWORD = 'gluc77'

All well and good. You will notice that we use the single quote character to delimit text strings. And here's where our injection comes in. Want to log in for free? Try entering a username of ' or 1=1 --

Now, let's get that clear; the actual username to type in is

' or 1=1 --

The username begins with a single quote and ends with two minus signs. It doesn't matter what the password value used is. The SQL query sent to the database becomes

select * from USERS where USERNAME = '' or 1=1 -- ' and PASSWORD = ''

In SQL -- is a special sequence that means everything following is purely a descriptive comment for human beings and ought to be ignored by the database. Therefore the command executed is

select * from USERS where USERNAME = '' or 1=1

The database now duly returns the entire table of users because there are now two possible conditions that can be satisfied. Either the username is blank ('') or the value of 1 is equal to 1, which of course it always is. So, every single line in the table matches. The web site will pull out the first row and proceed, effectively logging the web site visitor in as that person – which typically is the web site administrator.

Well and good, but what's theory without practice? Here's how to find a site that is vulnerable to such an attack and, in fact, two such sites that (at the time of writing) are indeed ripe for it.


You might have a web site you've been working on. You should be testing it for security vulnerabilities like this.

Not having such an in-progress site of my own to show you, I need to harness the raw helpfulness of the ever-enthusiastic Google to find some samples.

On the one hand, this may be considered wrong of me. After all, fundamentally, this is how to circumvent security on web sites.

On the other hand, this is crucial information that web site operators and owners need to know. The fact is, web sites can be vulnerable to this sort of attack. Without any effort I found two just now through a quick Google. The operators of these web sites may in fact have had unscrupulous people circumventing their login pages for years without detection. This publication of their weakness may serve to prompt tighter security on their part.

SQL injection can be avoided in several ways. One fundamental thing to do is not blindly trust user input and pass it on. Every string typed in by a user should be scanned for instances of the single quote character at a bare minimum. Databases that support stored procedures and/or parameterised queries should have these in use rather than dynamically-generated SQL. Indeed, writing queries that way will actually increase performance as well as remove this weakness.

So, how did I find these two sites and what are they?

First, the bulk of sites which will be executing SQL queries are going to be written in some form of scripting language, like Microsoft's Active Server Pages (asp, aspx) or PHP. Secondly, chances are the page name will be of the form login.asp or login.php or some other variant.

You can find pages like these by using a Google operator which specifies the search term ought to be considered part of the address, not from the text on the page. GovernmentSecurity.Org have published an article which gives some ideas for basic search terms.

In my case, I Google'd for a page with a name akin to that I mentioned. There were loads of results. I tested two and both were weak.

These pages from an SMS messaging gateway provider ("Site A") and a beverage vending machine distribution group in Northern Virginia ("Site B".)

Here's how I tested the sites were weak. Firstly, I clicked on the link from Google. This brought up a login page in both cases. I entered fred' as the username and a password of ddd and clicked Login.

Site A reported this error

Microsoft OLE DB Provider for SQL Server error '80040e14'
Line 1: Incorrect syntax near 'ddd'.
/Orders/OrderForm.asp, line 43

while Site B reported this error

Microsoft OLE DB Provider for SQL Server error '80040e14'
Unclosed quotation mark after the character string 'fred''.
/Orders/login.asp, line 20

The fact that I got an error message proves both sites were programmed without security in mind. Neither site tested that my input contained a single quote character and sent it to the database server with full trust.

Now that I know this much I can go further, and probe the limits of these sites. Firstly, could I log in without an account? The answer is yes. Here's how.


Can I log in to these sites without an account? To find out I entered a username of fred' or 1=1 -- on both forms.

The answer is yes. Site A took a brief while to log in – perhaps having many users in its table – but let me in and duly gave me a list of orders I, or rather the account it logged me in as, had made.

It was quite a jackpot, too. It seemed I had probably every order on the site listed. This stands to reason; as I said, the first user in the user table is often the administrator. The first bunch of orders appeared to be test data, but just scrolling down the table listed orders placed by D L Rogers Corporation, Muse Lifestyle Group, American InterContinental University, American Golf, Best International USA LLC and more.

Clicking these orders let me see the Site A staff member who took the order – Ms S Wells, in some cases – plus the order number, the client name, contact person and contact details. I could see the details of the orders (eg, $500/month) plus the client's terms and conditions.

If I were looking for an SMS gateway to send my online messages through I may be somewhat concerned by this. Now, Site A had not included any message on their login page – or after logging in – that stipulated unauthorised access was not permitted but that's a topic for another time.

Back to Site B, I also logged in effortlessly. Actually, this site did have a bit of JavaScript to validate that a password had been entered. A nice touch, but a bit in vain, because I could still log in using the username as specified above and any old password.

There was no clear facility to view past orders but I most definitely was given the product catalogue and could place an order (as Greenspun & Mann of the city of Fairfax in the state of VA) if I so desired.

Obviously, I did not print or copy any of the data I saw, nor place any orders. Further, I have e-mailed the contacts for both sites to advise them of this grotesque weakness on their sites.

Although I gained access to these sites, what I have described above really only scratches the surface. I could go further and actually work out what the table structure is of the databases in use which would permit even more nefarious activities.

For instance, using a username of fred' having 1=1 -- on Site A gives this error:

Microsoft OLE DB Provider for SQL Server error '80040e14'
Column 'Sales_Rep.RepID' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
/Orders/OrderForm.asp, line 43

So, now I know they have a table called Sales_Rep with a field RepID. Further tweaking the username would reveal more and more information. Finally, I could even query these tables to actually divulge genuine usernames and passwords.

Basically, the site is mine. Fortunately, I'm one of the good guys.

Make sure your site is not similarly exposed. This is how SQL injection works, and as you've seen, anyone at all with access to Google and a small bit of knowledge can break in with seconds of effort.



Recently iTWire remodelled and relaunched how we approach "Sponsored Content" and this is now referred to as "Promotional News and Content”.

This repositioning of our promotional stories has come about due to customer focus groups and their feedback from PR firms, bloggers and advertising firms.

Your Promotional story will be prominently displayed on the Home Page.

We will also provide you with a second post that will be displayed on every page on the right hand side for at least 6 weeks and also it will appear for 4 weeks in the newsletter every day that goes to 75,000 readers twice daily.




Denodo, the leader in data virtualisation, has announced a debate-style three-part Experts Roundtable Series, with the first event to be hosted in the APAC region.

The round table will feature high-level executives and thought leaders from some of the region’s most influential organisations.

They will debate the latest trends in cloud adoption and technologies altering the data management industry.

The debate will centre on the recently-published Denodo 2020 Global Cloud Survey.

To discover more and register for the event, please click the button below.


David M Williams

David has been computing since 1984 where he instantly gravitated to the family Commodore 64. He completed a Bachelor of Computer Science degree from 1990 to 1992, commencing full-time employment as a systems analyst at the end of that year. David subsequently worked as a UNIX Systems Manager, Asia-Pacific technical specialist for an international software company, Business Analyst, IT Manager, and other roles. David has been the Chief Information Officer for national public companies since 2007, delivering IT knowledge and business acumen, seeking to transform the industries within which he works. David is also involved in the user group community, the Australian Computer Society technical advisory boards, and education.


Webinars & Events