sql injection and hacking

[otuatail]

Hi I have taken lots of precautions including real escape string. I came acroos this on a website and can't see if this is a true risk?

 

http://YourWebsite.com/login.php?id=2‘; DROP TABLE login; #

 

If I do have a table login, will it be dropped? I am only passing information in the URL

 

TIA

Desmond

 

 

[PFMaBiSmAd]

Mysql_query() does not support multiple queries separated by semi-colons ; so the example you posted would not likely be a problem (you could always test it on a development system.)

 

However, for numeric data (which is what an id is normally used for in a query), if you are not validating or casting the entered number as an integer it is possible to inject sql that contains no quotes in it (i.e. a string that is encoded as a HEX value) that mysql_real_escape_string() won't protect against. You can for example inject a UNION query that will show all the usernames and passwords in your user table onto a typical SELECT query. The following can still have sql injected -

$id = mysql_real_escape_string($_GET['id']);
$query = "SELECT * FROM your_table WHERE id = $id";

 

The following cannot -

$id = (int)$_GET['id'];
$query = "SELECT * FROM your_table WHERE id = $id";

[andrewgauger]

To check for sql injection you need to attempt to "inject sql" into a variable.

 

In your example you are assuming that ?id=2' DROP TABLE login; will become

$id="2'; DROP TABLE login; #" right?  well you said you were real-escaping. That should mean that you parse the _GET variable?

 

does your

$id=mysql_real_escape($_GET['id']);

or do you escape on the insert? 

 

What you should do is to test this using something less sinister than DROP TABLE.

 

Why don't you see if login.php?id=2'; CREATE TABLE vuln (uhoh int); #

 

What your login id variable should equal is along these lines:

 

$id=intval($_GET['id']);

 

which even with this sql injection, it will get the correct id=2 value.

 

Remember to test all the possible sql injection attacks you can think of, that anyone can advise, but don't use DROP TABLE to test for injection.  Do something that isn't going to bring yourself to your knees.

 

PS also try escaping the ':

?id=2\''; CREATE TABLE vuln (uhoh int);

 

Also, real-escaping encourages sql injection, whereas type validation prohibits it

[flappy_warbucks]

Hi I have taken lots of precautions including real escape string. I came acroos this on a website and can't see if this is a true risk?

 

http://YourWebsite.com/login.php?id=2‘; DROP TABLE login; #

 

If I do have a table login, will it be dropped? I am only passing information in the URL

 

TIA

Desmond

 

 

 

A perfect example why you should clean all data going to the database.

 

But i don't think mysql_query will allow multiple queries? i know it does with Union, but never tried multiple.

 

I don't rely in mysql_real_escape_string on it's own, or even with addslashes() so i build all my web applications with security in mind.

 

In your example, i would be tempted to make sure that the passed variable is no longer than 3 characters in length, and i would also check to make sure that the variable is numeric.

 

If both of them equate to false, i would send the user to the home page, and make a log of the passed data on the server (just in case my logic went a bit coo coo (it does happen) or to give me an insight into what people are trying to do to the site, which in turn may guide me in a direction to make the application more secure.)

 

If i have to use words (such as page.php?action=contact) then i would use a switch statement with a default clause to handle any unexpected inputs.

 

If what i am doing is dynamic i.e. mysite.com/some_user then i would make sure all user names have to have an underscore and any variable passed will be checked to make sure they have one (if they don't, then they get dropped) but i would be tempted that any page like that, which any data that is passed as a string will have a read only database connection instance so any dodgy strings cant alter the database in any way.

 

But in real life, you never run a website with full privileges to the database, that's just asking for trouble.  You give the website all it needs to run (i.e. read and write without the ability to alter or drop tables or data)

[otuatail]

Hi thanks for that. It was the unusual‘; DROP TABLE login; #

on the end.

I couldent see this being a problem as I would not be reading this. also would

mysql_real_escape_string() cause a problem with email address as there are some strange formats out there?

 

[andrewgauger]

http://www.linuxjournal.com/article/9585

 

The only email validation you'll ever need.

[Psycho]

Remember to test all the possible sql injection attacks you can think of, that anyone can advise, but don't use DROP TABLE to test for injection.  Do something that isn't going to bring yourself to your knees.

 

Why would testing DROP TABLE bring you "to your knees"? You should be testing in a development environment, right? Create your test database (if not already in place), back up the data, run your tests, restore the backup. Basic testing methodology. You should never do general testing against a production environment - especially for anything which could be expected to corrupt the data.

[andrewgauger]

So are you recommending you don't test for SQL injection on your production server to make sure what you learned in testing holds true in production?

[aeroswat]

So are you recommending you don't test for SQL injection on your production server to make sure what you learned in testing holds true in production?

 

No... What he is saying is that testing should be done on your test server. Production servers are ONLY for final product. The testing has already been finished and so it should work without problems once it reaches the production server... This eliminates the need for further testing unless more issues arise in which time you take it off of the production server, revert back to the old system and back to the drawing board. Rinse and repeat.

 

Or if it's not economical to revert back to the old system and the new system is not causing many problems then you could perhaps develop a work-around while you fix your mistakes on the test server again.

[Psycho]

So are you recommending you don't test for SQL injection on your production server to make sure what you learned in testing holds true in production?

 

The only testing that you would do in a production environment would be performance/load testing. If you are not confident in the testing you are doing in the test environment, why have a test environment? The progression in environments should be Dev -> Test -> Stage -> Production. There need to be specific quality gates and processes in place at each progression. Also there can be multiple Development and Test environments based upon the size of the application and business needs.

 

With Development/Test environments you may go though several minor iterative changes. So, the purpose of Stage is to do a full update of all the changes since the last deployment to Production to ensure all the changes are made correctly "en masse".

[andrewgauger]

Sounds great!  So you'd advise this on anything that will be published?

[seventheyejosh]

http://www.linuxjournal.com/article/9585

 

The only email validation you'll ever need.

 

I think this is what you're looking for as far as email validation -> http://ex-parrot.com/~pdw/Mail-RFC822-Address.html

 

[andrewgauger]

That was one heck of a regex.  The one I pointed to (probably) does the same thing, but it will also verify the domain exists.  It performs a dns query of the domain name to ensure that it is a legitimate domain.  You should check out the article.  If nothing more, it sure is easier to follow what is going on behind the scenes.

[jcbones]

If you are really worried about the "Drop table", then deny "drop" to the database.

 

For a public site, I usually only allow INSERT, SELECT, and UPDATE.  DELETE is for admin's only, and works on a seperate mysql user and password.

[Axeia]

PHP has build in email validating since 5.2.0

filter_var.

if(filter_var('bob@example.com', FILTER_VALIDATE_EMAIL) !== false)
{
  //some valid email adress logic
}

[otuatail]

Hi and thanks to andrewgauger for the email regx. The artical is extensive. It states that % and = are valid email charecters. I have tried to search for what charecters are allowed, but not found this. I didn't know that % was valid.