SQL Injection, what do you guys do, any experts avil?
 

Been a ongoing issue for several months on the web, few questions

a) Once injected any quick way to clean up the bad data?

B) what kind of fix is there?

Will pay for help thanks

Half a million sites hit
http://it.slashdot.org/article.pl?sid=08/04/28/2014206

Just make sure your scripts filter any ' from the user input and you'll be save..

Because basically if your scripts makes querys like

SELECT * FROM `users` WHERE `userid` = '$_GET[userid]'

then I could inject by adding a ' to the ?userid=

So lets say I add ?userid=22'; DELETE * FROM `users` WHERE userid LIKE '%

I can make your query look like

SELECT * FROM `users` WHERE `userid` = '22'; DELETE * FROM `users` WHERE userid LIKE '%'

Several months?

IIS has been an insecure piece of shit since its inception. (yes, I understand this one is also the fault of the code being run)

There's no "quick" answer to a generic question about an SQL injection. It could manifest in an almost infinite number of ways.

It might look slightly different for ASP pages and MSSQL but the idea is basically the same filter out any characters from the user input which could alter your query's

a good practice is to put the user input first in a variable run some filters over that var before adding it to your query.. never include user input directly at the place of the query.. if you feel the need to include it with filter functions around it.

If you're using PHP: mysql_escape_string()

Further data sanitisation is probably a good idea, eg if a variable should be numeric only force it to be that by doing something like $var = 0 + $_GET["id"] . If someone enters "15; DROP DATABASE blah" then $var will only contain 15.

Another quick and dirty sanitisation:

$var = $_GET["somevar"];
$i = 0;
while ($i < strlen($var) && $var[$i] != ";" && $var != "'") $i++;
$var = substr($var, 0, $i);

This will trim $var to the first instance of ; or ' appearing, or leave the string as-is if neither appear

(note: this particular implementation from memory, & untested)

Strangely enough, I had just made a thread on another board about mysql injection as someone was attempting it on ATKCash.com over the week-end. Their attempt failed.
This won't help you sort out your data, but perhaps it'll help in preventing it from happening again.


What is MySQL Injection?
MySQL injection happens when a user enters values into a form that look like a MySQL query. I'll use examples that someone has just tried on ATKCash.com's "forgot password" form.
This hacker entered this into the box as his email address:
admin";+UPDATE+affiliates+SET+password+=+'123'+WHE RE+username+="admin

What this does is make the over all MySQL in my code look something like this:
WHERE username = 'admin";+UPDATE+affiliates+SET+password+=+'123'+WH ERE+username+="admin'

This hacker is hoping that his code would alter my query to set the password for user 'admin' to 123 and presto, he'd have access as an admin.

Not overly complicated, but in many cases, it does work.

How to prevent this
I don't expect you all to be coders, but even so, this is something that you CAN ask your programmer about, or check into scripts you buy/use. This is important as it's an easy in to your servers/scripts.

If you are a programmer, or have a programmer that is open to suggestions... or you have some other way of having these things done, here's what to consider.

1. Always have MySQL errors sent to you by email. 99% of these attempts will result in errors until they find the one that works. You won't even know that this is happening unless you're made aware of MySQL errors.
For me, I have the full query, the mysql error, the location/script name that it happened on and the IP address of the person who attempted it sent to my email address when it happens.

2. Have your MySQL errors display to the screen a very BASIC output. "Database error has occurred". Do NOT give them any more information than that, they may try to use it against you. What you should also include is their IP address. There's a real good chance they don't have a static IP, or are using a proxy... however, showing them their own home # sometimes puts them off from drawing even more attention to themselves by running errors over and over again.

3. If you can, or coder can... have certain characters and keywords NOT be allowed in any form input. UPDATE, INSERT, WHERE, ALTER, USERNAME.... many of these can be set as "reserved" words, or simply not allowed, and you can have people enter something else. It's a little tougher to do if you allow big textareas of text to be entered but for the security, it may be worth annoying a user or two.

4. Don't use standard names when possible. Use "user_username" or something, so you still know what it is, but that query alteration of "username = 'admin'" obviously wouldn't work since that field name doesn't exist. Also, never have any users named "admin", or "test" or anything else that's obvious.

5. mysql_real_escape_string is your friend. It will replace quotes and other special characters with SAFE characters that will be entered into the database, not used by the database. However, this is not a guarantee... there are ways around it. Use this method but also use all the other methods to ensure maximum safety.

6. Always have (int) preceding your ID's or other numeric fields. mysql_read_escape_string will not have much bearing on a numeric ID, so instead, force PHP to only use a number with (int)$_POST['id']. It will strip out all the bad stuff for you.

7. Use Google. Research. "MySQL injection" will give you a lot of results. And will give you many more examples and many more ways to prevent it.

Summary
This kind of attack pretty basic, and super easy for a wannabe hacker.... but it's still very important to consider. Don't put it off as something minor because anyone can take 5 minutes to learn this and cause you a world of headaches. Brute forcing and DDOSing a server, finding holes, implanting trojans... this stuff takes more effort. Think about it.
Prevent the easy to do stuff just as much or more as the hard to do stuff because there's WAY more people that can do the easy stuff.

I need to protect ATKCash's affiliates.... I want to help protect you too. Don't take this kind of thing for granted.

Nice tips Stuart, thanks..Bookmarked.

thanks guys

this one programmer I have has no clue what the heck hes doing as one of my sites keeps getting hit, using asp and sql 2000.

there are several ways to beat sql injection
 

the simplest of which is to not send dynamic data in the query string. Instead, just store them in session variables.

Another method is to create a middle layer between your web app source code and your database. Basically an XML webservice consumed by your main application. The webservice contains all your stored procedures and has methods you can call to get data and set data. All your application needs to know is how to interface with the web service, and the web service in turn handles opening, updating, and closing of database files. This is the proper enterprise solution used in system critical web applications in the financial industry.

Lasty, if you have no choice but to send data in the comand line, have your developer develop some bit shifting encryption algorithms and encrypt/decrypt the code as needed on each page load. That way all the end user ever sees is gobledegook, and would have to brute force strings of random characters to ever hurt your database.

If you would like to hire me as a consultant to help you fix your problem, e-mail me at my handle at g m a i l.

when using php, the proper combination of mysql_real_escape_string and mod_rewrite will take care of all of this for you.

any coder who doesnt validate ANY AND ALL variables passed via the query string AND post submitals should be fired, pronto.