SQL injection attacks
If you are taking in user data for any SQL query, it is imperative that you validate every single field that you use. Otherwise your data can be damaged with a SQL injection attack.
What Is a SQL Injection?
To summarize, a SQL injection attack is when someone uses a field you are taking in as input - say a comment field in a blog, or an email address for a newsletter signup - and replaces that field content with SQL commands. You think you are getting in Lisa@xxxxxx.com and start doing SQL commands with it - but in actuality what you are processing is a full SQL string which does whatever the hacker wants it to do.
So let's say you have blog.asp and you are expecting in a variable called comment. You take in that comment variable, and then use it in a SQL script, something like insert into blogtable values ('date', 'comment'). You expect the comment to just be inserted into the table. However, let's say the hacker has put in as their comment the text
');delete table blogtable;.
Now the full string of your SQL script turns into this:
insert into blogtable values ('date', 'comment');delete table blogtable;
So the SQL database merrily inserts a row - and then deletes the entire table. Of course hackers rarely delete tables - instead they use this ability to update your entire system to contain references to their porn sites.
If this is confusing, take it step by step. The hacker figures out what input you are accepting. They then keep trying various changes to the input field until they figure out how to get it to 1) not give an error and 2) create a fully valid syntax so that their insert and update requests go through properly. They don't have to do this on their own! There are a slew of scripts out there that they use that do this all in an automated fashion. It tries combo A, then combo B, then combo C, and goes right down the line testing out every possibility against your variable field to see which one works. Your database could be hammered for an hour or two - in the wee hours of the morning - by this script. Once the script figures out its opening, the damage begins.
How the SQL Injection Works
So to explain again, The main problem here is that SQL commands have a well known syntax. So in essence someone using your form can make a guess at your syntax, insert any SQL commands they want to in your form - and you likely will pass them right along to the database to execute.
Let's say you ask a user for their email address, and allow them to change it. You are running an update script that says
update people set email = '(email address entered by user)' where ID = 1
You plunk into your command whatever the user entered. But let's say they did NOT just enter an email address into the box. Let's say they entered a SQL text string:
'; update people set email = 'Be sure to visit my porn site at http://www.xxx.com'; select from people
now the entire SQL will read
update people set email = ''; update people set email = 'Be sure to visit my porn site at http://www.xxx.com'; select from people where ID = 1;
So now your entire database is going to be updated with garbage in every row, because of what that one person entered into your system.
There is no way to "stop" SQL from doing this. This is what it does, it executes commands. It is your job as the system administrator to ENSURE that every command you pass along to SQL is 100% correct and valid. That means that EVERY single form you take data into must be verified in every way you possibly can, to ensure that it only contains appropriate data. Hackers just love to corrupt peoples' systems for fun.
So important things to do include:
* Truncate input to as short a field as possible
* Check for '' and remove them
* If possible, eliminate anything but letters and numbers
* Use cInt and cLng where possible to ensure only numbers are input
* Prosecute any hackers that attempt assaults, so they are stopped!
A more robust solution is to turn your entire site into using procedures, since procedures do variable checking as part of their nature.
ASP Form Creation and Security