Confused by PDO bindValue

[StevenHu]

I used to write the code like this:

if (isset($_POST['ad']))
	$ad = $_POST['ad'];
	$ad = htmlspecialchars($ad, ENT_QUOTES, 'UTF-8');

But if I do this:

try
{
$sql = "INSERT INTO store" SET
rob = :rob";

 $s = $pdo->prepare($sql);
 $s->bindValue(':rob', $_POST['rob']);
 $s->execute();
}
catch (PDOException $e)
{
$output = 'Error performing update: ' . $e->getMessage();
include 'output.php';
exit();

... then does that mean I re-write the top part as just:

if (isset($_POST['ad']))

... dropping the htmlspecialchars() line?

Does bindValue mean we don't need to use htmlspecialchars() any more? I'm redoing my code with PDO and need clarification on this point. Is htmlspecialchars() just used for echoing data?

Thanks!

[cpradio]

You really never need to use htmlspecialchars when inserting into a database, you may need to use it when outputting content stored in the database. htmlspecialchars will prevent XSS (Cross Site Scripting) attacks, someone trying to inject malicious JavaScript or markup into your site.

So yes, you don't need to call htmlspecialchars when inserting into your database, however, you may want to use it when outputting anything from your database. Therefore, if you want to limit your code changes, and since you have been running it through htmlspecialchars up to this point, you may as well keep that line before inserting your record (it really won't harm anything).

[Jeff Mott]

htmlspecialchars is actually completely unrelated to anything SQL.

The gist is:
- When using user provided content in SQL, then escape characters that are special to SQL (real_escape_string, prepare/bind).
- When using user provided content in HTML, then escape characters that are special to HTML (htmlspecialchars).

So regardless if you escape for SQL with real_escape_string or prepare/bind, you don't need htmlspecialchars.

EDIT: I'm too slow.

[StevenHu]

Ooops. "ad" should be "rob"

[StevenHu]

Thank you all!

[StevenHu]

Well, this PHP/MySQL book is telling me to use htmlspecialchars when echoing from the table:

<p>
<?php
echo htmlspecialchars($joke, ENT_QUOTES, 'UTF-8');
?>
</p>

[cpradio]

Yes, that is technically the only time you need to use htmlspecialchars.

However, in my prior response, I simply meant to say, since you were using it before inserting your data, you can continue to do that to keep your data in your database consistent and you will remain protected from XSS.

[StevenHu]

Thanks again!