understanding mysql_real_esacpe_string issue..

[Drongo_III]

Hi Guys

 

I'm a tad confused by what's going on when using real_escape_string. Could be that I'm using it incorrectly or that i'm not fully understannding it but here goes.

 

I'm trying to sanitize the post data from a form then escape it before storing it in my database. The code is as follows:

 

	$validation_options = array(


'title_2'					=>array('filter'=>FILTER_SANITIZE_STRING),
'name_2'					=>array('filter'=>FILTER_SANITIZE_STRING),
'surname_2'				=>array('filter'=>FILTER_SANITIZE_STRING),
'address_2'				=>array('filter'=>FILTER_SANITIZE_STRING),
'town_2'					=>array('filter'=>FILTER_SANITIZE_STRING),
'postcode_2'				=>array('filter'=>FILTER_SANITIZE_STRING),
'telephone_2'				=>array('filter'=>FILTER_SANITIZE_STRING),
'email_2'					=>array('filter'=>FILTER_SANITIZE_STRING),
'dob_2'					=>array('filter'=>FILTER_SANITIZE_STRING),



);


$validated = filter_input_array(INPUT_POST, $validation_options );	

      // Display results to test that it's working
echo "<pre>";
print_r($validated);
echo "</pre>";


      // Run validated array through real escape for database

$escaped = array_map('mysql_real_escape_string', $validated);
  
       // Display results to test that it's working
print_r($escaped);
echo $escaped['town_2'];

 

But here's the issue. When I used the $validated array and deliberately entered quotes or double quotes into the form and then print the results of $escaped it doesn't add slashes. However, if i make up a new test array with say

 

$testarray(

'TESTER' => "This is a 'test' and 'another test' "

);

 

and run that through the same escape function and print the results it displays the backslahes around the single quotes.

 

 

So does this mean that for some reason the $validated array is not being escaped? Or am I just getting something wrong?

 

Any help would be very much appreciated!

 

Drongo

 

PS Indicentally before anyone points this out - i incorporate the DB handler elsewhere in the code.

[Drongo_III]

I have been mulling this over in anticipation of some more enlightened help.

 

Could it be that because the validate function encodes html entities,like quotes,  that when the escape function works it then doesn't see the array values as containing quotes and therefore is not escaping them?

 

So does that mean that the data is in fact safe and the escape function is likely working?? Anyone?

 

Hmmm

 

 

Hi Guys

 

I'm a tad confused by what's going on when using real_escape_string. Could be that I'm using it incorrectly or that i'm not fully understannding it but here goes.

 

I'm trying to sanitize the post data from a form then escape it before storing it in my database. The code is as follows:

 

	$validation_options = array(


'title_2'					=>array('filter'=>FILTER_SANITIZE_STRING),
'name_2'					=>array('filter'=>FILTER_SANITIZE_STRING),
'surname_2'				=>array('filter'=>FILTER_SANITIZE_STRING),
'address_2'				=>array('filter'=>FILTER_SANITIZE_STRING),
'town_2'					=>array('filter'=>FILTER_SANITIZE_STRING),
'postcode_2'				=>array('filter'=>FILTER_SANITIZE_STRING),
'telephone_2'				=>array('filter'=>FILTER_SANITIZE_STRING),
'email_2'					=>array('filter'=>FILTER_SANITIZE_STRING),
'dob_2'					=>array('filter'=>FILTER_SANITIZE_STRING),



);


$validated = filter_input_array(INPUT_POST, $validation_options );	

      // Display results to test that it's working
echo "<pre>";
print_r($validated);
echo "</pre>";


      // Run validated array through real escape for database

$escaped = array_map('mysql_real_escape_string', $validated);
  
       // Display results to test that it's working
print_r($escaped);
echo $escaped['town_2'];

 

But here's the issue. When I used the $validated array and deliberately entered quotes or double quotes into the form and then print the results of $escaped it doesn't add slashes. However, if i make up a new test array with say

 

