Hints on writing Secure Dynamic Web Pages

This article is aimed at those starting out writing dynamic server-side web pages created with tools such as Classic ASP and PHP. It is especially geared towards those developers coming from a traditional programming background, such as VB 6. The examples in this article use Classic ASP and Microsoft SQL Server.

The Current Web Environment

In the Halcyon Days of the early Internet, no one bothered much with security or writing secure code - it was a trusted environment. However, times have changed and money can be made by exploiting vulnerabilities in web servers and websites. This article will hopefully alert developers to the all-to-common mistakes made when writing dynamical, data-driven websites.

The Web Page as a function

Perhaps the way to view a dynamic web page is like a Function or Subroutine in a programming language. It is a 'standalone' piece of work that will take the parameters provided and perform a task. However, unlike a traditional programming Function, this routine is callable by anyone with an internet connection. Consequently, you cannot really control what data is passed to the page. Sure, you know YOUR calling page is going to pass an integer into the sectionID parameter - maybe you've even written some JavaScript to validate the users input - but at the end of the day, there's nothing to stop a semi-skilled attacker passing what they like through to your target page.

The SQL Injection Attack

I'm going to concentrate on the above attack vector since it's the most often overlooked by web developers. This type of attack works because of the way developers have learnt (typically from Microsoft tutorials!) to construct dynamic SQL. I'll look at a couple of examples to illustrate how SQL injection works.

In Authentication

Take a look at the code below:

recUsers.Open "SELECT * FROM users WHERE " & _ "login_name = '" & Request.Form("login") & "' " & _ "AND password = '" & Request.Form("password") & "'" If Not recUsers.EOF Then Session("authenticated") = "1"

The code is pretty simple - it takes the login and password posted from a form, plugs it into a SQL statement and runs it. If the resulting recordset is not empty, there must have been a match, therefore we're authenticated.

However, the above code is easily exploited by entering:

Into the password box. This would result in the following SQL being passed to the SQL Server:

SELECT * FROM users WHERE login_name = 'some_user' AND password = '' OR '1' = '1'

The addition of the OR clause has meant any row matches the criteria and we never have an empty recordset, unless the base table is empty.

Note that in 'real life' the password should be encrypted in the database, maybe via an MD5 algorithm, but for the sake of clarity, we'll keep the above example simple.

In Data Deletion

Using the same example code as above, it is possible to execute a DELETE statement by manipulating the password text. If the following text were entered:

The following SQL would be passed to the SQL Server:

SELECT * FROM users WHERE login_name = 'some_user' AND password = '' ;DELETE FROM test WHERE '1' = '1'

And yes, this WOULD delete all the users from that table.

Caveats and Mitigation

In order for the above to 'work' there are a number of conditions that have to be fulfilled. Firstly, in the case of deletions, the connection to the database would have to be read/write. If, as a developer, you can get away with it, the connection should be read-only. In addition to this, you should have a dedicated login that accesses the database from the website and you should assume this login is going to be hostile. It should not have direct access to the base tables - all database transactions should go through Stored Procedures, since this goes a long way towards stopping exploited SQL statements from 'creeping' around the database. It also has the additional benefit of hard-typing parameters, therefore numeric parameters are checked for 'correctness' by the Database / ADO before being passed for execution.

Secondly, the attacker needs to guess the underlying table name or hope the SQL Driver / Web page in question provides 'helpful' debugging messages. Back in the Bad Old Days, there were a number of exploits for IIS that would reveal the source code behind an ASP page. Things are better today, but always assume your code can be read by an attacker.

Thirdly, there are a number of in-built Stored Procedures in Microsoft SQL Server that may be exploited to gain Shell (xp_cmdshell) or Object (sp_OACreate) access. I haven't experimented too much with exploiting these via SQL Injection, but, as a developer, you should be aware these exist.