Retaining carriage returns in form data

juliamfg · January 14, 2010, 11:53am

I am using the following code (recommended by Kevin Yank in his php/mysql book) to keep apostrophes in my form data when entered into a mysql database.

<?php
if(get_magic_quotes_gpc())
{
	function stripslashes_deep($value)
	{
		$value = is_array($value) ?
		array_map('stripslashes_deep', $value):
		stripslashes($value);
		return $value;
	}
	$_POST=array_map('stripslashes_deep', $_POST);
	$_GET=array_map('stripslashes_deep', $_GET);
	$_COOKIE=array_map('stripslashes_deep', $_COOKIE);
	$_REQUEST=array_map('stripslashes_deep', $_REQUEST);
}
?>

This works fine for apostrophes etc but now I have a problem with retaining carriage returns in the address fields of my form. Before adding the above code to solve the apostrophe problem I was using the following in my form

<?php echo str_replace('\\r\
', ' ',$inv_address); ?>

which of course does not solve the problem now and I simply get an extra rn between what should be lines in my address field. Before using the magic_quotes code above I tried many ways to retain new lines in my address fields and this was the only way that worked. Please can someone suggest the correct code to sue now?
Thanks

system · January 14, 2010, 12:17pm

this code will do nothing to carriage returns.
because str_replace('\r
', ’ ',$inv_address) will do nothing to the carriage returns for it replace four symbols

\\r\

not 2 CR\LF symbols

I’d suggest to remove this line from the code, because your carriage returns do not need any special treatment

juliamfg · January 14, 2010, 1:16pm

Well, I had already tried just a simple echo $address leaving out the stripslashes stuff and I get something like
Chestnut GrovernElder Road
where rn should be a new line as entered on the form, it goes into the database like this too. But I can still have my apostrophes!! So, so far I can’t have both apostrophes and new lines only one or the other depending on which piece of code I include. that is really annoying and there must be a way of doing this but I haven’t found it yet and was wondering whether a Forum member had a suggestion.

system · January 14, 2010, 1:42pm

you’re doing to your data something unusual
because code you provided here cannot have such effect.

actually it is quite complicated to write a code which turns CR-LF to “rn”
hmm. Are you doing you something like mysql_real_escape_string before all this code?

I am using the following code to keep apostrophes in my form data

Actually this code does nothing to apostrophes. It removes unnecessary backslashes, if any.

Before adding the above code I was using the following in my form
<?php echo str_replace('\r
', ’ ',$inv_address); ?>

why did you use it?
this code looks like totally useless
because there is no reason to replace string \r
with a space and this code does nothing to carriage returns

juliamfg · January 14, 2010, 4:11pm

While I bow to your obviously superior knowledge:
Before I used the code you are saying does nothing I wasn’t using anything, just plain echo $value and in the case of the address text box in the form, all newlines were ignored so that the address just ran onto one line, which is what I didn’t want. I looked up various methods of overcoming this and avoiding lots of \r
in the address, which just became more \\s when the data was re-entered into the database when doing a resubmit of the form and the code I have given is the only one that worked perfectly. So, it must work!! I didn’t write it myself so it’s not an idiot’s code. However, as I explained, once I introduced the magic_quotes code to retain apostrophes (by getting rid of the unnecessary backslashes magic_quotes puts around them), the new line code didn’t work but nor does leaving it to echo $value, which logically it should. Other than that it is simply a mysql query to a database to get data and present it as an editable form which then gets checked for errors if changes are made and then re-submitted to the database if changes have been made to the original. I am not using mysql_real_escape_string. If I use an apostrophe in the address, the backslashes are removed from that and so it is retained but the backslashes are removed from what would have been \r
and simply become rn where there should be new lines. So, my conclusion is that the backslashes are being removed but something else is needed to retain the the newlines

system · January 14, 2010, 4:48pm

This riddle needs to be sorted out

Some premises out of my superior knowledge:

Normally newlines doesn’t need any special treatment. When you wrap a text with newlines into <textarea></textarea> tags, all newlines are ok. You can easily test it out yourself. So, in your case newlines being removed by some code. We have to find out, what code does it.
The string \r
does not represent a newline. It’s just a string. As I type it in the textbox, it does not turn to a newline when displayed. So. If you have it in your data, some PHP code put it there. We have to find out, what code does it (Up to my knowledge, only one function which does it is mysql_[real_]escape_string()).
You ought to use mysql_real_escape_string() unless you’re using another method for SQL query building.

I’d suggest to get rid of all the code you’re used for text preparations and start over.

juliamfg · January 15, 2010, 12:55pm

I have taken out both the magic_quotes code and the new line stuff and tried various things. I think I know where the problem is but I don’t know what is causing it and how to fix it.

I am using mysql_real_escape_string (and was before, sorry!), it’s in one of my include files.

