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?
highlawnAsked:
Who is Participating?
 
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
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.