5 Oct: Be afraid of users entering malicious SQL...

Always check any user input and remove harmful SQL

Here is a good example of where I hadn't done this:

DBGetRecords ( 'SELECT MemberID FROM Members WHERE Email=\'' + sEmail + '\' AND MemberPassword=\'' + sPassword + '\'' );

This simply returns a recordset containing a record that matches the given email and password. The problem with this is that I didn't check the values being entered on the form.

John entered this string for the email address:

   a' or Email like '%whatever%' or email='a

If we substitute that "email address" into my SQL statement we get this:

SELECT MemberID FROM Members WHERE Email='a' or Email like '%whatever%' or email='a' AND MemberPassword=''

As you can see, this changes the way the statement works so that it will match any email address containing "whatever". No check is made on the password now.

To fix this problem I simply pass all data from users through my DBEncode( ) function (in utils/database.asp) that contains the following code:

// ============================================
// return value with ' replaced by SQL-safe ''
// ============================================
function DBEncode ( sValue )
{
   return sValue.replace ( /\'/g, '\'\'' );
}

This is a function I use when building all my SQL statements - it makes safe many malicious SQL, and can easily be updated when other characters are found to be trouble!