foreach ($_POST as $key => $value) {
    $_POST[$key] = mysql_real_escape_string($value);
  }

The code (too long to reproduce here) looks up a customer’s details and prints them on the page (from mysql database). The user can then use those details unchanged or edit them. If there is any editing, checking for errors takes place.
If there are no errors when the customer changes their details, they are then asked to check the changes and continue or edit further. If there are errors on submission of any changes, the form is returned with error messages. The error checking seems to be the problem - if there are errors then the data in the address fields is reproduced with \r
instead of a newline and any apostrophes have a backslash preceding them. If the form is resubmitted with errors and the backslashes are not edited out first these backslashes multiply fivefold!
So, if there are no errors anywhere in the process, the text fields remain with the newlines intact as do apostrophes. BUT if there are any errors anywhere in the form, the data is not submitted to the database and the fields are returned with the problems above.
A sample of my error checking code - this is for the address field:

 if (array_key_exists('inv_address', $_POST) && is_string($_POST['inv_address'])) {
    $inv_address = trim($_POST['inv_address']);
    if ($inv_address == '') {
      $errors['inv_address'] = 'You must enter an invoice address.';
    } elseif (strlen($inv_address) < 20 || strlen($inv_address) > 100) {
      $errors['inv_address'] = 'The invoice address must be between 20 and 100 characters.';
    } 
  }

and this is the part of the form where the error message will appear:

<tr> 
            <td width="150" class= "label">Invoice Address:</td>
            <td class= "content">
            <?php if (array_key_exists('inv_address', $errors)): ?>
            <p class= "error"><?php echo $errors['inv_address']; ?></p>
            <?php else: ?>
             <?php endif; ?>
            <textarea name="inv_address" cols="30" rows="4" wrap="hard" id="inv_address"><?php echo $inv_address; ?></textarea>
            </td>
            </tr>
            <tr>

Can you see any obvious problem that is causing the backslashes and \r
??
I am out of ideas!
thanks

system · January 15, 2010, 1:13pm

the address fields is reproduced with \r
instead of a newline

this is because mysql_real_escape_string() being used in the wrong place
it must be used right before SQL query construction. No functions should be applied to the data after it.
And, of course, should not be used is no data goes to the SQL query. So, it shouldn’t be in the any include file. It must be used strictly in-place. But strictly for every query in your code!

After you settle it, I hope your newline problem will disappear.

any apostrophes have a backslash preceding them

This is because magic quotes. And the code from your first post is OK to solve.
But it must be used at the very top of the data processing, before anything else.
In every script. So, putting it in the file which got included above anything else is a good idea.

Hope this helps

system · January 15, 2010, 3:01pm

in short, mysql_real_escape_string(), placed in the wrong place, replaces newlines with \r\

and then stripslashes_deep, placed in the wrong place, replaces \r
with rn
So, it must be sorted

juliamfg · January 15, 2010, 4:50pm

Oh dear! I thought that would be the answer but it isn’t!
I put the magic_quotes stuff in my config include file and the following code before each mysql query:

foreach ($_POST as $key => $value) {
    $_POST[$key] = mysql_real_escape_string($value);
  }

I assume that is the correct place to put it?
But when I have an error in the completion of form, then the backslashes and \r
s are back. And then when I think about it surely neither of the above (magic_quotes, mysql_real_escape_string) can be the answer because the error checking takes place in the php code BEFORE submission to the database? As I said previously, if there are no errors in completing the form, the backslashes and newline problems do not occur - so the data from the form is checked, found error-free and submitted to the database and then sent back to the form for the user to check again. If errors are found, the data is simply sent back to the form for

juliamfg · January 15, 2010, 5:04pm

Oh dear! I thought that would be the answer but it isn’t!
I put the magic_quotes stuff in my functions include file and the following code before each mysql query like this:

foreach ($_POST as $key => $value) {
    $_POST[$key] = mysql_real_escape_string($value);
  } 
$sql = "INSERT INTO tbl_customer (company, contact_name, telephone, email, inv_address, inv_country, inv_postcode, payment_type, terms_conditions, region, del_address, del_country, del_postcode, currency, customer_vat, media) VALUES ('$company', '$contact_name', '$telephone', '$email', '$inv_address', '$inv_country', '$inv_postcode', 'payment in advance', '', '$region', '$del_address', '$del_country', '$del_postcode', '$currency', '', '$media')";
  $result = mysql_query($sql) or die ('Error, query failed');

I assume that is the correct place to put it?
I have simplified things by submitting the field data, where it is checked for errors and if there are no errors it goes to another file and if there are errors, the form is re-displayed with the data that was entered plus error messages. So, if there are any errors, the data does not get submitted to the database until the form is error free. If there are errors, with code as above, the apostrophe appears correctly but the new lines are replaced by rn with no space between the words at the beginning and end of what should be lines. If there are no errors, the data is submitted to the database like that.
Can you see what is wrong? I have attached the whole file so you can see what should happen, hope oyu don’t mind. This is driving me mad!!

