[SOLVED] mysql_real_escape_string() and stripslashes()

[Fluoresce]

I was getting SQL syntax errors whenever someone entered quotation marks in my forms. I'm a newbie, so I overlooked the fact that the inclusion of quotation marks in form fields would mess up the SQL INSERT statement.

 

Just now, I incorporated mysql_real_escape_string() into my form, which escapes problematic characters like quotation marks by putting a slash in front of them. It seems to work. However, the problem is, the slashes also make it through to my database. I'm assuming that this is normal, and that I have to use stripslashes() whenever I echo the data onto my page. Is that correct? Or, can I do something to stop the slashes from being inserted into my database?

[killah]

$text = $_POST['text']:
$text = str_replace("'","",$text);  //Strip out '
$text = str_replace('"','',$text);   //Strip out "
$text = mysql_real_escape_string($text); //make it safe enough

 

That should do it. But it is not the correct method. As user's some time's like there 's.

[Fluoresce]

Killah, that would strip all quotation marks (double and single) from the text and add slashes to any remaining problematic characters, right? But I want the quotation marks to make it into my database. If I remove them, I'll have loads of bad punctuation on my site.

 

How do I allow the quotation marks to be inserted without the use of mysql_real_escape_string() to prepend slashes for the INSERT statement? Or, can't this be done?

 

[corbin]

The slashes should not be inserted into your DB.  It sounds like you're running mysql_real_escape_string twice or magic quotes are enabled.

 

 

Can we see code?

[Fluoresce]

Sure, Corbin:

 

<?php

// Required fields. Function mysql_real_escape_string() escapes
// all disallowed characters (e.g., apostrophes) in the SQL statement.
$adv = mysql_real_escape_string($_POST['adv']);
$website = $_POST['website'];
$catid = $_POST['catid'];
$prod_serv = mysql_real_escape_string($_POST['prod_serv']);
$returns = mysql_real_escape_string($_POST['returns']);
$benefits = mysql_real_escape_string($_POST['benefits']);
$support = mysql_real_escape_string($_POST['xsupport']);
$payment = mysql_real_escape_string($_POST['payment']);
$tax = mysql_real_escape_string($_POST['tax']);
$usdelopt = mysql_real_escape_string($_POST['usdelopt']);
$intdel_avail = mysql_real_escape_string($_POST['intdel_avail']);
$ordertrack = $_POST['ordertrack'];

// Optional field
$description = mysql_real_escape_string($_POST['description']);

// As $description can be NULL, prepare it for the insert.
if (!$description) {
  $description = "NULL";
} else {
  $description = "'$description'";
}

