即使我使用html_entity_decode，html实体也会传入数据库

Question

$string = "susan's"; //string is scraped from website
$string = html_entity_decode($string);
$sql = 'INSERT INTO database SET name = "'. $string .'"';

When I echo out $sql, it shows correct one: INSERT INTO database SET name="susan's", but when I run query it inserts susan's into database. When I run query manually from phpmyadmin it inserts correct one. Why do html entities get passed to database even when I remove them?

Answer 1

You need to use the ENT_QUOTES flag constant.

As per the manual:

• https://secure.php.net/manual/en/function.htmlspecialchars-decode.php

ENT_QUOTES Will convert both double and single quotes.
A bitmask of one or more of the following flags, which specify how to handle quotes and which document type to use. The default is ENT_COMPAT | ENT_HTML401.

Where ENT_COMPAT produces susan's.

So your code ends up being:

$string = htmlspecialchars_decode($string, ENT_QUOTES);

Note: Depending on which API is used to insert this with, you need to be made aware that escaping it without using stripslashes() to it and should this be the case, may produce susan\'s, being another undesired result.

Use a prepared statement, should this be coming from user input if you're not already doing so.

• https://en.wikipedia.org/wiki/Prepared_statement

This will help against an SQL injection.

• https://en.wikipedia.org/wiki/SQL_injection

---

When I echo out $sql, it shows correct one: INSERT INTO database SET name="susan's",

Tip: Before inserting into your database, always look at your HTML source. That will reveal exactly what it is that is going to be passed in the query. That is also considered as being a "tool".

• Echo and the (HTML) source are two different animals altogether.