CoverYourASP --> Snippets

Free membership

Join in the fun! Sign in
Member Services

Site navigation
Download the entire site!
Search my articles
Free Magazines
Browse the directory

Send me feedback
Buy my boxer shorts

Recommend this page
Printer-friendly page

Resources I recommend
Link to my site
Advertising slashed!
About your privacy
Legal stuff
Site statistics
207 active users
2013 visitors today
1785 pages today
(only part of today)
Tools I use

Copyright © 1999-2016 James Shaw.
All rights reserved.

RSS submissions

Now open source with SourceForge!

"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:

// ============================================
// 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!

Featured sponsor
My favorite resources

I share my content

Supporting ASPRSS

Do you need a quick and easy way to link to my articles? All the information you need is published with ASPRSS...

New Proposal Kit Professional 5.1
Brand yourself as a top professional: create quotes and amazing proposals and get many legal documents free!

The latter saved me 3 times the purchase price on the first day I owned it!