mysql_real_escape_string and ’

L

9

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 :)

Lenticel answered 19/1, 2011 at 19:29 Comment(0)

P

16

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.

Plight answered 19/1, 2011 at 19:33 Comment(3)

to clarify, there is a difference between ’ and ' – Plight 19/1, 2011 at 19:33

+1 Could also be a client/server encoding issue (i.e.: SET NAMES). – Betoken 19/1, 2011 at 19:54

thanks. so setting the field encoding to utf-8 should fix any problems like this? – Lenticel 20/1, 2011 at 10:15

S

4

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.

Serow answered 19/1, 2011 at 19:32 Comment(1)

This seems so obviously better than any amount of quoting, I'm stunned the PHP team hasn't removed mysql_real_escape_string() as a silly first attempt. – Provincialism 6/2, 2011 at 11:8

M

2

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.

Meara answered 19/1, 2011 at 19:33 Comment(1)

not into query but into quote-delimited string in the query. feel the difference – Syriac 19/1, 2011 at 20:3

S

1

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.

Selfimmolation answered 19/1, 2011 at 19:37 Comment(0)

A

0

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

Airstrip answered 3/2, 2011 at 15:25 Comment(0)

T

0

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.

Tramel answered 27/3, 2017 at 9:4 Comment(0)

T

-1

mysql_real_escape_string(utf8_encode($data));

Hope this will work.

Talmudist answered 6/2, 2011 at 3:28 Comment(0)

Y

-1

What would even be better is to use PDO instead of standard mysql.

http://www.php.net/manual/en/class.pdo.php

Yugoslavia answered 6/2, 2011 at 11:38 Comment(0)

C

-1

<?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

enter image description here

Cawthon answered 13/2, 2013 at 3:16 Comment(0)

Recommended topics

Hot tags