Do I Have to Use MySQL_Real_Escape_String If I Bind Parameters

Do I have to use mysql_real_escape_string if I bind parameters?

No, you don't have to escape value yourself (i.e. no you don't need to call mysqli_real_escape_string), when you are using prepared statements : the DB engine will do that itself.

(Actually, if you were calling mysql_real_escape_string and using bound parameters, your strings would get escaped twice -- which would not be great : you'd end up with escaping characters everywhere...)

As a sidenote : your values are passed as integers (as indicated by the 'ii'), so you wouldn't have to call mysql_real_escape_string, even if you were not using prepared statements : as its name indicates, this function is used to escape... strings.

For integers, I generally just use intval to make sure the data I inject into my SQL queries really are integers.

(But, as you are using prepared queries, once again, you don't have to do that kind of escaping yourself)

Is mysql_real_escape_string() necessary when using prepared statements?

No, prepared queries (when used properly) will ensure data cannot change your SQL query and provide safe querying. You are using them properly, but you could make just one little change. Because you are using the '?' placeholder, it is easier to pass params through the execute method.

$sql->execute([$consulta]);

Just be careful if you're outputting that to your page, SQL parameter binding does not mean it will be safe for display within HTML, so run htmlspecialchars() on it as well when outputting.

When to use mysql_real_escape_string?

You should use mysql_real_escape_string() whenever you're building a query that will be run against the database. Any user input that is being used to build a database query should be run through this function. This will prevent sql injection attacks.

User inputs are your big area of concern when it comes to this.

PDO without mysql_real_escape_string and bindValue

No, this is not safe. PDO doesn't magically escape your queries for you. Your code, as shown, is wide open to SQL injection.

If you are using variables in your query, don't use ->query. Do not try to escape them yourself. You should be using prepared statements. That's the way to be safe.

$stmt = $db->prepare('SELECT id,name FROM names where id = ?');
if($stmt->execute(array($id))){
    while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
        echo "<p>", ($row['name']), "<br>";
    }
}

So, yes, you need to use bindParam, or execute, as shown.

P.S. mysql_real_escape_string is only for the (deprecated) mysql_ extension. It doesn't work with PDO.

mysql_real_escape_string() for $_SESSION variables necessary?

Regardless of whether the user can modify the data, you probably want to escape it anyway in case you ever need the data to contain characters that would break the SQL (quotes, etc).

Better yet, use bound parameters and you won't have to worry about it.

What is the PDO equivalent of function mysql_real_escape_string?

Well No, there is none!

Technically there is PDO::quote() but it is rarely ever used and is not the equivalent of mysql_real_escape_string()

That's right! If you are already using PDO the proper way as documented using prepared statements, then it will protect you from MySQL injection.

---

# Example:

Below is an example of a safe database query using prepared statements (pdo)

  try {
     // first connect to database with the PDO object. 
     $db = new \PDO("mysql:host=localhost;dbname=xxx;charset=utf8", "xxx", "xxx", [
       PDO::ATTR_EMULATE_PREPARES => false, 
       PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
     ]); 
 } catch(\PDOException $e){
     // if connection fails, show PDO error. 
   echo "Error connecting to mysql: " . $e->getMessage();
 }

And, now assuming the connection is established, you can execute your query like this.

if($_POST && isset($_POST['color'])){ 

    // preparing a statement
    $stmt = $db->prepare("SELECT id, name, color FROM Cars WHERE color = ?");

    // execute/run the statement. 
    $stmt->execute(array($_POST['color']));

    // fetch the result. 
    $cars = $stmt->fetchAll(\PDO::FETCH_ASSOC); 
    var_dump($cars); 
 }

Now, as you can probably tell, I haven't used anything to escape/sanitize the value of $_POST["color"]. And this code is secure from myql-injection thanks to PDO and the power of prepared statements.

---

It is worth noting that you should pass a charset=utf8 as attribute, in your DSN as seen above, for security reasons, and always enable
PDO to show errors in the form of exceptions.

PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION

so errors from you database queries won't reveal sensitive data like your directory structure, database username etc.

Last but not least, there are moments when you should not trust PDO 100%, and will be bound to take some extra measures to prevent sql injection, one of those cases is, if you are using an outdated versions of mysql [ mysql =< 5.3.6 ] as described in this answer

But, using prepared statements as shown above will always be safer, than using any of the functions that start with mysql_

Good reads

PDO Tutorial for MySQL Developers

I use mysql_real_escape_string before SQL INSERT, but then have to apply stripslashes to my retrieved data. Is it normal?

oh, what a senseless function. I know it's not your fault but ones who wrote it in their stupid articles and answers.

Get rid of it and use only mysql_real_escape_string to escape strings.

you have mixed up everything.

• first, no magic quotes stuff should be present in the database escaping function.
  
  if you want to get rid of magic quotes, do it centralized, at the very top of ALL your scripts, no matter if they deal with the database or not.

• most of checks in this function are useless. is_bool for example. PHP will convert it the same way, no need to write any code for this.

• LIKE related escaping is TOTALLY distinct matter, and has nothing to do with safety.

• is numeric check is completely useless, as it will help nothing.

Also note that escaping strings has nothing to do with security.
I's just a syntax rule - all strings should be escaped. No matter of it's origin or any other stuff. Just a strict rule: every time you place a string into query, it should be quoted and escaped. (And of course, if you only escape it but not quote, it will help nothing)

And only when we talk of the other parts of query, it comes to the SQL injection issue. To learn complete guide on this matter, refer to my earlier answer: In PHP when submitting strings to the database should I take care of illegal characters using htmlspecialchars() or use a regular expression?

---

Related Topics