|CoverYourASP --> Snippets|
|"Snippets" are mini-articles - they allow me to very quickly add answers to your frequently asked questions, and add brief explanations for topics that you've searched for without success. So, everytime you send an email or perform a search, chances are a new snippet will be the result!|
5 Oct: Be afraid of users entering malicious SQL...John took the time to report a problem with the login code recently, which reminded me to write this warning again:
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:
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!