CODEWITHSUNDEEP
phpmysqlpdo
Louis Tran
Louis Tran

Reputation: 1166

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

  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?

Upvotes: 0

Views: 605

Answers (2)

Markus AO
Markus AO

Reputation: 4889

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.

Upvotes: 1

WillardSolutions
WillardSolutions

Reputation: 2314

  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.

Upvotes: 1

Related Questions