Prevent SQL Injection: Do I need to escape input before binding values into a PDO query

Question

1. In the scenario below, $user_id is strings entered by a user. I'm wondering if I still need to escape the strings first with $email = htmlentities($_POST["email"]) and $name= htmlentities($_POST["name"])?

   $query = "INSERT INTO user ('email','name') VALUES (?,?)";
   $this->_query = $this->_pdo->prepare($query);
   $this->_query->bindValue(1, $email);
   $this->_query->bindValue(2, $name);
   $this->_query->execute();
   

2. I saw some people don't escape the input before insert query but they do escape data before echo it out. Why do thay do this?

   $query = "SELECT * FROM users WHERE id = ?";
   $this->_query = $this->_pdo->prepare($query);
   $this->_query->bindValue(1, $user_id);
   $this->_query->execute();
   $data = $this->_query->fetchAll(PDO::FETCH_ASSOC);
   $output = $data[0]["name"];
   $output = htmlentities($output);
   echo output;
   

3. Is it neccessary to use htmentities() before and after the query?

Answer 1

You don't need to escape/convert HTML to avoid a SQL injection if you're using prepared queries. As for when you need to use htmlentities or htmlspecialchars, you will need that if you output user-provided data to a web-page. Not doing that makes you vulnerable to XSS (Cross-Site Scripting) injections, ie. malicious javascript that gets executed in the browser on page load. Recommended reading: XSS Prevention Cheat Sheet and PHP Security Cheat Sheet.

Aside SQL and XSS injection prevention, you may also want to do other checks on user input, such as validating e-mail addresses, username characters, etc. before you input them into your DB. Otherwise you're up for some tedious database cleaning down the road. You may want to look into PHP's filter functions, basic regex that removes everything outside white-listed character ranges, etc.

Answer 2

1. No; a parameterized query is safe from SQL-injection attacks.

2. & 3. This is using htmlentities(), which isn't exactly escaping the output. The primary benefit of using this is it protects you from XSS attacks, which is very different from SQL-injection. This sanitizes user input and prevents a user from running a malicious <script> on your webpage.