[SOLVED] Someone killed my database!

[robcrozier]

OK, i've got a mysql database set up on my server and someone once mentioned that it is possible to delete tables and even the whole database by using the text boxes on the web pages that are supposed to be there to enter data records into the database. 

 

They were right, someone has just deleted (dropped) my database this way!  Im not sure what code they used obviously but i want to be able to prevent this in the future.  Has this ever happened to anyone here and what did you do to stop it happening again?

 

Cheers!

[mkosmosports]

Are you using php to process whatever the user is entering through the text boxes?

[robcrozier]

No, not in this particular case i wasn't that's the problem.  I usually prevent silly things like '(' or '$' signs within usernames and password etc... However like i say this time i wasn't.

 

What i'm trying to find out is what sort of things to look out for and thus filter via PHP when users are inputting data via text boxes on the website.

[mkosmosports]

"However like i say this time i wasn't."

 

Well, that would explain how someone could do what they did.

 

Look into using mysql_real_escape_string on all of those entries through the text boxes when running your query. Or addslashes.

[robcrozier]

NO... there was no protection/filtering at all in this particular case

[mkosmosports]

Sorry robcrozier, I missed what you said. Check my response again.

[KevinM1]

Two words: form validation.

 

One of the most common mistakes by newbie coders is that they never check to see if the input a user enters is valid/legit.  Coders should always assume that user input is corrupt and code defensively.

 

Form validation, in a nutshell, is the process of checking each input's value against what it [/i]should[/i] be.  For example, if a text field is used only for numbers, you shouldn't allow any letters/words to be processed within it.  So, yes, in order to validate a form, you'll need to go through each form input and test it.  Tedious, but vital.

 

This subject is a bit too broad to say "This is exactly how to do it" as validation is dependent on the context of the form.  Typically, one tests that all vital inputs aren't empty, then tests that the values entered matches the expected format.  This last step is usually done via regular expressions (regex).  There's a subforum here that deals with regex in PHP.  I suggest that you read the stickied threads there.

[KevinM1]

What i'm trying to find out is what sort of things to look out for and thus filter via PHP when users are inputting data via text boxes on the website.

 

Sorry for the double-post, but I think that this is the wrong philosophy to take.  Trying to blacklist all of the bad things that could be entered is:

 

1. Not efficient.

2. Not truly possible.  Something will always get through the cracks.

 

Like I said before: code defensively.  Instead of allowing all but the blacklisted data in, deny all but the whitelisted data.  Only allow what you want into the system.  Discard everything else.  This is where regular expressions come in.  Regex is just pattern matching.  If data matches the pattern you're looking for, then you can process it.  If not, don't let it in the system.

 

One key is to use common sense.  Most form inputs don't require the entry of parentheses or commas, or any other special characters.  So, since that's the case, don't allow them to be processed.  If an input is for numbers only, check that only numbers are in the field.

 

Another key is to escape the data you're going to put into the database.  This step happens last, after you've already screened the data.  I use the following function which does slightly different things whether or not magic quotes are on:

<?php

function myEscape($string){
	return get_magic_quotes_gpc() ? mysql_real_escape_string(stripslashes($string)) : mysql_real_escape_string($string);
}

?>

 

Some sample form validation:

<?php

/* This checks to see if the input only has a number -- if not, it's false.
It uses the myEscape function from above */

$errMsg = "";

if(isset($_POST['submit'])){
   if(!empty($_POST['numInput']) && preg_match("/^[0-9]+$/", $_POST['numInput'])){ // More on this below
      $numInput = myEscape($_POST['numInput']);
   }
   else{
      $numInput = NULL;
      $errMsg .= "Only numbers allowed!<br />";
   }
   .
   .
   .

   if(numInput){
      //insert into/update database
   }
   else{
      echo $errMsg;
   }
}

?>

 

The pattern in the preg_match function says "Give me one or more (+) characters in the range 0-9 ([0-9]) for the start (^) and end ($) of the pattern."  If the value ($_POST['numInput']) matches the pattern, the function returns true.  If not, it returns false.  Since there's nothing else in the pattern, only a positive integer value will pass the test.

[robcrozier]

Thanks everyone, i will give it a bash!