I'm using mysql_real_escape_string to escape a string before inserting it into my mysql database.
Everything's working fine, except that the character ’ is getting missed and turned into ’ by mysql.
What can I do to get solve the problem? Should i be using a better function to escape the string?
I'm also worried that other charachters might be getting missed and being similarly turned into nonsense!
Please help!
Thanks :)
The character ’ is not getting missed, it is simply a character that is not used by mysql for encasing strings, and does not need to be escaped.
The reason it is turning into that strange string is because it is a multi-byte character, and you are putting it into a single byte field.
You should be using prepared statements with bind variables instead: http://php.net/manual/en/pdo.prepared-statements.php This way you don't have to worry about escaping anything. The advantages are mentioned in the documentation I linked to.
mysql_real_escape_string() simply escapes a handful of characters (using \'s) to make them "safe" to shove into your query. You appear to have an encoding mismatch with your data (the styled quote) and your column encoding type. mysql_real_escape_string will never resolve that kind of issue.
Is that a fancy quote? If so, it probably looks like gibberish in your database due to character encoding differences. Each table has an associated character encoding, and the connection has its own encoding.
Try executing "SET NAMES utf8" before your query. That will set the encoding of the connection to UTF-8. Of course, if you are trying to store UTF-8 characters into, say a latin1 table, you will still not get the result you expect.
It will work in case you had established the mysql connection with: mysql_query ("SET NAMES 'utf8'");
In other words, if SET NAMES 'utf8' is not set, utf8_encode is not needed.
That is a special character for this you need to use UTF Encoding
Place this line at the top of the page where you are inserting the data in database
header ('Content-type: text/html; charset=utf-8');
Hope it works
<?php
if(isset($_GET['submit']))
{
mysql_connect('localhost','root','');
mysql_select_db('test');
$var=mysql_real_escape_string($_GET['asd']);
$sql="INSERT INTO `test`.`asd` (`id` ,`name` ,`desc`)VALUES ('', '$var', 'knkk');";
echo $sql."<br />";
$res=mysql_query($sql) or die('error');
echo $res;
}
?>
<html>
<body>
<form name="f1" method="get">
<input type="text" name="asd">
<input type="submit" name="submit">
</form>
</body>
</html>
Output:
INSERT INTO test.asd (id ,name ,desc)VALUES ('', 'asd\'lgh', 'knkk');
1
mysql_real_escape_string(utf8_encode($data));
Hope this will work.