// Check that the required fields have been filled in.
if ($adv == '') {
  echo "<p>You have not entered your business's name. Please go back and try again.</p>";
} elseif ($website == '') {
  echo "<p>You have not entered your web address. Please go back and try again.</p>";
} elseif ($catid == '') {
  echo "<p>You have not selected the category which best suits your business. Please go back and try again.</p>";
} elseif ($prod_serv == '') {
  echo "<p>You have not specified the types of products/services you offer. Please go back and try again.</p>";
} elseif ($returns == '') {
  echo "<p>You have not specified a return policy. Please go back and try again.</p>";
} elseif ($benefits == '') {
  echo "<p>You have not entered anything in the Benefits and Features field. Please go back and try again.</p>";
} elseif ($support == '') {
  echo "<p>You have not specified how you provide customer support. Please go back and try again.</p>";
} elseif ($payment == '') {
  echo "<p>You have not specified what payment methods you accept. Please go back and try again.</p>";
} elseif ($tax == '') {
  echo "<p>You have not entered anything in the Sales Tax field. Please go back and try again.</p>";
} elseif ($usdelopt == '') {
  echo "<p>You have not specified which delivery options you provide for U.S. customers. Please go back and try again.</p>";
} elseif ($intdel_avail == '') {
  echo "<p>You have not specified the geographic availability of your products/services. Please go back and try again.</p>";
} elseif ($ordertrack == '') {
  echo "<p>You have not specified if you provide a facility for order status tracking. Please go back and try again.</p>";
} else {

// Generate entry for advdate.
$advdate =  date("Y-m-d");

// Insert the column entries that you've got so far.
$conn = mysql_connect('localhost', 'heru_tehutimaat', 'atumra') or die(mysql_error());  
mysql_select_db('heru_ctyi', $conn) or die(mysql_error());
$insert = mysql_query("INSERT INTO tadv (visitaffurl, adv, catid, advdate, website, prod_serv, returns, benefits, support, payment, tax, usdelopt, intdel_avail, ordertrack, description) VALUES ('$website', '$adv', '$catid', '$advdate', '$website', '$prod_serv', '$returns', '$benefits', '$support', '$payment', '$tax', '$usdelopt', '$intdel_avail', '$ordertrack', $description)") or die(mysql_error());

// Get the inserted row's advid.
$advid = mysql_insert_id();

  // If the insert worked, generate the remaining
  // column entries, then update the row.
  if ($insert) 
  {
    $update = "UPDATE tadv SET
    store = 'store.php?storeid=$advid', 
    visit = 'visit.php?siteid=$advid'
    WHERE advid = $advid";

    $updated = mysql_query($update, $conn) or die(mysql_error());

      // If the update worked, present a message.
      if ($updated) 
      {
        echo "<p>Success! Thank you for your submission.</p>";
      } 
      
      else 
      {
        echo "<p>Sorry! An error occurred. Please go back and try again.</p>";
      }
  } 
}

?>

[Fluoresce]

Corbin, I turned off Magic Quotes (or whatever it's called), and it seems to have done the trick. I created a .htaccess file with "php_flag magic_quotes_gpc off" in it and placed this file in the root folder of my site. Does that sound right to you? Should I have done anything else?

 

And since I submitted my script in the last post, I was wondering what you more experienced programmers thought of it. It's one of the first scripts I have ever written by myself. Does it need more security? Can it be improved in any way?

 

Any advice will be appreciated.

 

 

[Fluoresce]

Actually, that .htaccess thing didn't work. It worked on my own server (XAMPP) but not on my actual site, on HostGator. I put the .htaccess file not in the primary root folder (public_html) but in the root folder of the web site. Maybe I should just ask HostGator about this . . .

[Fluoresce]

This is very strange . . .

 

I got the script above working on my own server, but it doesn't work on HostGator. If someone includes a quotation mark in a field while filling in my form, that field doesn't make it to the database. Magic Quotes has been disabled on my server. What could be the problem? Remember, it works on my server but not on HostGator.  ???

[corbin]

What does:

 

 

var_dump(get_magic_quotes_run_time());

 

output?

[Fluoresce]

What does:

 

var_dump(get_magic_quotes_run_time());

 

output?

 

That just gives me an error:

 

"Fatal error: Call to undefined function get_magic_quotes_run_time() in [location] on line 3".

 

I've got a strange problem here, guys. I spoke to a HostGator representative and he must have done something to my configuration, because my form now doesn't work at all. It works fine on my server but not on HostGator. I am currently waiting for a technician to respond. If I get it fixed, I'll mark this issue resolved. Until then, I'll leave it open, just in case I need more help.

 

Thanks for the help so far.

[killah]

Here is another option.

 

if(get_magic_quotes_gpc()) {
   //Add mysql_real_escape_string() to all my posts.
}
else {
  //Add slashes & mysql_real_escape_string()
}

 

Remmember, you will also need to strip_slashes when you output the result.

[Fluoresce]

Thanks for the help, guys. This topic is now solved. I discovered that all I had to do was move mysql_real_escape_string() above the connection for it to work properly, and I had to switch Magic Quotes off.

 

I appreciate your contributions.