24 Jan, 2009, Zeno wrote in the 1st comment:
Votes: 0
There are so many open source projects out there that use mysql_real_escape_string.

The data (stored in a database) I'm using some projects on is generally plain text. Tracking down an issue I lead me to mysql_real_escape_string.

I have this data:
<a title="link to eric grabe's web profile" href="http://www.getmynameout.com/ericgrabe" target="_blank">Eric's web site</a>

Which mysql_real_escape_string changes to:
<a title=\"link to eric grabe\'s web profile\" href=\"http://www.getmynameout.com/ericgrabe\" target=\"_blank\">Eric's web site</a>

The problem being is that it didn't escape the ' in Eric's.

Eventually I viewed this data in vim and saw this:
<a title="link to eric grabe's web profile" href="http://www.getmynameout.com/ericgrabe" target="_blank">Eric<92>s web site</a>

<92> says it is ASCII (<<92>> 146, Hex 0092, Octal 222): a single quote.

So mysql_real_escape_string is not handling those. I can't change the data because there is so much of it.

Is there some sort of server setting I can change to "fix" this?
24 Jan, 2009, David Haley wrote in the 2nd comment:
Votes: 0
You're in PHP right? You might want to consider doing a preprocessing step of using a regex to replace hex92 with whatever character code the normal single quote uses. Note that hex92 is not the standard single quote: that would be hex27.
24 Jan, 2009, Zeno wrote in the 3rd comment:
Votes: 0
Yeah, PHP.

I have hundreds of account that could potentially have this issue.

Nothing a bit more standard? :P

Where is this special quote anyway?
http://www.cdrummond.qc.ca/cegep/informa...
24 Jan, 2009, David Haley wrote in the 4th comment:
Votes: 0
Note that the extended ascii chart isn't necessarily standard, which is probably why you're not seeing it in that chart.

