HELP! Single Quote NIGHTMARES

peggy

I thought I had it figured out. I have been adding data to a blob type field from a textarea box on a form, this way, the copy entered goes into the field as a variable.

I used addslashes to filter the pesky quotes before they got put in.

Then, I went into phpMyAdmin, and tried to update a record. phpMyAdmin gave me an error because the data, lo and behold, had no slashes. Why? Why? Where did they go?. However, on another page, I am pulling that very same data out by way of a query, and using stripslahes to filter it before it hits the page, and it shows up perfectly. Why? Why is it showing up okay, but phpMyAdmin is telling me it has errors because of quotes?

I thought I had finally licked the quote problem, and here it is again, in a new confusing form. I need a definitive answer. Do blobs get rid of the slashes? What is happening?

jariizumi

Okay, phpMyAdmin is great for some things, but when it comes to quotes, etc., it often times kills things. Not the smartest thing to trust quotes with.

When you take ANY data via POST or GET, PHP will put in MagicQuotes as they're called. Thus, if I type in

Didn't he say "Linux"?

or some corny sentence, when it's been POST'd or GET'd, it should be in its own variable as

Didn\'t he say \"Linux\"?

which can be inserted into MySQL without a problem. Don't add extra slashes. Because it's ALREADY slashed out.

Now, when you insert that into the database, then all the escapes are evaluated, and it goes back to the way it should be. So when you select that and fetch it from the database again, you have an already escaped version where you can just echo it and see everything fine. Now if you want to reinsert that into the database without having it POST'd, you would, of course, have to addslashes() it. But once is plenty. Hope this helps 🙂

peggy

I am thrilled to learn that it is a phpAdmin problem. I have a very live operation thumping along smoothly, and people are signing up like mad, and everyone is just gooey about how much fun they are having making their own pretty little web pages.

I had visions of re-engineering the application at 3:00 a.m.

But it's not broke. I just won't use phpMyAdmin to fiddle with that part. I'll write my own app to do my own alterations.

So this "evaluating the escapes" is something that MySQL does, right? Which means, apparently, that the quotes, once they are actually in the database, sit there and do no harm, right? In my mind's eye, I see quotes and singlequotes as vicious little weapons flying through the ether to destroy all that lives at the other end. I'm sensitive because my name is O'Connor, and I foul up other people's databases all the time. My bank has to spell my name OConnor in order to keep me in their database.

kparker

I'm sensitive because my name is O'Connor, and I foul up other people's > databases all the time. My bank has to spell my name OConnor

Hmm, it seems that in the Good Old Days, the Irish were saving civilization,
not wrecking it! :-) (Sorry, couldn't resist--Cahill's book is truly wonderful!)

Anon

OK...I'm getting large "text" and "blob" data into MySQL with quotes, cariage returns, etc. I'm also getting quotes out OK. However, I am loosing the rest of the formatted string when retrieving the data, like spaces and carriage returns. How do I preserve these so what goes in, also comes out properly formatted?

peggy

If you insert text that has line returns, spaces, tabs, etc. then to show this exactly as it is on the webpage where you are pulling it out of the database and displaying it online, you need to surround with <PRE></PRE> tags. Otherwise, if you want to have everything formatted with proper HTML style formatting, you will have to include <br> tags and   tags or <p> tags in the copy you are putting into the database. That way, when you pull it out, it will already have the HTML coding that will make it look right on an HTML page.

jariizumi

You should use nl2br() for the <BR> conversion, that is, if you want to use HTML formatting. Also, str_replace() and replace " " with "  " will also help with the spacing consistency.