Page 1 of 2

How do you use mysql_real_escape_string w/o backslashes?

Posted: Mon Jan 11, 2010 3:47 pm
by simonmlewis
how do you use "mysql_real_escape_string" without the result going into the DB having backslashes?

For example: I enter a book called "Mustn't Grumble" into a web page, and the database stores it, after the escape, as Mustn\'t Grumble.

Those backslashes are not very userfriendly. Am sure there's a simple method to avoid this - but what is it?

Re: How do you use mysql_real_escape_string w/o backslashes?

Posted: Mon Jan 11, 2010 3:53 pm
by AbraCadaver
Most likely magic quotes is doing it when the form is posted, so you get double slashes:

Code: Select all

if(magic_quotes_gpc()) {
   $var = stripslashes($var);
}
$var = mysql_real_escape_string($var);

Re: How do you use mysql_real_escape_string w/o backslashes?

Posted: Mon Jan 11, 2010 3:55 pm
by simonmlewis
So magic quote will get rid of it?... as I don't have that in my code.

Re: How do you use mysql_real_escape_string w/o backslashes?

Posted: Mon Jan 11, 2010 3:57 pm
by AbraCadaver
simonmlewis wrote:So magic quote will get rid of it?... as I don't have that in my code.
magic_quotes_gpc if enabled in php.ini causes addslashes to be run on all get, post and cookie vars. You can turn off magic_quotes_gpc in php.ini or use the code that I gave you that checks to see if magic_quotes_gpc is enabled, and if so it strips the slashes.

Re: How do you use mysql_real_escape_string w/o backslashes?

Posted: Mon Jan 11, 2010 4:07 pm
by simonmlewis
Like this?

Code: Select all

