[SOLVED] addslashes and \n - I don't want them removed!

[netphreak]

I have a problem:

 

When inserting user submitted data from a form to MySQL there are several things to take care of, we all know that. But I have one field that gives me headache... This field will usually contain a Windows filename, like this:

C:\somefolder\somesubfolder\newfolder\somefile.txt

 

Well, I need to use mysql_real_escape_string() or addslashes(), but here comes the problem... The the examle above is inserted to MySQL like this:

C:\somefolder\somesubfolderewfolder\somefile.txt

 

See? The '\n' is removed!

 

So, my question is simple: How to prevent '\n' and '\r' to be removed when using mysql_real_escape_string() or addslashes()?

[hitman6003]

what code are you currently using?

[netphreak]

I use mysql_real_escape_string(). Like this:

	mysql_query("INSERT INTO table SET (id,field) VALUES ('','". mysql_real_escape_string($_POST[formfieldname]) ."') ");

 

 

[hitman6003]

hmmm, it shouldn't be removing the "\n", but rather escaping it...e.g. "\n" becomes "\\n".  How are you viewing the value stored in the database?  Through the browser?  If so, it may be interpreting a new line, which would not be visible execpt in "source view".

[netphreak]

Well, I check what's inserted in MySQL through the browser, yes - phpMyAdmin (running on windows, WAMP). It simply remove the \n.

 

Could this be related to the fact that I am running this on Windows?

[netphreak]

hmmm, it shouldn't be removing the "\n", but rather escaping it...e.g. "\n" becomes "\\n".  How are you viewing the value stored in the database?  Through the browser?  If so, it may be interpreting a new line, which would not be visible execpt in "source view".

 

Alright, when I look at the inserted data in source view, \n isn't removed - it breakes to a new line. This behavior isn't what I expect or want. Ideas are greatly welcome - I've been struggeling with this for days.

[quiettech]

You'll be annoyed

 

The problem has almost certainly to do with how $_POST[formfieldname] operates. Strings are enclosed inside double quotation marks. By using this, the \n portion of your string gets translated even before mysql_real_escape_string() gets to act.

 

You can test this very quickly on a separate script:

 

<?php
$conn = @mysql_connect('localhost','user','pass') or die('');
@mysql_select_db('schema_name') or die('');

$data1 = "no\no";
$data2 = 'no\no'
mysql_query("INSERT INTO test_table VALUES('".mysql_real_escape_string($data1)."')");
mysql_query("INSERT INTO test_table VALUES('".mysql_real_escape_string($data2)."')");
?>

 

Only data2 will be insert as you expect. I'm unsure as to why $_POST does this. I'm myself only starting in PHP having my experience been on other server-side language. But the solution has to come from making $_POST behave correctly...

 

Someone in here will know how.

[netphreak]

Well, but ', ", NUL and / are being handled by mysql_real_escape_string, just as they should... It's only \n and \r making trouble... They're making a new line, only visible in source view of my browser

 

There must be something basic I am missing?

[quiettech]

Yes but those string elements don't translate into escape characters. /n and /r do.

 

In PHP strings are single-quote delimited. We tend to forget that.

 

A double-quote delimited string gets its contents parsed before being stored into the variable. Any '/n' inside a double quoted string will be translated into a linefeed before being stored. On this case $_POST is suffering from that, I'm almost positive.

 

The solution however I cannot give you. Someone else here will know how to handle this.

[netphreak]

Alright - I see what you mean now. But there must be a straight forward solution for this - I mean, it should be a really common problem I believe? Thanks for your valuable thoughts on this matter quiettech

[netphreak]

I just verified that you are 100% right.

 

When I use $_POST data, \n is not escaped. When I create a variable $data = '\n'; it's escaped correctly. So please, if anyone reads this and know how a solution... I'd be able to sleep at night again.

[Cep]

My question to you is why are you storing the \n and \r in the first place? Surely you only want to store the data of the path you are using. When reading the data back out of your database you could quite easily append the \n or \r on output.

[netphreak]