system · January 15, 2010, 10:15pm

The situation is still vague to me.
But at least I can say that foreach mysql_real_escape_string code does nothing to the data that goes to SQL query.
Because it escapes $_POST[‘company’] variable, and does nothing to the $company, which used in the query.

You didn’t attach any code, by some accident I think.
Anyway, I strongly recommend you to write a sample application, and tune it until it worked well.
The data flow in it must be simple and clear, with only two rules:

stripslashes_deep code applied unconditionally with every script call, at the very top of it.
mysql_real_escape_string code used only to escape a variable going to the query.

And everything should be fine. If not - we could repair this small code here.
If yes - it rules can be applied to the big part.

juliamfg · January 18, 2010, 11:57am

Sorry about the attached code, don’t know what happened. I have attached a simple version of the code I thought I had attached.
I find that

when the data is first fetched from the mysql table, if the contact_name and/or the address field have apostrophes, each apostrophe is preceded by a backslash, even though there are no backslashes in the mysql data. Carraige reruns in the address field are also ignored.
if I then correct the contact_name and address fields or change them but make an error in the email field (simply so that I can check the backslash situation) the name field is returned with the an apostrophe and no backslash while the address field has a backslash in front of any apostrophe and newlines are ignored. At this stage of course the data has not been submitted to the database because of the error checking.
Can you see what is wrong?
By the way, the two include files only contact database connection information, nothing else.

system · January 19, 2010, 12:25pm

You’re still falling into same pit.
I suspect you have mysql_real escape string in one of your includes. Before stripslashes

And you don’t use mysql_real_escape_string in this script. I can’t see where $address variable got properly escaped.
The only use of mysql_real_escape_string in your code is wrong. Because it works only if value enclosed in quotes, but your $customer_id variable doesn’t

Also I notice this strange snippet of code:

[B]$inv_address[/B] = trim($_POST['address']);

but
if ($address == ‘’) {

juliamfg · January 19, 2010, 2:04pm

The strange snippet of code was a typo, as I’m sure you realised. I took some bits of the code from my much larger file to illustrate what I am trying to do, inv_address in the larger file should have become address in the small one and I missed one of them. Actually the error checking still worked as I obviously tried to get the newlines and backslash stuff to work with a smaller file as you had suggested. I can assure you that there are no mysql_real_escape_strings in the two include files, they only include some datbase connection stuff and nothing else. So, if I am doing it wrong could you possibly show me how I should be doing things? To say something is wrong without an example of how to do it correctly is not very helpful, so please could you provide an example of how I should be writing my code?
thanks

system · January 19, 2010, 3:28pm

I am a programmer and I don’t “realize” anything. Only thing I can trust is my own eyes.

Under smaller file I meant really small, 8 lines of code, not 8 kilobytes.
At first it must be just a simple form of one textarea field that displays it’s content after submit.
At the next step some database must be added, to store that value and displaying it back.

To say something is wrong without an example of how to do it correctly is not very helpful

I beg my pardon but you have plenty of examples already. But without understanding it, as we can see, examples help nothing. Just put right example upside-down and it fails!

For the SQL syntax it’s simple. There are several ways to compose a query.
For the mysql_real_escape_string way it is just 2 simple rules:
Each literal goes to the mysql query must be enclosed in quotes and have special characters escaped with backslash. Example:
SELECT * FROM table WHERE address='Bourbon\‘s street’;
(yes, there must be backslash but you’ll never see it again)
PHP code to make it dynamic:

$variable=mysql_real_escape_string($variable);
$sql="SELECT * FROM table WHERE address='$variable'";

if there are more literals to a query, each of them must be treated same way.
Of course, mysql_real_escape_string should be the very last thing applied to the data, and must be done right before query composing.

It was only SQL right syntax rules. It does nothing to newlines and apostrophes. But must be followed unconditionally.

With your newlines and backslash stuff you desperately need to start debugging. the only technique that can help a programmer.

make a line of code like this

echo "<br><br><b><font color=red>$_POST[address]</font></b><br><br>";

and put it after nearly each line of your code.
Run it and see how it changed at length of your script. That’s the way to find the problem code that spoils your data.

Also run phpinfo() function to see actual values of magic_quotes_gpc and magic_quotes_runtime settings.

juliamfg · January 20, 2010, 3:58pm

Problem solved!
All I needed to know was how I should be using mysql_real_escape_string and exactly where the magic_quotes code should go. I understand perfectly well how to make a mysql query etc - if you’d remembered my original email it stated that everything worked perfectly well except when there were newlines and apostrophes which I wanted to keep correctly when they went into the database and were retrieved in a form. Now you have told me that every literal in a query should be used with mysql_real_escape_string I have done that and my new lines and apostrophes work fine.