When you make your house secure, you might want to do the obvious things such as putting good locks on the doors, and fasteners on the windows, but you are still interested on peeping through the curtains to watch for suspicious activity in the street. If people in balaclavas are carrying ladders and eyeing up the side of the house, or checking sight-lines, you’d want to know about it, surely.

Why take a different attitude when you put a website out there, particularly if it is a trading site with lots of confidential information? Wouldn’t you want to see what the villains are up to? A couple of years ago, I did just that. I felt like the little pig that had the house of brick. Although I was confident that I’d done enough to prevent the big bad wolf from blowing the site over, I wanted to know if he was eyeing up the chimney with a view to a sneaky entrance, especially if I could light a fire when he’s descending.

It was a fascinating and salutary experience. I was not aware of the extent of the activity that bombards a site to try to break it. It is a constant stream.

Some attacks are so simple, yet seem to be still worthwhile for the hacker.  Surely there aren't sites around still with this sort of vulnerability?  Just as an example, there was a continuous stream of 404s (returning a 404 error to the requester to tell them the file was not found), resulting from people trying to find the location of the admin site.  Hmm. Up to no good here. The combinations were endless. Responding to 404s is dumb, as it allows an automaton to go through a very long list; login.asp, login.php, index.asp, admin.php, maint.aspx, and so on until they get a successful request. As an experiment I made a lot of completely bogus admin pages to react to these sorts of requests. It caused a temporary rush of excitement to some east European somewhere. Here, I discovered, is one of the few ways that the long-suffering site-maintainer can fight back, and laugh like a drain as a human being attempts to hack into your bogus site-admin console. There is a delicious pleasure from constructing a completely bogus database, using SQL Data Generator, and watching the hackers break in, but be careful, as you don't want to provoke a retaliatory DOS attack. The payback is that you get to see the whole process of an attack.

I had great fun seeing the ingenuity of these attempts to log in as the administrator of my dummy site admin screen. Mostly, they use a shared script, probably a Perl script, though there are plenty of applications that will do a brute-force attack. There is no shortage of places on the internet that you can download these tools. You don't have to be clever to use them, just to think them up in the first place. Although a few were completely unintelligible to me, most  are simple.

This is the way they do it. They get the Admin login screen. They check the form, and see what the variables are, just by looking at the <form> block. They see if it is a POST or a GET. When they know how to send login data to the database they are away. They rely on a very elementary mistake. It is anticipating that user input is either incorrectly filtered for string literal escape characters such as '', or user input that has not been checked for correctness. It relies on SQL statements being constructed on the fly, and executed,  rather than parameterized.

The hacker determines, from the FORM block, the name of the script file, ASPX or PHP, perhaps, to log in to the site. Imagine that the parameters represent user ID and password. The <FORM> block will tell the hacker all he/she needs to know.

The hacker passes any UserID he wants, let’s use the example ‘DAVE’, and passes, as the password the string ‘’’ or 1=1 --  (there are around fifty combinations of Magic strings, which are variations on this.) This will sometimes get him logged in as administrator. Game over.

Even today, there is a chance that the ASP file will not check to see if there are single quotes in the password. It will just take the GET or POST variable and stuff it into a SQL query, hoping it will look like this....
SELECT USER_Identity FROM MyUsers WHERE userid='DAVE' AND password='secret'
…and will expect to return a non-zero recordset if the userid and password were correct. Instead, the query will look like this!
SELECT USER_Identity FROM MyUsers WHERE USER='DAVE' AND password='''' OR 1=1 --'

Our password value, which is one of the many ‘Magic Strings’ …
''' OR 1=1 --
    … has had delimiters added and the resulting code had returned every row. Try it.

--just to demonstrate the technique
--we build a bogus list of users and their passwords
CREATE TABLE MyUsers (User_Identity INT IDENTITY(1,1),
                       UserID VARCHAR(20),
                       Password VARCHAR(30))

INSERT INTO MyUsers(UserID, Password)
       SELECT 'Dave', 'voluble12'
INSERT INTO MyUsers(UserID, Password)        
       SELECT 'Tony', 'cockeyed3546'
INSERT INTO MyUsers(UserID, Password)        
       SELECT 'Robyn', 'workinghard408354'
INSERT INTO MyUsers(UserID, Password)        
   SELECT 'Bob', 'MyPasswordIsSecure2396845'
--This is the SQL Statement the programmer wanted to construct
SELECT USER_Identity FROM MyUsers WHERE userID='dave' AND password='voluble12'
--this is the string that the hacker likes to inject
SELECT USER_Identity FROM MyUsers WHERE USER='DAVE' AND password='''' OR 1=1 --'
GO
--although the 'dynamic SQL crimes are generally committed in ASP, here is
--a stored procedure that commits the crime.
ALTER PROCEDURE spLogin (@userID VARCHAR(80), @Password VARCHAR(80))
AS
EXECUTE ('Select USER_Identity from MyUsers where userID='''
           +@UserID
           +''' and password='''+@Password+'''')
GO
--innocent procedure call
EXECUTE spLogin 'dave', 'voluble12'
--U BIN HAKKED!!!
EXECUTE spLogin 'THE HAKKER', ''''''' or 1=1 --'

Now, you wouldn’t believe that anyone is dumb enough to keep an exploit like this open would you? The rookie programmer scoops up the name of the userID, and the password and does no sanity checks. He/She then constructs the SQL on the fly rather than using parameters or, better, calling stored procedures. He doesn't look at the contents of the result that is handed back to him, even to see how many rows there are.

Notice that if getting the admin login to your website isn’t enough for you, you can always do more extreme things such as delete the user table

EXECUTE spLogin 'THE DESTROYER', ''''''' drop table MyUsers --'
--Or look for backup files with a view to FTPing them to you
EXECUTE spLogin 'THE CURIOUS', ''''''' execute xp_cmdshell ''dir c:\windows'' --'

This is just the start, of course. You don’t have to be able to access the Admin screen to get the information from a database. As long as the programmer has forgotten to parameterize his queries, forgotten to check for escape characters, or has failed to check for the validity of the input, in just one of the scripts that call the database, then your database is at the mercy of the hacker.

The hacker can use a technique such as blind SQL injection or inference SQL injection. This essentially does a binary chop to read strings character-by-character so as to get from the database a whole realm of interesting information.  All that the hacker needs is an indication of when a query returns rows and when it doesn’t.  Time is on his side, as the website probably has no alerts for unusual activity. (a binary chop for character string causes a spike)

Some of the easiest hacks have been possible because the result of a query is always rendered on the browser. It is so tempting to create a single generic routine for rendering all data returned from the database as an HTML table, or list.

Of course, if the database users are set up properly, none of this is possible as the login from the website only has access to the Interface stored procedures, and you don’t use dynamic SQL in these.  You have to assume that the hacker is somehow going to be able to pick up the login credentials from the site, and you must plan accordingly. I use the extra precaution of a time-limited, and revokeable,  authorization key issued from the database. You can then relax as you study the amazing complexity of the attempts to get at your data. When I say ‘relax’ I’m only referring to ‘as relaxed as the third little pig whilst the big bad fox paces around the brick house, huffing and puffing’. You can’t predict in advance what the hacker will try. I always create a log that stores all accesses to the interface, along with the parameters passed. It really takes no extra time to do this. I read the weblogs into the database as well, to check for unusual spikes of activity. It’s always best to know what attacks are being made to your websites.