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

Question

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();

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;

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

Do you understand the difference between SQL and HTML, by chance? — Your Common Sense
– Your Common Sense, Commented Dec 18, 2016 at 17:50
Possible duplicate of How can I prevent SQL injection in PHP? — u_mulder
– u_mulder, Commented Dec 18, 2016 at 17:52

Markus AO · Accepted Answer · 2016-12-18 18:02:29Z

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.

Welcome. Added some more "sanity" leads for you to look into.

WillardSolutions · Accepted Answer · 2016-12-18 17:57:17Z

1

No; a parameterized query is safe from SQL-injection attacks.
& 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.

answered Dec 18, 2016 at 17:57

WillardSolutions

2,3124 gold badges30 silver badges38 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Linked

Related