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

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
Bri!
Posted: Tue Nov 15, 2005 3:49 pm
by Ambush Commander
This is bind SQL. Bind SQL is your friend.
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.