This is a very important issue to consider when you're using form/url variables in databases. A user can cleverly put in SQL statements and manipulate your db server as he sees fit if you don't take the necessary precautions. I get the feeling that there are several people that aren't aware of this when they develop their apps. I crashed a page from jumptheshark.com a while back doing this and I e-mailed them about their bug. They seemed to fix it the next day but I never got a thank you!

If expecting integer, on a script, check if it's of type int, and check if it's within boundary of integer - if it's expecting numbers from 1-20, then just make it within this boundary, if larger or less than this boundary, take necessary action.

Also check that your integers/reals that your script is expecting from the user are less/greater than than large integer/reals or whatever the max/min of the language you're using... sometimes a big or small enough number can cause the page to crash. For example, if you define something expecting an integer, and a user manually puts in 9999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999 it can cause unexpected results.

Strings - if its a value from a list box or pull down menu, error check for those values in the script... if anything else, take necessary action. If it's a string the user has to put in, check if the first chars are: ' or " or ; - (If the user is expected to put in a their name, a number, a name, there should be no reason why they would be putting fields/chars that are part of SQL statements. Also make sure you always replace all <>"& with their html entities.

When accepting variables from a form/url make sure you have a function that always replaces single apostrophes with double apostrophes. Consider the following:

You have a form where the user has a text box and a submit button. It asks him to enter his name. He enters the name "ALFKI" (without the apostrophes).

Let's say the script is suppose to retrieve information about that name (simple drill down). So the script receives the name and executes the following:


select *
from customers
where customerid = 'ALFKI'

(figure 1)

"ALFKI" in this case is dynamically inserted through whatever script language into the SQL statement. There's nothing wrong with this statement. Now, what if the user inserts the following?


ALFKI';select * from customers--


(figure 2)

He enters the above as you see it. With the apostrophes, semicolon, asterisk, and two dashes at the end. This statement will append another SQL statement to the previous. So now you will get a list of customers where customerid = ALFKI and another list of everyone under customers.


The first apostrophe you see in the SQL statement in figure 2 closes the statement in figure 1, and appends the extra select statement. The two dashes at the end of figure 1 are to comment out (in SQL Server) the apostrophe in figure 1's last dash. So your original SQL statement (figure 1) will now be executed as the following:


select *
from customers
where customerid = 'ALFKI';select * from customers--'


Replacing single apostrophes with double apostrophes can be beneficial in this case. Of course the person doing the injections won't know the table names, but however will get the page to crash. If the servers are not configured right, then the database interface could spit back an error with table names. This is a problem.


Here's a good article.
http://www.webmasterbase.com/article/794

You can search for more articles on Google by searching for "SQL Injection"