Solved

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

Posted on 2008-10-09
11
334 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
 
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
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…

867 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

15 Experts available now in Live!

Get 1:1 Help Now