Formatted text + database insertion/retrieval

[influxer]

Hey all,

I have a site similar to any other blog site, where I have a few friends coming and posting whatever they want. I found this "Rich-text Editor" online, a WYSIWYG editor for posting to their blog...and have a few questions about database input and future output.

Since the WYSIWYG editor (found here: http://www.kevinroth.com/rte/demo.htm ) is basically a bunch of HTML code...what would I need to do before inputting it into my database to be safe?

Since my php.ini has magic_quotes_gpc set to off...would I need to mysql_real_escape_string() everything going into the database? What else would I need?

Also, when retrieving the data from the MySQL database, what would I need to do to it? Do I need to use stripslashes()? I really want this to be foolproof.

BTW: the richtext editor suggested using the following function before placing formatted text into a database, should I use this or simple PHP functions instead?


function RTESafe($strText)
{
//returns safe code for preloading in the RTE
$tmpString = trim($strText);

//convert all types of single quotes
$tmpString = str_replace(chr(145), chr(39), $tmpString);
$tmpString = str_replace(chr(146), chr(39), $tmpString);
$tmpString = str_replace("'", "'", $tmpString);

//convert all types of double quotes
$tmpString = str_replace(chr(147), chr(34), $tmpString);
$tmpString = str_replace(chr(148), chr(34), $tmpString);
//$tmpString = str_replace("\"", "\"", $tmpString);

//replace carriage returns & line feeds
$tmpString = str_replace(chr(10), " ", $tmpString);
$tmpString = str_replace(chr(13), " ", $tmpString);

return $tmpString;
}


Thanks!
-influx

[marek_mar]

It's good that magic quotes are off. They are very annoying. You havet to escape things that go into the query. You shouldn't have to do anything when you retrieve data. You always should use mysql_real_escape_string() as mySQL queries need other chars to be escaped than addslashes() escapes.

[missing-score]

To confirm, you should use mysql_real_escape_string() rather than mysql_escape_string (which is deprecated).

What I do, is always escape the string to insert into the database, regardless of gpc settings, and then when I select the data back out again I stripslashes() based on whether magic_quotes_gpc is on or off... Alot of servers have it on. To be honest, I dont mind magic_quotes_gpc, its magic_quotes_runtime that gets on my nerves (but thank the lord, that can be turned off)...

Here is the sort of thing I usually use (I always have a dedicated class for handling database connections and queries, but I will show you here with basic functions...


<?php
set_magic_quotes_runtime(0); // Turn runtime quotes off

$potentially = $_POST['value1'];
$dangerous = $_POST['value2'];
$values = $_POST['value3'];

// now, I use mysql_real_escape_string() when inserting into the database,
// regardless if magic_quotes_gpc is on or off

$query = "INSERT INTO mytable (col1, col2, col3)
VALUES
('".mysql_real_escape_string($potentially)."',
'".mysql_real_escape_string($dangerous)."',
'".mysql_real_escape_string($values)."')";

mysql_query($query); // Insert the new row


// Now, when reading from the database, I use my own custom fetch function which allows me to test for magic_quotes_gpc being on/off and if its on, strip the unnecessary slashes.

function mystripslashes($array = array()){ // Stripslashes of all values of an array
$out = array();
foreach($array as $key => $value){
$out[$key] = stripslashes($value);
}
return $out;
}

function fetch_assoc($result){
if($data = mysql_fetch_assoc($result)){
if(get_magic_quotes_gpc() == 1){ // If GPC is on....
return mystripslashes($data);
}
else // If its not on, return the normal data without stripping slashes
{
return $data;
}
}
else
{
return false;
}
}

?>

[marek_mar]

To confirm, you should use mysql_real_escape_string() rather than mysql_escape_string (which is deprecated).

Oops... that's becouse I's so lazy :p