Jump to content

Mysql_real_escape_string And “” (left And Right Double Quotes)


Greywacke
 Share

Recommended Posts

hi all,the actual cause of this issue was discovered, please read the last post: http://w3schools.invisionzone.com/index.ph...st&p=165476i was wondering if anyone can help me with the following problem.

XML Parsing Error: undefined entityLocation: http://www.ferrety.co.za/fab/scripts/ajax_prospects.phpLine Number 177, Column 3:
	$sql3 = "SELECT 12_prospectmessages.bigint_MessageID, 		12_prospectmessages.smallint_ProspectMessageCount, 		12_prospectmessages.text_ProspectMessage FROM 		12_prospectmessages WHERE 12_prospectmessages.bigint_ServiceID = ".$GLOBALS["s"]." 		ORDER BY 12_prospectmessages.smallint_ProspectMessageCount ASC;\n"; // select regions	$GLOBALS["sql"] .= $sql3;	$result3 = mysql_query($sql3);	$err = mysql_error();	$GLOBALS["sql"] .= strtoupper($err)."\n";	if ($result3) {		echo "		<prospectmessages>\n";		while ($row3 = mysql_fetch_array($result3)) {			echo "		<prospectmsg id=\"".$row3["bigint_MessageID"]."\" number=\"".			$row3["smallint_ProspectMessageCount"]."\" text=\"".			htmlentities($row3["text_ProspectMessage"])."\" />\n";		}		echo "</prospectmessages>\n";	}

now i can already identify the characters - the two euros, and the funny characters after them - htmlentities does not seem to have replacements for them. any other function i could use?any way i could "trap" characters such as these so the xml files can't be "broken" anymore?furthermore these characters ended up in the database passing through the following code in an ajax interface:

		case 4:	// modify prospecting message			$num = $_POST["text_prospectingnumber"];			$msg = mysql_real_escape_string(str_replace("\n","<br />",$_POST["textarea_prospectingmessage"]));			$mid = $_POST["list_msgid"];			$tsql = "UPDATE 12_prospectmessages SET smallint_ProspectMessageCount = ".$num.				", text_ProspectMessage = \"".$msg."\" WHERE bigint_MessageID = ".$mid.";\n";			$sql .= $tsql;			$result = mysql_query($tsql);			$err = mysql_error();			$sql .= strtoupper($err)."\n";			break;

i realise now this can be repaired by doing an htmlentities parse before replacing the \n, and before the real escape.

Edited by Pierre 'Greywacke' du Toit
Link to comment
Share on other sites

Instead of writing

â

and the like, write the character itself. If this is because of htmlentities(), switch to htmlspecialchars() instead.XML doesn't define the entities present in HTML, such as the one above. It only defines the entities for "<", ">", "&", """ and "'".

Link to comment
Share on other sites

the issue was not the use of htmlentities, but the creation of those weird chars in the db content - which in turn screwed up the xml...

Link to comment
Share on other sites

You mean the raw DB content has

â

in it? Before htmlentieis() is called? Seems unlikely to me, as htmlenties() should encode the ampersand, turning it into

&acirc;

in the end.This is surely created by the use of htmlentities(). Just try to switch to htmlspecialchars(), and then let's see.

Link to comment
Share on other sites

this issue was apparently created by trying to pass a string containing the left and right slanted double quotes (“ and ”) from a form. the weird characters (“ and â€) where inserted by mysql_real_escape_string in their place it seems.how can i insert a string containing any special characters such as a double slanted quote left or right into a mysql db without using mysql_real_escape, also escaping the normal double quotes, etc?i need the special characters (“ and ”) inserted into the db without doing it manually via PMA...they insert with the phpMyAdmin but how would i be able to insert a string containing them into a table via a php, mysql query as an ajax result over POST?the current code for inserting and updating a record is:

		case 3:	// add prospecting message			$num = $_POST["text_prospectingnumber"];			$msg = mysql_real_escape_string(str_replace("\n","<br />",$_POST["textarea_prospectingmessage"]));			$tsql = "INSERT INTO 12_prospectmessages (bigint_ServiceID, smallint_ProspectMessageCount, 				text_ProspectMessage) VALUES (".$s.", ".$num.", \"".$msg."\");\n";			$sql .= $tsql;			$result = mysql_query($tsql);			$err = mysql_error();			$sql .= strtoupper($err)."\n";			break;		case 4:	// modify prospecting message			$num = $_POST["text_prospectingnumber"];			$msg = mysql_real_escape_string(str_replace("\n","<br />",$_POST["textarea_prospectingmessage"]));			$mid = $_POST["list_msgid"];			$tsql = "UPDATE 12_prospectmessages SET smallint_ProspectMessageCount = ".$num.				", text_ProspectMessage = \"".$msg."\" WHERE bigint_MessageID = ".$mid.";\n";			$sql .= $tsql;			$result = mysql_query($tsql);			$err = mysql_error();			$sql .= strtoupper($err)."\n";			break;

i can retrieve these two characters from mysql (tested this with phpmyadmin), just inserting or updating with them automatically is a biatch to put it mildly... the weird characters break the xml when inserted into the database!this issue has been confirmed to, in reality, be the result of a javascript function - so discussion has been moved to http://w3schools.invisionzone.com/index.php?showtopic=30432

Edited by Pierre 'Greywacke' du Toit
Link to comment
Share on other sites

this issue has been resolved by adding a function in the form handler to convert the messed up characters to the originally input ones.

function rep_lrdquotes($string) {	return str_replace("â€","”",str_replace("“","“",$string));}

thanks for all the attempted help!

Link to comment
Share on other sites

You may find this useful, I use this to replace the stuff that MS Office likes to use.

function sanitize_ms_chars(&$val, $i){  $find = array(	'“',	'”',	'‘',	'’',	'…',	'—',	'–',	chr(145),	chr(146),	chr(147),	chr(148),	chr(151),	chr(0xe2) . chr(0x80) . chr(0x98),	chr(0xe2) . chr(0x80) . chr(0x99),	chr(0xe2) . chr(0x80) . chr(0x9c),	chr(0xe2) . chr(0x80) . chr(0x9d),	chr(0xe2) . chr(0x80) . chr(0x93),	chr(0xe2) . chr(0x80) . chr(0x94)  );  $replace = array(	'"',	'"',	"'",	"'",	'...',	'-',	'-',	"'",	"'",	'"',	'"',	'-',	"'",	"'",	'"',	'"',	'-',	'-'  );    $val = str_replace($find, $replace, $val);}array_walk_recursive($_POST, 'sanitize_ms_chars');

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...