As stated in the first post - this is an example of what should be entered in my form field:

C:\somefolder\somesubfolder\newfolder\somefile.txt

 

You see the \n in here, right?

[Cep]

You are referring to the fact that \newfolder is being escaped as opposed to being treated literally as part of the string?

 

Are you enclosing the string in double or single quotes?

[Cep]

Don't do this,

 

mysql_query("INSERT INTO table SET (id,field) VALUES ('','". mysql_real_escape_string($_POST[formfieldname]) ."') ");

 

Do this,

$mystring = mysql_real_escape_string($_POST[formfieldname]);

mysql_query("INSERT INTO table SET (id,field) VALUES ('Null',$mystring) ");

 

See what happens.

[netphreak]

Thank you for the tip. Unfortunately I've already tried this, it doesn't help.

[netphreak]

You are referring to the fact that \newfolder is being escaped as opposed to being treated literally as part of the string?

 

Exactly!

[Cep]

Have you tried the code I presented earlier? If so, please post your script, there must be something else going on.

[netphreak]

Here's an exact cut'n'paste from the actual part. Sorry about the norwegian writings, the meaning is not interesting I believe

 

Notes is the field to look for.

 

<?php
//################################# save customer employees #############################################
if ($_GET['action'] == editemployee) {

$query = mysql_query("SELECT customers.company,customers_employees.* FROM test.customers LEFT JOIN test.customers_employees ON customers_employees.company_id = customers.id WHERE customers_employees.id = $_GET[id]"); 
$row = mysql_fetch_array($query);
echo "<center><br><h2>Redigere ansatt hos $row[company]:</h2></center>";
?>
<div id="fel">
<form action="index.php" method="POST"><input type="hidden" name="action" value="doeditedemployee">
<input type="hidden" name="customerid" value="<?php echo $row[id]; ?>">

<br>
<div id="notd001"><label style="width:74px" for="f_name">Fornavn:</label><input style="width:141px" type="text" tabindex="1" name="f_name" value="<?php echo $row[f_name]; ?>"></div>
<div id="notd002"><label style="width:65px" for="phone">Telefon:</label><input style="width:134px" type="text" tabindex="3" name="phone" value="<?php echo $row[phone_work]; ?>"></div>
<div id="notd003"><label style="width:50px" for="mail">Epost:</label><input style="width:204px" type="text" tabindex="5" name="mail" value="<?php echo $row[mail]; ?>"></div>
<br>
<div id="notd001"><label style="width:74px" for="l_name">Etternavn:</label><input style="width:141px" type="text" tabindex="2" name="l_name" value="<?php echo $row[l_name]; ?>"></div>
<div id="notd002"><label style="width:65px" for="mobile">Mobil:</label><input style="width:134px" type="text" tabindex="4" name="mobile" value="<?php echo $row[phone_mob]; ?>"></div>
<br><br>
<center>Ekstra informasjon om kunden:</center>
<br>
<textarea id="notes" name="notes" rows="15" cols="70" style="width: 100%"><?php echo $row[notes]; ?></textarea>
<br><center><input type="submit" name="submitbutton" value="Legg til ny ansatt"></center>
<br>
</form></div>

<?php
}

//################################# save edited customer employee to db #############################################
if ($_POST['action'] == doeditedemployee) {
$notes = mysql_real_escape_string($_POST[notes]);

mysql_query("UPDATE test.customers_employees SET company_id='$_POST[customerid]',f_name='$_POST[f_name]',l_name='$_POST[l_name]',phone_mob='$_POST[mobile]',phone_work='$_POST[phone]',mail='$_POST[mail]',notes=$notes,up_date='". time() ."' WHERE id = $_POST[customerid]");
echo "Thank you";
}
?>

[quiettech]

There must be something else affecting your results, netphreak. I've prepared the following script: It tries to reproduce your problem. However, it works as expected and not as you are experiencing. Maybe you should try and compare results.

 

Create a test table as:

 

