Solved

Trouble in mysql_real_escape_string is it BUG? Why i have to use it twice?

Posted on 2008-10-09
11
335 Views
Last Modified: 2013-12-12
Hello ,

in my scripts when i use mysql_real_escape_string or addslashes and echo variables they take effect
but when i add them to database then slashes getting lost, i dont see slashes in database  
but when i use twice time mysql_real_escape_string or addslashes i see one slash in database
WHY? ANYBODY KNOWS?
<?php
mysql_connect("localhost","username","password");
mysql_select_db("saple_database");
/*
My database table charset utf8_general_ci
magic_quotes_gpc = Off
*/
 
if($_REQUEST['add']!=""){
$name=mysql_real_escape_string($_REQUEST['name']);
$result=mysql_query("insert into sample_table (name) values ('$name')");
//There is a problem when i input IT'S mysql_real_escape_string converts IT\'S all things is ok 
//but when i look to table i see here IT'S not converted , 
//i tried mysql_real_escape_string(mysql_real_escape_scring($_REQUEST['name'])) then add again to table then i see converted //IT'\S why i have to use mysql_real_escape_string twice
}
?>
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=utf-8">	
</head>
<body>
<form action="form.php" method="post">
<input type="text" name="name"><br><input type="submit" name="add" value="OK">
</form>
</body>

Open in new window

0
Comment
Question by:phparmy
  • 6
  • 5
11 Comments
 
LVL 48

Expert Comment

by:hernst42
ID: 22676278
So you input "IT'S" amd expect in the database the value "IT\'S"? That does not make sense. As the db contains "IT'S" ist absolute corrct, why should the db contain "IT\'S" ??
>but when i look to table i see here IT'S not converted.
That the correct behaviour.
0
 

Author Comment

by:phparmy
ID: 22676302
after using mysql_real_escape_string i echo the variable and see IT'S is IT\'S then variable is equal to IT\'S and in database it must be IT\'S why slashes is lost when i insert into database
0
 

Author Comment

by:phparmy
ID: 22676333
Ok i am asking that.
<?php
//i connected to database

$value="IT\'S";
$result=mysql_query("insert into sample_table (name) values ('$value')");

?>
i inserted $value to dababase but when i look table i see IT'S where is my slash

After i am trying differtent

<?php
//connected to db
$value="IT\\'S";
$result=mysql_query("insert into sample_table (name) values ('$value')");

?>
i inserted $value to database now i see in table IT\'S one slash lost in fact my variable was IT\\'S
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 48

Expert Comment

by:hernst42
ID: 22676357
0
 

Author Comment

by:phparmy
ID: 22676437
read http://www.php.net/manual/en/language.types.string.php#language.types.string.syntax.double
Ok it is right.
But when it is not double quote only IT\'S i echo variable and see in screen IT\'S, problem in when iam trying to insert to database mysql or something else removes slashes

i think using one addslashes() is enough to add slash and inserting it to database. but mysql removes slash,
using addslashes(addslashes()) twice solves my problem iam asking how it can be.  
0
 
LVL 48

Expert Comment

by:hernst42
ID: 22676447
All strings you put/query into you database and are not bound to parameters (not supported for mysql, but mysqli) need to put trough mysql_real_escape_string. So if you want ->IT\'S<- as string in the database you need to define it as:

$str = "IT\\'S";
$value = mysql_real_escape_string($str);
mysql_query("insert into sample_table (name) values ('$value')");

Open in new window

0
 

Author Comment

by:phparmy
ID: 22676512
Why we use addslashes or mysql_real_escape_string look code below
<?php
$str="IT'S"; //attention: $str is IT'S not IT\'S
$value=mysql_real_escape_string($str); // or $value=addslashes($str);
echo $value; 
//attention:what you see in screen IT\'S right
mysql_query("insert into sample_table (name) values ('$value')");
//attention:what you see in database IT'S right why we do not see IT\'S in database?
?>

Open in new window

0
 
LVL 48

Accepted Solution

by:
hernst42 earned 500 total points
ID: 22676705
because you have the string -IT'S- in PHP and not -IT\'S-

addslashes is the wrong way to do it. Also the result looks the same for most things but mysql_real_escape_string does more when you cope with different charserts and database specific things.

>right why we do not see IT\'S in database?
in mysql it's just -IT'S- as it also uses escaping for strings in the sql as php
0
 

Author Comment

by:phparmy
ID: 22676768
one more question last one i think i understand
if sting in php = IT'S after using mysql_real_escape_string  why we see IT\'S on screen. I think we have to see again IT'S on screen
<?php
 
$str="IT'S";
$value=mysql_real_escape_string($str);
echo $value; // i have php string IT'S why i see IT\'S on screen
 
?>

Open in new window

0
 
LVL 48

Expert Comment

by:hernst42
ID: 22676810
nope thats correct as mysql needs it this way. To make it more clear the output of mysql_real_escape_string is not designed to be printed to screen. It's not the content that would appear in the database. What in the database appears is what you put into mysql_real_escape_string as it should be. If you ommit mysql_real_escape_string you will get SQL errors if a ' is in the text (SQL-Injection error).

I build sql this way with placeholders so it's clear what are strings in sql which need a mysql_real_escape_string :
mysql_query(sprintf("insert into sample_table (name) values ('%s')", mysql_real_escape_string($str)));
0
 

Author Comment

by:phparmy
ID: 22676811
An example use of addslashes() is when you're entering data into a database. For example, to insert the name O'reilly into a database, you will need to escape it. Most databases do this with a \ which would mean O\'reilly. This would only be to get the data into the database, the extra \ will not be inserted. Having the PHP directive  magic_quotes_sybase set to on will mean ' is instead escaped with another '.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question