1

-- 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

John Mellor
  • 2,351
  • 8
  • 45
  • 79
  • 2
    You shouldn't keep HTML-escaped data in your database in the first place. HTML-escaping should occur only when you actually render your data in HTML. – lanzz Oct 19 '12 at 15:44
  • I agree, like I said this is inherited from another system not maintained by me. I guess I could htmldecode the data and update it so that it's not saved in escaped format. – John Mellor Oct 19 '12 at 15:45

1 Answers1

0

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
Rauli Rajande
  • 2,010
  • 1
  • 20
  • 24
  • I realise this and mentioned it in my question (although I said str_replace). The only problem is that I can see in the database there are some rows with an in that column. Like I said I don't know what the previous script did to the string before inserting, I was assuming there was some other parser that did things this way a mere preg_replace or str_replace may not cover all the possibilities. – John Mellor Oct 19 '12 at 16:32
  • If you are sure, that all conversions in your db are htmlspecialchars, then you can try to do it in other way using htmlspecialchars_decode() on both and compare after that. – Rauli Rajande Oct 19 '12 at 16:43