CREATE TABLE `test` (
  `name`  varchar(20),
  `id`    int AUTO_INCREMENT NOT NULL,
  PRIMARY KEY (`id`)
);

 

Use this:

 

<?php
$conn = @mysql_connect('localhost','root','password') or die(''); // put your credentials in

@mysql_select_db('schema_name') or die(''); // alter with your schema

if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    echo $_POST['text'];
    $res = mysql_query("INSERT INTO test (`name`) VALUES ('".$_POST['text']."')");
}
else {
    echo 'Not submitted yet.';
}

?>

<form name="myform" action="test.php" method="POST" target="_self">
    <input name="text" />
    <input type="submit" name="submit" value="submit" />
</form>

<?php
    if($res)
        echo 'Inserted as: ',
        mysql_result(mysql_query("SELECT * FROM test ORDER BY id DESC LIMIT 1"), 0);
    else
        echo 'Not inserted';
?>

 

What you want is to change the INSERT to work with and without mysql_real_escape_string().

These are my results:

 

Without mysql_real_escape_string()

Input: c:\test\name

echo $_POST['text'] : c:\\test\\name

Database contents: c:\test\name

echo SELECT: c:\test\name

 

With mysql_real_escape_string()

Input: c:\test\nome

echo $_POST['text'] : c:\\test\\nome

Database contents: c:\\test\\nome

echo SELECT: c:\\test\\nome

 

This is what I would expect to happen. Now... I'm no expert on PHP. Still learning the language. So this is my take:

 

- If you get different results than the POST echo above, there is some PHP setting that is affecting your results. We can rule out a mysql settings problem.

 

- If you get a different result than that on your database contents, it's your mysql installation that is at fault. If this is the case we can eliminate PHP from the start and concentrate there.

 

- If you get a different SELECT echo result, and yet the POST echo and the database contents is as expected, this is also a mysql settings problem.

 

So what are your results?

[Cep]

Try

 

<?php
$notes = str_replace("\\", "-", $_POST['notes']);

$notes = mysql_real_escape_string($notes);
?>

 

Then when you read the data out in your text area do this.

 

<?php
echo str_replace("-", "\\", $row['notes']);
?>

 

Its dirty but test it.

 

 

[netphreak]

quiettech:

 

Thank you for your effort trying to help! I run your script exactly as you wrote it, here are the results:

 

1: Without mysql_real_escape_string()

Input: c:\test\name

echo $_POST['text'] : c:\test\name

Database contents: c:    est

                            ame

echo SELECT: c: est ame

 

2: With mysql_real_escape_string()

Input: c:\test\nome

echo $_POST['text'] : c:\test\nome

Database contents: c:\test\nome

echo SELECT: c:\test\nome

 

 

---

 

So, it's a MySQL issue then?

 

What I find odd is that the data is actually insertet just the way I want with mysql_real_escape_string() in your script - how come it doesn't in mine?

 

Here's your script with mysql_real_escape_string(). I simply removed mysql_real_escape_string() for test result 1

<?php

include '../job/config.php';


if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    echo ($_POST['text']);
   $res = mysql_query("INSERT INTO test.test (id,name) VALUES ('NUL','" .mysql_real_escape_string($_POST['text']). "')");
}
else {
    echo 'Not submitted yet.';
}

?>

<form name="myform" action="!.php" method="POST" target="_self">
    <input name="text" />
    <input type="submit" name="submit" value="submit" />
</form>

<?php
    if($res)
        echo 'Inserted as: ',
        mysql_result(mysql_query("SELECT * FROM test.test ORDER BY id DESC LIMIT 1"), 0);
    else
        echo 'Not inserted';
?>

[GingerRobot]

The difference in results between the two tests is caused by the magic_quotes setting. It would seem that quiettech has it turned on, whilst you have it off.

 

Surely your 2nd test is correct netpreak?

[netphreak]

I know I have them turned off, at least phpinfo() tell me so...

 

Yes, I just tried #2 again - the exact script I posted gives those results.

[GingerRobot]

Isn't the results of your 2nd test what you are after? It looks fine to me.