Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 554
  • Last Modified:

mysql_real_escape_string

When using mysql_real_escape_string on variables garnered from $_GET and $_POST variables, I currently only do this on text - not on numerics or on dates/times etc. Is this correct? Is there any harm on running mysql_real_escape_string on numerics and date/time etc.

What I am considering doing is writing a routine that applies mysql_real_escape_string before any insert/update by looping through all the values. The easiest way would seem to me to do this at the start of each page by calling a routine similar to the below (taken from php.net):

$_GET = array_map('trim', $_GET);
$_POST = array_map('trim', $_POST);
$_COOKIE = array_map('trim', $_COOKIE);
$_REQUEST = array_map('trim', $_REQUEST);
$_GET = array_map('mysql_real_escape_string', $_GET);
$_POST = array_map('mysql_real_escape_string', $_POST);
$_COOKIE = array_map('mysql_real_escape_string', $_COOKIE);
$_REQUEST = array_map('mysql_real_escape_string', $_REQUEST);

Any thoughts?
0
highlawn
Asked:
highlawn
5 Solutions
 
jet-blackCommented:
Yes, it is the correct way.
0
 
hieloCommented:
>>I currently only do this on text - not on numerics or on dates/times etc.
Shame on you.

>>Is this correct?
No. The whole point of validation is that you don't know what the user is going to supply to your script. Just because you EXPECT a number it doesn't mean that is what you are going to get. When crafting SQL injection strings, chances are you are going to get more than just numbers - specifically you will likely get/see apostrophes. So don't think if it in terms of dates, numbers and/or text. See it in terms of INPUT. In other words, apply mysql_real_escape_string to your input.

Consider for instance that your  SQL is:
$SQL = "SELECT firstName, lastName, username FROM Person where id =" . $_POST['id'];

Now if jet-black where to provide the following as the input:


Your actual sql would then become:
SELECT firstName, lastName, username FROM Person where id =3 OR username='admin'
0
 
VanHackmanCommented:

You MUST use mysql_real_escape_string() in all the data that comes from your users and NEED be used in a Query to your DB.


Another security good practice is apply the filter_var() function to each field with the right filter to be sure that the data have the correct data type:

http://php.net/manual/en/function.filter-var.php
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
profyaCommented:
The code you have submitted is incorrect since $_GET, $_POST, ... etc are readonly. I think the best thing is to use mysql_real_escape_string as the name implies on strings and use other validation functions on numeric and date/time values. This gives you two benefits, protection against sql injection and at the same time good input validation. For example for numeric entries use is_numeric built-in function and develop your own based on checkdate function to validate dates. Attached code is an example from http://php.net/manual/en/function.checkdate.php
<?php
function is_date( $str )
{
  $stamp = strtotime( $str );
 
  if (!is_numeric($stamp))
  {
     return FALSE;
  }
  $month = date( 'm', $stamp );
  $day   = date( 'd', $stamp );
  $year  = date( 'Y', $stamp );
 
  if (checkdate($month, $day, $year))
  {
     return TRUE;
  }
 
  return FALSE;
}
?>

Open in new window

0
 
r2d2_PLCommented:
It's incorrect to not use mysql_real_escape_string on numeric values. It's security risk and script written like that will usually get hacked. You should have some filtering for all data that goes to mysql database.

For numeric data you should use:
+ intval(...)
+ max(...,...) / min(..., ...) for additional validation

>The code you have submitted is incorrect since $_GET, $_POST
$_GET, $_POST are not readonly. But doing mysql_real_escape.... on $_GET and $_POST is incorrect too, because you may need eg. to display the data from $_POST on a web-form and for web-forms you should use function to strip html tags and encode special html characters.
0
 
highlawnAuthor Commented:
Thanks to all for your contribution - most helpful. I will certainly start applying mysql_real_escape_string to all input destined for the database.

Some variety of opinion with regard to the code from php.net and I'll take some time to think that through.

It looks to me like a validation routine is required first and then once happy with the data from the form, to mysql_real_escape_string those bits destined for the database.

Many Thanks
0
 
jet-blackCommented:
$_GET = array_map('trim', $_GET);
$_POST = array_map('trim', $_POST);
$_COOKIE = array_map('trim', $_COOKIE);
$_REQUEST = array_map('trim', $_REQUEST);
$_GET = array_map('mysql_real_escape_string', $_GET);
$_POST = array_map('mysql_real_escape_string', $_POST);
$_COOKIE = array_map('mysql_real_escape_string', $_COOKIE);
$_REQUEST = array_map('mysql_real_escape_string', $_REQUEST);

If you use above code from the FIRST line of the script, ALL user submitted variables are escaped.
Then, for this reason you do not need to worry about the sql injection, just use superglobals (not seperate date, numbers).
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now