htmlspecialchars not working

Question

My code is:

$description = $_POST['description'];
$description = htmlspecialchars($description);

I use it to insert some description into a table:

$insertBillIndexQuery = "INSERT INTO $billIndexTableName (type, exp_category, shopping_date, shop, description, total_amount, paid, due, mode_of_payment) VALUES ('Expense', '$exp_category', '$billDate', '$shop', '$description', '$total_amount', '$paid', '$due', '$modeOfPayment')";

This works fine usually. However, when I type a special character such as a single quote, the system breaks, and I get an Error Querying Database error. I'm sure that the single quotes are causing the problem. Am I using htmlspecialchars wrong?

You're assuming that `'` is an html special character; it's not.... but it is in SQL, and needs escaping if it's part of a string value.... learn to use prepared statements, and it ceases to be an issue — Mark Baker, Jan 04 '17 at 13:26
@MarkBaker Can you provide a link to the same? I don't know what it is.. — Somenath Sinha, Jan 04 '17 at 13:27
HTML escaping and SQL escaping are two completely separate things. — arkascha, Jan 04 '17 at 13:29
http://www.w3schools.com/php/php_mysql_prepared_statements.asp — Michael, Jan 04 '17 at 13:30
[prepared statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) — Mark Baker, Jan 04 '17 at 13:30

score 1 · Accepted Answer · answered Jan 04 '17 at 13:28

1

You need to do the conversion using ENT_HTML401 for converting ' into '. According to the manual:

' (single quote)
' (for ENT_HTML401) or ' (for ENT_XML1, ENT_XHTML or ENT_HTML5), but only when ENT_QUOTES is set

answered Jan 04 '17 at 13:28

Praveen Kumar Purushothaman

164,888
24
203
252

Okay.. but after the form submits, the `'` value gets saved in the database. How do I output the character `'` instead? – Somenath Sinha Jan 04 '17 at 13:30
@SomenathSinha `'` is converted automatically to `'` by the browsers in HTML mode, the same way how `&` gets displayed as `&`. – Praveen Kumar Purushothaman Jan 04 '17 at 13:31
In the context of the question - database insertion - this is a pretty horrible answer... – jeroen Jan 04 '17 at 13:32
@jeroen Agreed with you. – Praveen Kumar Purushothaman Jan 04 '17 at 13:33
@SomenathSinha The right thing to do is, please use [Prepared Statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) for the right way. – Praveen Kumar Purushothaman Jan 04 '17 at 13:33
@jeroen ^^^^ Updated. – Praveen Kumar Purushothaman Jan 04 '17 at 13:33

htmlspecialchars not working

1 Answers1