Page 1 of 1

problem with carriage returns and new lines

Posted: Tue Nov 15, 2005 11:33 am
by s.dot
I have a standard textarea:

Code: Select all

<textarea name="message"></textarea>
I don't want to use nl2br() on $_POST['message']. However when I use mysql_real_escape_string() for inserting it into the database it shows up like this

Code: Select all

line 1 \r\n line 2 \r\n line 3\r\n
It shows up like that I'm guessing because mysql_real_escape_string escapes the backslash as well.. which makes sense.

So the only thing I could think of to solve this problem was doing this:

Code: Select all

$message = str_replace("\r","",$_POST['message'];
$message2 = str_replace("\n","",$message);
$message3 = mysql_real_escape_string($message2);
My problem seems almost too simple. Am I doing something backwards or is this a good way of doing it?
(PS: I don't want/need new lines)

Posted: Tue Nov 15, 2005 11:55 am
by Burrito
If you're just outputting to HTML, you don't need to worry about replacing anything. The browser won't parse the \r\n to create a new line.

If you just want to remove the \r\n for cleanliness sake, what you're doing should work fine.

Posted: Tue Nov 15, 2005 12:08 pm
by s.dot
you're right, it won't parse the \r\n
but when I escape the string it shows up as \\r\\n in the database, then when I stripslashes() on the output it shows up in plain text as \r\n ... quite annoying

i think its an issue with my magic quotes, should escaped content show the backslashes in the database?

Posted: Tue Nov 15, 2005 1:34 pm
by Burrito
well you piqued my interest here so I tested out what you're saying:

I do not get any of the \r\n showing on this test. It simply just puts all of text together (w/o line breaks)...

so I dunno what to tell you. Here's the test I ran:

Code: Select all

<?
mysql_connect("localhost","user","pass")
	or die(mysql_error());
mysql_select_db("db")
	or die(mysql_error());
if(isset($_POST['blah']))
	mysql_query("insert into test (stuff) values ('".mysql_real_escape_string($_POST['blah'])."')");
	
$result = mysql_query("select * from test")
	or die(mysql_error());
?>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

<html>
<head>
	<title>Untitled</title>
</head>

<body>
<form method="post">
<textarea name="blah"></textarea><input type="submit">
</form>
<?
while($row = mysql_fetch_assoc($result))
{
	echo stripslashes($row['stuff'])."<br><br>";
	echo $row['stuff']."<br><br>";
	echo nl2br($row['stuff'])."<br><br>";
}
?>

</body>
</html>

Posted: Tue Nov 15, 2005 1:42 pm
by redmonkey
This is usually the case if you are running with magic_quotes on as the $_POST var will already be escaped prior to you running it through escape_string.

Posted: Tue Nov 15, 2005 1:44 pm
by s.dot
exactly, redmonkey

so if i use mysql_real_escape_string() I am unescaping the escaped data and inserting unsafe data?

Posted: Tue Nov 15, 2005 1:55 pm
by Ree
Simply check if magic_quotes_gpc is on, and if it is, stripslashes() the value, if not, leave it as it is. Then use mysql_real_escape_string().

Posted: Tue Nov 15, 2005 1:58 pm
by jayshields
what Ree said is bang on.

Posted: Tue Nov 15, 2005 2:03 pm
by s.dot
okay i know that now :P thanks

however all the hundreds of forms that i've used it on before, i didn't do that... wow, lots of work ahead of me.

Posted: Tue Nov 15, 2005 2:56 pm
by Ambush Commander
You might want to just automatically clean all POST/GET/COOKIE data with a script that automatically runs before main execution. Here's a non-recursive deep array stripslasher:

Code: Select all

function _mqQuotes($a) {
        
        //Return value - the array
        $r = array();
        
        //Array pointers that allow us to work with arbitrary depths in arrays
        $rPointer =& $r;
        $aPointer =& $a;
        
        //References to higher up portions of array
        $stack = array();
        
        //Names of array that was recursed on the level
        $stackArray = array();
        
        //Level we are in array depth-wise
        $level = 0;
        
        while (true) {
            
            $recurse = false; //this tells us whether or not we skip into
                              //hyperdrive and simulate a recursive call.
            
            //Loop through all values of the array
            foreach($aPointer as $k => $v) {
                
                //If it is an array, simulate the recursive call
                //Check and make sure we haven't already operated on this array
                if (is_array($v) &&
                  (!isset($stackArray[$level]) || $stackArray[$level] != $k)) {
                    
                    //Since we're recursing, set our stacks
                    $stackArray[$level] = $k; //the current key that is operated
                    $stack[$level] =& $aPointer; //set reference
                    
                    $aPointer =& $aPointer[$k]; //set pointer deeper in array
                    
                    $rPointer[$k] = array(); //make an empty array in return
                    $rPointer =& $rPointer[$k]; //...then point to it
                    
                    $level++; //increase our level in the array
                    $recurse = true; //tell the while to continue
                    break;
                }
                
                //////////////////////////////////////////
                //ACTUAL OPERATION!
                //Only lower level keys had slashes added
                $nk = !$level ? $k : stripslashes($k);
                $rPointer[$nk] = stripslashes($v);
                unset($aPointer[$k]);
                //////////////////////////////////////////
                
            }
            if ($recurse) { //simulate recursion
                continue;
            }
            if ($level > 0) { //if we are not on base level, move shallower
                $level--;
                $aPointer =& $stack[$level];
                unset($stack[$level + 1]);
                unset($aPointer[$stackArray[$level]]); //rm the array we were in
                unset($stackArray[$level]);
                continue;
            }
            break;
        }
        
        return $r;
    }

Posted: Tue Nov 15, 2005 3:45 pm
by trukfixer
what we have done for work, is we turn off magic_quotes_gpc (evil stuff) and in any php script I write for work, I also ini_set magic quotes off, and then we run a safe_query() function
(this code is not proprietary - it's free to use by anyone - it's so simple, anyone could use it anyway.. and it does an *EXCELLENT* job of emulating BIND VARIABLES, and completely eliminating SQL injection...

Code: Select all

function safe_query($query, $values, $link) {
   $query_parts = preg_split("/\?/", $query);
   $safe_query = array_shift($query_parts);
   $needed_values = count($query_parts);
   $ii=count($values);
   foreach ($values as $value) {
       $value = "'" . mysql_escape_string($value) . "'";
       $safe_query .= $value.array_shift($query_parts);
   }

   if (count($query_parts)) {
       die ('Query "<i>'.$query.'</i>" needs'.
           $needed_values.' values, you only sent '.$ii);
   }
   return mysql_query($safe_query, $link);
}
 //the above function used thusly 

$sql = "INSERT INTO table (name,type,desc,whatever) VALUES (?,?,?,?)";
$sql_array = array($name,$type,$desc,$whatever);
$result = safe_query($sql,$sql_array,$db_resource_link);
It works very very well.. I would suggest everyone use it in their code, and forget completely about worrying if magic_quotes_gpc is on, and with some basic cleanup work, no worries about sql injection attacks too :)

Ive tested this with every possible combination of SQL injection that I could come up with or find on the web, and have yet to find anything to break the query...

If someone can find a way to sql inject something into safe_query, I'd definitely like to know about it :-D

Bri!

Posted: Tue Nov 15, 2005 3:49 pm
by Ambush Commander
This is bind SQL. Bind SQL is your friend. :D

magic_quotes_gpc cannot always be turned off (usually, this is the case in shared environments), so circumvention is often necessary. But yes, an interpreter level solution is optimal.