$testarray(

'TESTER' => "This is a 'test' and 'another test' "

);

 

and run that through the same escape function and print the results it displays the backslahes around the single quotes.

 

 

So does this mean that for some reason the $validated array is not being escaped? Or am I just getting something wrong?

 

Any help would be very much appreciated!

 

Drongo

 

PS Indicentally before anyone points this out - i incorporate the DB handler elsewhere in the code.

[Buddski]

FILTER_SANITIZE_STRING without the FILTER_FLAG_NO_ENCODE_QUOTES will encode quotes.

Which means that mysql_real_escape_string has no "physical" quotes to escape.

[Drongo_III]

Hi Buddski!

 

Thanks for that. Does that mean you should always use filter_flag_no_quotes when sanitising strings - assuming you're going to use real_escape_string after? Is that a safe way to input into the database?

 

Thanks

 

Drongo

 

 

FILTER_SANITIZE_STRING without the FILTER_FLAG_NO_ENCODE_QUOTES will encode quotes.

Which means that mysql_real_escape_string has no "physical" quotes to escape.

[Buddski]

It doesn't mean you should always use FILTER_FLAG_NO_ENCODE_QUOTES with mysql_real_escape_string, it all depends on the application.

How you want the data stored etc, some cases may call for the encoded quotes to be stored in the databases others maybe not.

I always use mysql_real_escape_string on all database inputs regardless of prior filtering/cleaning methods.

[Drongo_III]

I see that - that makes sense.

 

So the way I have filtered and escaped my input in my example above should provide a good basic level of protection against injections etc?

 

I just want to be sure i am not leaving a massive gap anywhere - quite paranoid about this sort of thing

 

Drongo

 

 

It doesn't mean you should always use FILTER_FLAG_NO_ENCODE_QUOTES with mysql_real_escape_string, it all depends on the application.

How you want the data stored etc, some cases may call for the encoded quotes to be stored in the databases others maybe not.

I always use mysql_real_escape_string on all database inputs regardless of prior filtering/cleaning methods.

[Buddski]

Seems pretty good to me

[Drongo_III]

Hurah! Thanks for your help Buddski. Much appreciated 

 

Seems pretty good to me

[Psycho]

Why do you think you need to use FILTER_SANITIZE_STRING to prevent SQL Injection?

[Drongo_III]

Well its not just sql injection its a sort of all round bit of a code to stop any nasties getting into my application. My main concern originally was whether sanitizing the data was some how conflicting with escaping it.

 

