Solved

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

Posted on 2008-10-09
11
333 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 48

Expert Comment

by:hernst42
Comment Utility
0
 

Author Comment

by:phparmy
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 48

Expert Comment

by:hernst42
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to count occurrences of each item in an array.

772 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now