Decoding HTML Entities when putting them into a MySQL database

Question

I am trying to write to a MySQL database properly, but it's giving me HTML Entities instead of the character I want.

My page header contains...

<meta charset="utf-8">

Here's the code I'm using...

$team_name = "Ranieri&#39;s Ghost";
$team_name_converted = html_entity_decode($team_name, ENT_COMPAT, "UTF-8");

mysqli_query($con,"INSERT INTO mydbname (team_name) VALUES ('$team_name_converted') ");

echo $team_name_converted;

Echoed to screen: Ranieri's Ghost
Entry in MySQL Database: Ranieri's Ghost

I know this is susceptible to SQL injection but I'll sort that once fixed.

The SQL Table row is a VARCHAR (40) and I have tried collation "utf8_roman_ci", "latin1_swedish_ci", "utf8_swedish_ci", "utf8_general_ci" and a few others but it didn't seem to change much.

Any idea how I can get it to write Ranieri's Ghost into my MySQL database without the special characters?

Explosion Pills · Accepted Answer · 2013-04-16 01:55:36Z

3

Your query is failing because you are not escaping the apostrophe. You can do this easily by using prepared statements (as you should be doing with mysqli anyway). In the meantime, $team_name_converted = mysqli_real_escape_string($con, $team_name_converted) will do.

EDIT: ENT_COMPAT converts double quotes but not single quotes. Use ENT_QUOTES.

edited Apr 16, 2013 at 1:55

answered Apr 16, 2013 at 1:41

Explosion Pills

192k56 gold badges341 silver badges417 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

deceze Over a year ago

+1 This goes to show that "SQL injection prevention" is not an afterthought, it's a basic necessity.

Cully Over a year ago

Thanks - this works, but I also had to change ENT_COMPAT to ENT_QUOTES above. Silly me didn't realise ENT_COMPAT actually ignores single quotes.

Explosion Pills Over a year ago

@Cully oh right; for some reason I thought it was the other way around

Ross Smith II · Accepted Answer · 2013-04-16 01:43:41Z

1

Do this instead:

$team_name = "Ranieri&#39;s Ghost";
$team_name_converted = html_entity_decode($team_name, ENT_COMPAT, "UTF-8");

$val = mysqli_real_escape_string($con, $team_name_converted);
mysqli_query($con,"INSERT INTO mydbname (team_name) VALUES ('$team_name_converted') ");

echo $team_name_converted;

answered Apr 16, 2013 at 1:43

Ross Smith II

12.2k1 gold badge41 silver badges46 bronze badges

Collectives™ on Stack Overflow

Decoding HTML Entities when putting them into a MySQL database

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related