{ 
if(magic_quotes_gpc()) 
{
$description = stripslashes($description);
$title = stripslashes($title);
$subtitle = stripslashes($subtitle);
}
 
$description=mysql_real_escape_string($_POST["description"]);
$title=mysql_real_escape_string($_POST["title"]);
$subtitle=mysql_real_escape_string($_POST["subtitle"]);
mysql_query("INSERT INTO products
(userid, catid, catname, subid, subname, title, subtitle, description, video, method, price) VALUES 
('$userid', '$catid', '$catname', '$subid', '$subname', '$title', '$subtitle', '$description', '$video', '$method', '$price')");
coz it still puts the slashes in.

Result of $title:
That\'s Another Story

Re: How do you use mysql_real_escape_string w/o backslashes?

Posted: Mon Jan 11, 2010 4:21 pm
by AbraCadaver
Uhhh, that's because you stripped slashes on $description and then you assigned mysql_real_escape_string($_POST['description']) to $description. I would create a function and use that:

Code: Select all

function prep4db($var) {
    if(magic_quotes_gpc()) {
       $var = stripslashes($var);
    }
    return mysql_real_escape_string($var);
}
 
$description = prep4db($_POST['description']); 

Re: How do you use mysql_real_escape_string w/o backslashes?

Posted: Mon Jan 11, 2010 4:25 pm
by simonmlewis
Oh wow, so that function will get rid of the \'s on ALL those three escapes I have done?

Brilliant.

Re: How do you use mysql_real_escape_string w/o backslashes?

Posted: Mon Jan 11, 2010 4:30 pm
by flying_circus

Code: Select all

<?php
  # Disable Magic Quotes
    if(get_magic_quotes_gpc()) {
      $input = array(&$_GET, &$_POST, &$_COOKIE, &$_ENV, &$_SERVER);
      
      while(list($k, $v) = each($input)) {
        foreach($v as $key => $val) {
          if(!is_array($val)) {
            $input[$k][$key] = stripslashes($val);
            continue;
          }
          $input[] =& $input[$k][$key];
        }
      }
      unset($input);
    }
    
  # Variables
    $description = $_POST["description"];
    $title = $_POST["title"];
    $subtitle = $_POST["subtitle"];
  
  mysql_query(sprintf("INSERT INTO `products` (`userid`, `catid`, `catname`, `subid`, `subname`, `title`, `subtitle`, `description`, `video`, `method`, `price`) VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s');",
                      mysql_real_escape_string($userid),
                      mysql_real_escape_string($catid),
                      mysql_real_escape_string($catname),
                      mysql_real_escape_string($subid),
                      mysql_real_escape_string($subname),
                      mysql_real_escape_string($title),
                      mysql_real_escape_string($subtitle),
                      mysql_real_escape_string($description),
                      mysql_real_escape_string($video),
                      mysql_real_escape_string($method),
                      mysql_real_escape_string($price)));
?>

Re: How do you use mysql_real_escape_string w/o backslashes?

Posted: Mon Jan 11, 2010 4:40 pm
by simonmlewis

Code: Select all

if ($pic == NULL) 
{ 
   if(get_magic_quotes_gpc()) {
      $input = array(&$_GET, &$_POST, &$_COOKIE, &$_ENV, &$_SERVER);
     
      while(list($k, $v) = each($input)) {
        foreach($v as $key => $val) {
          if(!is_array($val)) {
            $input[$k][$key] = stripslashes($val);
            continue;
          }
          $input[] =& $input[$k][$key];
        }
      }
      unset($input);
    }
 
$description=mysql_real_escape_string($_POST["description"]);
$title=mysql_real_escape_string($_POST["title"]);
$subtitle=mysql_real_escape_string($_POST["subtitle"]);
mysql_query("INSERT INTO products
(userid, catid, catname, subid, subname, title, subtitle, description, video, method, price) VALUES 
('$userid', '$catid', '$catname', '$subid', '$subname', '$title', '$subtitle', '$description', '$video', '$method', '$price')");
 
$newid = mysql_insert_id();
 
$result = mysql_query ("SELECT * FROM products WHERE id = '$newid'");
while ($row = mysql_fetch_object($result))
      {
echo "<meta http-equiv='Refresh' content='0 ;URL=index.php?page=product&id=$row->id&menu=sub&c=$row->catid&cname=$row->catname&sname=$row->subname&head=$row->title'>";
      } mysql_free_result($result);
}
 
This is still getting back slashes.

Re: How do you use mysql_real_escape_string w/o backslashes?

Posted: Mon Jan 11, 2010 4:42 pm
by flying_circus
I prefer my method to AbraCadaver's for a couple of reasons.

Magic quotes is global in that it adds slashes to all get, post, and cookie data. My function strips the slashes globally up front, so that you dont need to remember to escape data in an as-needed basis, which leaves the door open for a mistake.

Also, I like to use the sprintf function when generating mysql queries, as it allows me to cleanly escape all data immediately before it goes into a query. If you escape the data as in AC's solution, be sure that you dont modify the value before inserting it into a database, or you run the possibility of corrupting the data.

Both of our solutions work well, whichever route you decide to go.

Re: How do you use mysql_real_escape_string w/o backslashes?

Posted: Mon Jan 11, 2010 4:49 pm
by simonmlewis
Sorry, but for some reason neither are working in my code - both still have backslashes.

Re: How do you use mysql_real_escape_string w/o backslashes?

Posted: Mon Jan 11, 2010 4:57 pm
by pickle
If magic quotes are enabled, PHP will automatically escape quotes. So, "Mustn't Grumble" becomes $_POST["Mustn\'t Grumble"]. When you run that through mysql_real_escape_string(), the function sees the single quote, and escapes it again, so you get "Mustn\\'t Grumble". Inserting that into the database removes one of backslashes, since MySQL realizes "\" is escaping the next character.

Fixing the problem like ~AbraCadaver suggested is the simplest solution,

Code: Select all

//remove any slashes that may have been added
$posted_val = (get_magic_quotes_gpc()) ? stripslashes($_POST['val']) : $_POST['val'];
//$posted_val will now be exactly what the user entered
 
//prepare it for the database
$posted_val = mysql_real_escape_string($posted_val);

Re: How do you use mysql_real_escape_string w/o backslashes?

Posted: Fri Jan 15, 2010 10:39 am
by simonmlewis
Brilliants - works perfect. It leaves the apostrophies in, without adding the slashes.

Cheers.

Re: How do you use mysql_real_escape_string w/o backslashes?

Posted: Fri Jan 15, 2010 11:08 am
by simonmlewis
Should this work?

Code: Select all

$head = $_REQUEST['head'];
    $posted_head = (get_magic_quotes_gpc()) ? stripslashes($_REQUEST['head']) : $_REQUEST['head'];
$head=mysql_real_escape_string($posted_head);
 
        if ($head != NULL) { echo "<div class='head'>
        $head</div>
Coz it isn't. And I thought it would be the same, except thatI am using REQUEST not POST.

...... UPDATE:............. bearing in mind, this particular page has got no MySQL db connections. I just need to pass something like "Walter's" in the URL, so it appears in the $head variable.

At the moment it is producing everying up to "Walter".... without the 's.

Re: How do you use mysql_real_escape_string w/o backslashes?

Posted: Fri Jan 15, 2010 5:49 pm
by thinsoldier
passing the value in the url? Then you probably need php.net/urlencode
Just a guess.
Ever noticed how spaces get turned into %20 in the address bar? You probably need to turn the ' in Walter's into some %123 kind of thing.