Database contains #39 instead of #039

Question

-- Sytem is MySQL, PHP, Apache and the code is built around the Codeigniter Framework

EDIT FOR CLARITY: I am not storing data, I am trying to retrieve data that was stored some years ago (badly as escaped data). In the database the name Fred' is stored as Fred&#39 yet when I convert Fred' using htmlspecialcahrs it comes out as Fred&#039. My question is what do I need to do to make Fred' convert to Fred&#39 and any other equivalents?

Original Question

I've inherited a database from another system (Invision Power Board to be exact). The site is now custom coded using Codeigniter but is using the same member database from the old Invision Power Board site.

I've now discovered a problem where by if a user has an apostrophe in their name e.g. "Fred'" codeigniter's built in html_escape function (which just uses htmlspecialchars) converts it to Fred&#039

Yet in the database the name is saved as: Fred&#39 and thus the lookup fails.

I'm not sure what Invision Power Board was doing to the string before inserting it into the database, but does anyone have any idea how I could ensure that it is converted to &#39 instead of &#039 ?

Simply saying do a str_replace or change the data in the db is not useful as there are hundreds of possibilities for what could be in a users name. A quick search for users with a # in their name (presumably a special char) shows up 440 users who are currently unable to login due to this bug in our site.

EDIT: Fixed some formatting to remove ";" so it doesn't just display an apostrophe

Answer 1

You can use preg_replace() to remove 0's from php generated string before comparison:

$string = 'Fred&#039';

$string = preg_replace('/&#0+([1-9]+)/', '&#$1', $string);

var_dump(str_split($string));
// str_split to show real result