Is 'my_real_escape_string' required after I validated and sanitized form data?

Question

So I have validated and sanitized my form data on the same document. I was thinking it was safe until I started seeing stuff about the 'my_real_escape_string'.

I am thinking that my data will be secure if I enter it how I have it but am not wanting to take the chance. So my question is do I need to use this 'my_real_escape_string' to ensure data security.

I am using the code below. First will be the form document (index.php) itself and then processing document(processform.php). Thanks for any and all help.

//index.php

<?php
// define variables and set to empty values
$first_nameErr = $last_nameErr = $emailErr = $passwordErr = $genderErr = "";
$first_name = $last_name = $email = $password = $gender = "";

if ($_SERVER["REQUEST_METHOD"] == "POST")
{
if (empty($_POST["first_name"]))
 {$first_nameErr = "";}
else
 {
 $first_name = test_input($_POST["first_name"]);
 // check if name only contains letters and whitespace
 if (!preg_match("/^[a-zA-Z ]*$/",$first_name))
   {
   $first_nameErr = "Only letters allowed";
   }
  }

if (empty($_POST["last_name"]))
 {$last_nameErr = "";}
else
 {
 $last_name = test_input($_POST["last_name"]);
 // check if name only contains letters and whitespace
if (!preg_match("/^[a-zA-Z ]*$/",$last_name))
   {
   $last_nameErr = "Only letters allowed";
   }
 }


if (empty($_POST["email"]))
  {$emailErr = "";}
else
 {
 $email = test_input($_POST["email"]);
 // check if e-mail address syntax is valid
if (!preg_match("/([\w\-]+\@[\w\-]+\.[\w\-]+)/",$email))
   {
   $emailErr = "Invalid email format";
   }
  }


if (empty($_POST["password"]))
 {$passwordErr = "";}
else
 {$password = test_input($_POST["password"]);}

if (empty($_POST["gender"]))
 {$genderErr = "";}
else
 {$gender = test_input($_POST["gender"]);}

}

function test_input($data)
{
$data = trim($data);
$data = stripslashes($data);
$data = htmlspecialchars($data);
return $data;
}
?>



<div class="signupitsfree" float="right">
<p class="signup">Sign Up<br />It's Completely Free!</p>


<form method="POST" name="signup" action="processform.php">

<label for="first name"></label><input id="first name" name="first_name" 
placeholder="First Name" type="text" value="<?php echo $first_name;?>" /> <span  
class="error">* <?php echo $first_nameErr;?></span>

<label for="last_name"></label><input id="last name" name="last_name"  
placeholder="Last 
Name" type="text" value="<?php echo $last_name;?>" />
<span class="error">* <?php echo $last_nameErr;?></span>
<br><br>


<label for="email"></label><input id="email" name="email" placeholder="Email"    
type="text" value="<?php echo $email;?>" />
<span class="error">* <?php echo $emailErr;?></span>
<br /><br />


<label for="password"></label><input id="password" name="password" 
placeholder="Create  
Password" type="password" />
<span class="error">* <?php echo $passwordErr;?></span>
<br /><br />
<label for="male"><strong>Male</strong></label> <input id="male" value="male" 
<?php if (isset($gender) && $gender=="male") echo "checked";?> 
name="gender" type="radio" /> 
<label for="female"><strong>Female</strong></label> <input id="female" value="female" 
<?php if (isset($gender) && $gender=="female") echo "checked";?> name="gender"  
type="radio" />
<span class="error">* <?php echo $genderErr;?></span>
<br /><br />
<label for="submit">"I Agree To <a href="#">Terms And Conditions"</a></label>

//BELOW IS 'PROCESSFORM.PHP'

