mysql_real_escape_string and search data in mySql DB

Question

I have problem with php function : mysql_real_escape_string

My test string:


    @,&!#$%^*()_+' "\/

I add this data to mySql database, like that (in short):


    $str = mysql_real_escape_string($str);

    $sql = "INSERT INTO table(company) VALUES('".$str. "')";

In DB is stored as:


    @,&!#$%^*()_+\' \"\\/

But problem is with find this data by SELECT statement.

I want find, company where name is like

' "

My SELECT's:

    SELECT company FROM table WHERE company LIKE '%\' "%';
    SELECT company FROM table WHERE company LIKE '%\\' \\"%';

; not working.

This works:


    SELECT `company` FROM `table` WHERE `company` LIKE '%\\\' \\\\"%';

    and

    SELECT `company` FROM `table` WHERE `company` LIKE  '%\\\\\\\' \\\\\\\"%'

But I dont know why this work :(.

My questions are:

why must add so many slashes ?
how I can make correct query in PHP:


    $query = '\' "';
    '%'.mysql_real_escape_string($query).'%' 
    result is : '%\' \"%'

    '%'.mysql_real_escape_string(mysql_real_escape_string($query)).'%'
    result is : '%\\\' \\\"%'

    '%'.mysql_real_escape_string(mysql_real_escape_string(mysql_real_escape_string($query))).'%' 
    result is : '%\\\\\\\' \\\\\\\"%'

Only last one works good.

simply go through the addslashes() manual http://php.net/manual/en/function.addslashes.php — user2092317, Oct 22 '13 at 15:40
If you use `like` you have to escape the string twice. If you use equals, you only need to do it once. I'm not sure why exactly but I just know it needs to be done like that.... and I'm also not sure why you need to do this a third time, though...? You should really use the mysqli_ or PDO functions b/c they are going to remove mysql_ from PHP entirely soon. (this might also be an issue with `get_magic_quotes_gpc`) — gloomy.penguin, Oct 22 '13 at 15:52
I can not change that (mysql_*). I changed just a piece of software, I have no influence on the methods used to communicate with the database. — ryrysz, Oct 22 '13 at 16:00

score 0 · Answer 1 · answered Oct 22 '13 at 15:51

0

Why you are using old method since it won't support from PHP team!

Lets try using PDO! it would do it for you with bindvalue.

It's secure much more than the old method

$db_conn = new PDO('mysql:host=localhost;dbname=mydb',
'user', 'pass');
} catch(PDOException $e){
    echo 'Cannot Connect to the DB!';
}

$sql = 'SELECT name, description
FROM recipes
WHERE chef = :chef';
$stmt = $db_conn->prepare($sql);
$stmt->bindValue(':chef', 'Lorna');
$stmt->execute();

that's it and there is no need to use mysql_real_escape_string()

Lets have a try

answered Oct 22 '13 at 15:51

absfrm

352
2
14

Because it's a part of an old project that is not yet using PDO – ryrysz Oct 22 '13 at 15:55
1

It's important that whenever the server is updated , your project wont work correctly. by the way , I recommend you to use `str_replace('"\\','"\')` after `mysql_real_escape_string()` – absfrm Oct 22 '13 at 16:07
Thans Mr.Farahmand. Problem was in double escaping of my string (in other place in program). But You simple tip with str_replace() helps me very much. Because, searching of char \ strill not worked. In my case I used :
```
$query = str_replace('\\','\\\\', $query);
```
+1 for You :)
– ryrysz Oct 23 '13 at 09:34

mysql_real_escape_string and search data in mySql DB

1 Answers1