I thought (and my understanding isn't great) that sanitising input was just a necessary part to ensuring incoming data is safe. That not the case?

 

 

 

 

Why do you think you need to use FILTER_SANITIZE_STRING to prevent SQL Injection?

[Psycho]

Well its not just sql injection its a sort of all round bit of a code to stop any nasties getting into my application. My main concern originally was whether sanitizing the data was some how conflicting with escaping it.

 

I thought (and my understanding isn't great) that sanitising input was just a necessary part to ensuring incoming data is safe. That not the case?

 

It is very important to ensure user submitted data does not "damage' your site. But, you need to analyze what you are doing and how you are using the data to determine WHEN and HOW you will do that sanitation. For example, you used FILTER_SANITIZE_STRING for all of the values - why didn't you use FILTER_SANITIZE_EMAIL for the email! But, that is really beside the point.

 

You need to be very careful when imposing any arbitrary methods that will actually modify the user input. There are plenty of way to make the input safe without changing the 'intent' of the input. Rule #1 is that you always escape the input before using in a query. But, it gets trickier to determine what validations/escaping you should do for XSS, HTML tags, etc.

 

The approach I almost always take is to simply store the code exactly as the user submitted it. Then when I retrieve the code I will 'escape' it as needed. If I am using the content in a web page I will use either htmlspecialcharacters() or htmlentities() to make it safe to be displayed in the web page. But, you never know how else you may need the data in the future. Maybe an RSS feed, output to an XML file, or ??? So, if you modify the data before you store it you make it difficult, if not impossible, to re-purpose the data for other purposes.

[Drongo_III]

It's a bit of a jungle this...

 

I totally see your logic and it makes a lot of sense.

 

When you say "store the code exactly as the user submitted it" - does this mean that you'd only ever escape the code and leave it at that? No santitisation? Lets assume you were just making a simple data capture form for instance so the purpose is pretty straightforward.

 

Can you describe how you'd go about storing the data from this form so i can see how it should be done? (don't expect code or anything)

 

Thanks,

 

Drongo

 

 

Well its not just sql injection its a sort of all round bit of a code to stop any nasties getting into my application. My main concern originally was whether sanitizing the data was some how conflicting with escaping it.

 

I thought (and my understanding isn't great) that sanitising input was just a necessary part to ensuring incoming data is safe. That not the case?

 

It is very important to ensure user submitted data does not "damage' your site. But, you need to analyze what you are doing and how you are using the data to determine WHEN and HOW you will do that sanitation. For example, you used FILTER_SANITIZE_STRING for all of the values - why didn't you use FILTER_SANITIZE_EMAIL for the email! But, that is really beside the point.

 

You need to be very careful when imposing any arbitrary methods that will actually modify the user input. There are plenty of way to make the input safe without changing the 'intent' of the input. Rule #1 is that you always escape the input before using in a query. But, it gets trickier to determine what validations/escaping you should do for XSS, HTML tags, etc.

 

The approach I almost always take is to simply store the code exactly as the user submitted it. Then when I retrieve the code I will 'escape' it as needed. If I am using the content in a web page I will use either htmlspecialcharacters() or htmlentities() to make it safe to be displayed in the web page. But, you never know how else you may need the data in the future. Maybe an RSS feed, output to an XML file, or ??? So, if you modify the data before you store it you make it difficult, if not impossible, to re-purpose the data for other purposes.

[Psycho]

Like I said, it all depends on how the data would or could be used. But, most of the time I would simply use mysql_real_escape_string() before storing the data. If I did have a specific purpose where I needed to restrict certain input, then I would implement that as part of the validation logic. If there was something not kosher in the input I would not accept the input and provide an error back to the user. It is never, ever a good idea IMO to modify user input without their knowledge. For example, someone might think they are being smart to strip out any non-numeric characters for a phone number input. But, what if the user used letters in the phone number - which is perfectly valid from a human interpretation. If the phone number was simply for display purposes, then I would let them use letters. But, if the phone number was going to be used by some automated dialing application that only accepted numbers, then I would only allow numbers.

 

There is also another problem with modifying the user input - the database field length. Many processes to modify input to make it safe will increase the character length. So, if you made the input field 20 characters, you might need to make your DB field much bigger to accept the 'escaped' input.

[Drongo_III]

Thanks Mj

 

I can see more what you mean now.

 

I suppose this is a mindset you need to get into - bit like programming in general.

 

Thanks for the advice - i've learned a lot!

 

Drongo

 

 

Like I said, it all depends on how the data would or could be used. But, most of the time I would simply use mysql_real_escape_string() before storing the data. If I did have a specific purpose where I needed to restrict certain input, then I would implement that as part of the validation logic. If there was something not kosher in the input I would not accept the input and provide an error back to the user. It is never, ever a good idea IMO to modify user input without their knowledge. For example, someone might think they are being smart to strip out any non-numeric characters for a phone number input. But, what if the user used letters in the phone number - which is perfectly valid from a human interpretation. If the phone number was simply for display purposes, then I would let them use letters. But, if the phone number was going to be used by some automated dialing application that only accepted numbers, then I would only allow numbers.

 

There is also another problem with modifying the user input - the database field length. Many processes to modify input to make it safe will increase the character length. So, if you made the input field 20 characters, you might need to make your DB field much bigger to accept the 'escaped' input.