Validate Input Numeric Data

[nightkarnation]

Hey Guys...I am trying to secure my php file and have been reading a lot regarding sql injection.

 

I still dont understand clearly how to prevent sql injection through numeric data input, since from what I understood mysql_real_escape_string() does nothing about it only prevents attacks on string input.

 

Here's an example:

 

if ($action == "checkId")
{
//retreive data from flash
$user_id=mysql_real_escape_string($_POST['Id']);


$result = mysql_query("SELECT user_id from users WHERE user_id = '$user_id'");
if (mysql_num_rows($result) > 0) 
{
	echo "status1=exists";
} 
else 
{
	echo "status1=id doesnt exist";
}
}

 

I would like to create a function like this:

 

foreach($_POST as $post)
{
$postvars[$key] = htmlentities($post); //XSS prevention
$postvars[$key] = mysql_real_escape_string($post); //Sql String Prevention
}

 

But then again...How do I check on the numeric POST's ? how do I validate them through this function?

 

Any suggestions and/or ideas?

 

Thanks a lot in advance!

Cheers.

[Anti-Moronic]

If I read you correctly, you just want to protect a column within your sql database to ensure only int can be entered?

 

You should then define the column as an int with a max amount of digits if necessary.

 

You can ensure you are only dealing with numbers using (int) with php:

 

$string = (int)'abc';

 

returns: 0

 

 

[nightkarnation]

Hi Anti-Moronic, thanks a lot for your reply!

 

What if I have a long list of numeric variables, is there a way I can automate this, instead of having to add the int on each variable?

 

What about is_numeric() does the same?

 

Thanks a lot!

[sharal]

(int) typecasts the variable as an integer, hence all non-integer values will be gone. is_numeric($number)

returns true, if $number is a numeric, but does nothing more.  You could also use:

 

$var = filter_var($myInteger, FILTER_VALIDATE_INT);  // validates

 

http://www.php.net/manual/en/filter.filters.validate.php

 

also

 

$var = filter_var($myInteger,FILTER_SANITIZE_NUMBER_INT);

 

http://www.php.net/manual/en/filter.filters.sanitize.php

[nightkarnation]

Hi Sharal,

Thanks a lot for your help!

 

I have a direct question to anyone that may know the answer,

Its a simple, stupid question which I cant trully understand yet.

 

If I validate numeric data only to POST's that should receive numeric data...

What if a hacker sends a numeric injection to a string POST...how do I protect that?

 

Is mysql_real_escape_string going to work? (if yes, then why it doesnt work when sending to a numeric POST ?) when actually the POST is the same, only that the variable contains different data type...

 

I am lost here...

 

Here's something I read on this forum:

 

"It however does not protect against sql injection in numerical data (i.e. data that is not put between single-quotes in a query.) For numerical data, you must validate that it is numerical or simply cast it as a number before you put it into a query. The reason for this is that it is possible to craft a query that does not use any quotes in it that injects a UNION query to dump all the data in your table. When this type of injection is used in STRING data, it is just treated as data. When this type of injection is used in numerical data it becomes part of the query.

"

 

So what happens if a hacker injects numerical data into a post im only expecting STRING data ?? how do I prevent this?

 

[Pikachu2000]

That doesn't fall under SQL injection, really. SQL injection is when a malicious user attempts to use characters that will allow them to break your query string and 'inject' their own commands.

[nightkarnation]

Hi Pikachu,

Thanks a lot for your reply!

 

So if I use the following little script at  the start of my php files, sql injection is fully protected?

 

foreach($_POST as $post)
{
$postvars[$key] = htmlentities($post); //XSS Prevention
$postvars[$key] = mysql_real_escape_string($post); //Sql Injection Prevention
}

 

Any ideas and/or suggestions on how I can improve this script? am I safe enough here?

[BlueSkyIS]

safer than any other PHP programmer. ever. 

[BlueSkyIS]

but seriously, I think that is enough to keep you safe, though I'm not sure that your script will work as intended. you might want to try something more like:

 

foreach ($_POST as $key=>$val) {
     $postvars[$key] = htmlentitites($val);
     $postvars[$key] = mysql_real_escape_string($postvars[$key]);
}

[nightkarnation]

Hi BlueSkyIS, thanks a lot for your help!

 

Let me show you some actions on how my script is organized:

 

foreach ($_POST as $key=>$val) {     
$postvars[$key] = htmlentitites($val);     
$postvars[$key] = mysql_real_escape_string($postvars[$key]);
}

$action=mysql_real_escape_string($_POST['action']);

//----INSERT REFERRALID TO REFERRED
if ($action == "registerReferral")
{
$email=mysql_real_escape_string($_POST['Email']);
$name=mysql_real_escape_string($_POST['Name']);
$referral_id=mysql_real_escape_string($_POST['ReferralId']);  

$result = mysql_query("UPDATE `users` SET referral_id = '$referral_id' WHERE email = '$email' AND name = '$name'"); 
if($result) 
{ 
	$imdoneUpdate = true;
echo "imdoneUpdate=".$imdoneUpdate;
} 
else 
{ 
	$imdoneUpdate = false;
	echo "imdoneUpdate=".$imdoneUpdate;
}
}

//change credit to redeemed credit
if ($action == "redeemCredits")
{
$email=mysql_real_escape_string($_POST['Email']);
$credits=mysql_real_escape_string($_POST['Credits']);
$date_redeemed=mysql_real_escape_string($_POST['DateRedeemed']);

//checks first if username exists
$result = mysql_query("SELECT email, credits, date_redeemed from users WHERE email = '$email'");
if (mysql_num_rows($result) > 0) 
{
	$result = mysql_query("UPDATE `users` SET credits = (credits - '$credits'), redeem_credits = '$credits', date_redeemed = '$date_redeemed' WHERE email = '$email'");
   		$imdoneUpdate = true;
	echo "imdoneUpdate=".$imdoneUpdate;
}
else
{
	$imdoneUpdate = false;
	echo "imdoneUpdate=".$imdoneUpdate;
}
}

//And Other similar actions like the ones above

 

Based on this script, do you think the protection script that you suggested will work fine as it is?

Thanks again!