See for example this page or this page that talk about where this comes from (normally it's MS Word).

If you have all this data lying around, can't you just fix it once and be done with it? Or is the data dynamic somehow?

Incidentally, why do you care anyhow? Is MySQL getting tripped up on the right single quote?
24 Jan, 2009, Zeno wrote in the 5th comment:
Votes: 0
I care because this is happening:
-mysql_real_escape_string is being used to export data from a 1.0.x Joomla version (using a migrate component).
-That data is then imported during the installation into a Joomla 1.5.x install (migrate is a built in feature during install).
-Thus when the data is in Joomla, the parts where <92> are is where the entire page cuts off (I think the insert cuts off there due to no escape).

I could fix the data, but it's not going to stop clients from continuing to add <92> to new content.
24 Jan, 2009, David Haley wrote in the 6th comment:
Votes: 0
Do you control where the data is entered? If so, you could fix it there. It sounds like you have some control somewhere in the process, so you could fix it somewhere at least as it happens.

These funky quotation marks are a fairly well-known annoying problem FWIW.
24 Jan, 2009, Kline wrote in the 7th comment:
Votes: 0
I think I ran into this, or something very similar, with mysql_real_escape_string in one of my MUDs. Any single quotes would break my query as the query treated them as a delimiter for its arguments for some reason. I ended up just stripping them out of the string before handing it off to mysql_real_escape_string, as it was only affecting chat logging, so it wasn't a big issue for me luckily.
24 Jan, 2009, Zeno wrote in the 8th comment:
Votes: 0
DavidHaley said:
Do you control where the data is entered? If so, you could fix it there. It sounds like you have some control somewhere in the process, so you could fix it somewhere at least as it happens.

These funky quotation marks are a fairly well-known annoying problem FWIW.


I have full control over anything.

But fixing it for each account? Then when I go to upgrade the CMS I have to ensure I don't overwrite the code change for each account? I know I could have done this already, but it really doesn't feel reasonable to do.
24 Jan, 2009, David Haley wrote in the 9th comment:
Votes: 0
What do you mean, fixing it for each account? Do you have local installs of the software for everybody or something? (why?)

Surely somebody else has had this issue with the software before? Have you tried asking their forums?
24 Jan, 2009, Zeno wrote in the 10th comment:
Votes: 0
There's an installation of the CMS for each account, yes. If everyone shared one source, that would be a huge problem.

But the the issue isn't with the software. It's with mysql_real_escape_string.
24 Jan, 2009, David Haley wrote in the 11th comment:
Votes: 0
Since you have full control over everything, just fix mysql_real_escape_string. Or change all instances where it's called to call some other function. Or if you can't do that, it sounds like you'll have to live with changing the individual CMS installs some other way. Not sure what other options you think there could be at this point.

Why is the code fully replicated for each user? Can you not simply symlink to the common code, and give each user an individual config file?
24 Jan, 2009, Tyche wrote in the 12th comment:
Votes: 0
Zeno said:
<a title="link to eric grabe's web profile" href="http://www.getmynameout.com/ericgrabe" target="_blank">Eric<92>s web site</a>

<92> says it is ASCII (<<92>> 146, Hex 0092, Octal 222): a single quote.

So mysql_real_escape_string is not handling those. I can't change the data because there is so much of it.


Why would it? The above isn't the ascii code for a single quote.
There's nothing wrong with mysql_real_escape_string
That is it doesn't need to be escaped, it can be stored as is safely.
Sounds to me like this Joomla thing is broken.
24 Jan, 2009, Zeno wrote in the 13th comment:
Votes: 0
DavidHaley said:
Since you have full control over everything, just fix mysql_real_escape_string. Or change all instances where it's called to call some other function. Or if you can't do that, it sounds like you'll have to live with changing the individual CMS installs some other way. Not sure what other options you think there could be at this point.

I've never changed something like mysql_real_escape_string. I'll check it out.

Quote
Why is the code fully replicated for each user? Can you not simply symlink to the common code, and give each user an individual config file?


To quote a Joomla dev:
Quote
It's possible. (I did it at one point in time), but not practical (there are A LOT of things that would need to change to get a smooth, complete install).. Not to mention some workflow paradoxes (When installing a component, do you install it to all DB's, or only the current, or what)…
24 Jan, 2009, David Haley wrote in the 14th comment:
Votes: 0
I'm still a little confused as to why this is necessary in the first place. As Tyche and I have both pointed out, hex92 is not a single quote and so shouldn't be confusing MySQL. I'm not sure why it's confusing Joomla, unless Joomla itself is replacing hex92 with an actual single quote somewhere.

There's a Joomla forum post about this, actually… looks like you can set up Joomla to react to these things differently.
25 Jan, 2009, Zeno wrote in the 15th comment:
Votes: 0
Haha, HDG = me.
03 Feb, 2009, Rojan QDel wrote in the 16th comment:
Votes: 0
If you have not fixed this issue yet, here is my suggestion: Make a script to run through every row in your database, and apply this to every field and then update the database. You will only need to run this once, and then mysql_real_escape_string will work. However, I also suggest updating any input forms to do the same str_replace

str_replace("'","'",$rowdata); //Make sure the first field has the funky symbol, and the second field has the proper one: '
03 Feb, 2009, Zeno wrote in the 17th comment:
Votes: 0
Only run this once? So when people put in new data every day and that gets inserted again, why wouldn't I have to run it again?
03 Feb, 2009, Rojan QDel wrote in the 18th comment:
Votes: 0
If you run it on every piece of entered data, you wont need to run it on the whole database again. Basically you're retro-filtering the database to fix the errors, and then setting it up to filter all new data as well so no new errors appear.
03 Feb, 2009, David Haley wrote in the 19th comment:
Votes: 0
There's an echo in here, echo in here, echo in here… :tongue:
03 Feb, 2009, Zeno wrote in the 20th comment:
Votes: 0
Rojan QDel said:
If you run it on every piece of entered data, you wont need to run it on the whole database again. Basically you're retro-filtering the database to fix the errors, and then setting it up to filter all new data as well so no new errors appear.

That means I have to do this code change on every CMS installed on the entire server, and that just isn't viable (there are hundreds installed and updating each one while keeping what I edited would be a pain, yes?).
0.0/22