<?php

 $hostname="this is correct";
 $username="thisalso";
 $password="chicken";
 $dbname="chiken also";

  $db_conx = mysqli_connect($hostname, $username, $password) OR DIE ("Unable to
  connect to database! Please try again later.");

  if(mysqli_connect_errno()){
  echo mysqli_connect_error();
  exit();
  }

  $select = mysqli_select_db($db_conx,$dbname);

  $first_name= $_POST["first_name"];
  $last_name= $_POST["last_name"];
  $email= $_POST["email"];
  $password= $_POST["password"];
  $gender= $_POST["gender"];

  mysqli_query($db_conx,"INSERT INTO users (firstname, lastname, email, password,   gender)
  VALUES ('$first_name', '$last_name', '$email', '$password', '$gender')");
  mysqli_close($db_conx);

  header("Location: anotherpage.php")
  ?>

`mysql_*_escape_string` **does not "sanitize" data** (that is, it it *outside* the scope of business rules!): it is a hack to prevent SQL Injection. Always apply *business rules* to the data and use *placeholders* to prevent SQL Injection. They are two *different* issues. See http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php — user2864740, Dec 29 '13 at 06:16
@ user2864740 OK I think I understand now. So as a web designer do you have to use hacks to prevent hacks? — user3066599, Dec 29 '13 at 06:21
@user3066599 No. Placeholders are not a hack. Business rules (and validation therein) are not a hack. Placeholders prevent *SQL Injection* (or, changing the *shape* of the query) while business rules ensure that only "valid" data is allowed. (The database schema should be appropriately typed to provide fail-safe rules/limits where applicable.) — user2864740, Dec 29 '13 at 06:21
This can support Maz lqbal answered http://stackoverflow.com/questions/3716373/real-escape-string-and-pdo — Jay Sithiporn, Dec 29 '13 at 06:22
Actually, `mysql_real_escape_string()` is _not_ a hack. It's a legitimate tool to escape your data safely in a locale-aware way. Prepared statements are not always the best approach, and sometimes they're not easily usable, so a proper tool to escape data is essential. — , Dec 29 '13 at 06:24
@MikeW Anyone who asks so generally about `mysql_*_escape_string` is not ready for `mysql_*_escape_string`: there are *some* cases for it, but they are not the norm and should be *very carefully* considered. — user2864740, Dec 29 '13 at 06:25
@user3066599 Not at all! Placeholders prevent [SQL injection](http://en.wikipedia.org/wiki/SQL_injection) (and *nothing* more): "SQL injection is a code injection technique, used to attack data driven applications, in which malicious SQL statements are inserted into an entry field for execution (e.g. to dump the database contents to the attacker)." — user2864740, Dec 29 '13 at 06:25
Oh I see. Well The business rules and the placeholders will allow me to stop sql injections. Thanks for the information. — user3066599, Dec 29 '13 at 06:26
So what is used to encrypt the passwords before I let you go — user3066599, Dec 29 '13 at 06:27
Because I can see in my table that the password is as I put it in — user3066599, Dec 29 '13 at 06:27
@user3066599 Business rules aren't related to SQL Injection (directly): rather, they ensure that the *information* is "valid". For instance, that a person can only "die" *after* they are "born" (some rules can/should be encoded in the databases while other can/should be in the application or DAL, depending upon how one views the business rules and application of such). — user2864740, Dec 29 '13 at 06:28
@user3066599 Oh no! Do not encrypt passwords, that's an entirely different situation not related to `mysql_*_escape_string`. Passwords should be hashed (with bcrypt or scrypt - not SHA!) with an appropriate salt. — user2864740, Dec 29 '13 at 06:28
Ok I will look into that. Thanks for your help. I now have a better understanding. — user3066599, Dec 29 '13 at 06:29
If you have any additional information on this matter it will be accepted with open arms but I think I am on the right path to researching and finding out. — user3066599, Dec 29 '13 at 06:30
Just like users before me said, you should use [prepared statements](http://www.php.net/manual/en/mysqli.prepare.php) — Ohgodwhy, Dec 29 '13 at 06:55
[The Great Escapism (Or: What You Need To Know To Work With Text Within Text)](http://kunststube.net/escapism/) — deceze, Dec 29 '13 at 09:28

Damien Pirsy · Answer 1 · 2013-12-29T09:26:15.163

The catch frase we always hear about mysql*_real_escape_string() is to avoid SQL injection , so it's perfectly clear that some people develop their own twisted and complicated system to sanitize any input and think: "Ok, now my input is "safe", I'm not needing to avoid SQL injection anymore".

But that's wrong, simply because mysql*_real_escape_string(), and prepared statements, are not to avoid SQL injection. They're in place to avoid breaking the query (I know, they're also for SQL injection, but because SLQ injection work by breaking your query and hijacking it, adding parts, removing others, etc)

Look at your code:

  $first_name= $_POST["first_name"];
  $last_name= $_POST["last_name"];
  $email= $_POST["email"];
  $password= $_POST["password"];
  $gender= $_POST["gender"];

  mysqli_query($db_conx,"INSERT INTO users (firstname, lastname, email, password,   gender)
  VALUES ('$first_name', '$last_name', '$email', '$password', '$gender')");

Now, I'm a genuine and naive user, I don't even know what an injection is, so I'm not a user you should not trust. I register to your site, I put my data there: name Ian, lastname O'Really.

What? Database error? Why?

That's it. If I put a quote in your query, it breaks, because the quote ends the string where it shouldn't, and your db receives

INSERT INTO users (firstname, lastname, email, password,   gender)
  VALUES ('Ian', 'O'Really'

which is an SQL syntax error.

That's why we need escaping functions, not because of ninja hackers always lurking to destroy our user table and pawn our server, but because we don't want the query to be broken as soon as a syntax characters is put in the wrong position, unescaped.

Validation has nothing to do with escaping

You validate when you want your data to be in a valid format: a date must be formatted as date, a username cannot contain spaces, a password must be minum 8 chars, etc. Those are valid inputs for your program (in my app I might want totally different rules), and for this reason you validate them.

Escaping on the other hand is to allow control characters to be inserted without their original meaning, making them a regular character without special meaning: you escape < so that it won't be an html start of tag and be a potential XSS issue, you escape the quotes to avoid breaking a query, and so on.

Different things for different situation, please keep that in mind.

I can't believe I see such a sane answer under PHP tag. – Your Common Sense Dec 29 '13 at 10:47 — Your Common Sense, Dec 29 '13 at 10:47

score 0 · Answer 2 · answered Dec 29 '13 at 07:03

0

Yes you do need the escape string, because your information you'r sending will be visible for the user. So for instance. You have a shopping cart system. They can just add items in from the URL. So if you want to have it secure, use the escape string.

Cheers, Jesse

answered Dec 29 '13 at 07:03

1

Wha-‽ What does "visibility" and "from the URL" have to do with this? – deceze Dec 29 '13 at 09:06

Is 'my_real_escape_string' required after I validated and sanitized form data?

2 Answers2