Inserting quotes in MySQL

Pepe

When I do a quoted INSERT into a DB I use addslashes($text), the problem is that it also inserts the extra back slashes, and when I read the DB I get these ugly '\hello'\

Is there any way to delete these slashes from the DB without stripping the text?

Hope you understand me.

Thank you

Pepe Peinado

bernouli

INSERT INTO table (article) VALUES ('\'test\'');

drudnev

Why won't you just do reqular expresions replace function before putting stuff in to the DB.

ereg_replace("'+", "\'", trim($text));

[deleted]

"the problem is that it also inserts the extra back slashes"

No it doesn't actaully, the slashes are only put into the query-text to seperate the quotes in the content from the quotes in the statement so the query-parser knows what-is-what.

The slashes you see when you read the data come from the magic-quotes function in PHP.

With magic-quotes "on", PHP does the 'addslashes' function on incoming data automatically. Those are the slashes you see, the are NOT in the database itself.

And ofcourse you can remove the slashes again using stripslashes().

radua

actually, I never got a problem with that ...

doing INSERT INTO table VALUES ('$text', $variable1, '$variable2') ...

[deleted]

That depends on how you set your magic_quotes.

Magic-quotes=on will add slashes to all incoming data, which means you also get slashes before all quotes in data from POST and GET methods (forms). When that happens you don't have to add slashes before inserting the data into the DB. But, then you also get the slashes when you read data from the DB.

Turning magic-quotes off means you don't get slashes on POST and GET data, you do have to use addslashes, but you also don't have to use stripslashes.

And in a database you usually SELECT about 10 times as much as you insert, so it makes sense to trurn magic-quotes off and use addslashes() for the inserts. That saves a ton of calls to stripslashes().

amansabharwal

why don;t you convert the quotes(") to " using
htmlentities() or htmlspecialchars() functions.

If you wana to display just in web browser this is perfect.
and more over if you are taking the input from the user and storing it in the database i will suggest you to use htmlentites() and then store because user might enter the php code or html code and when you display the same it will affect your site.

and if you wana to again convert the html entities back to actual chars you can use the class html_reverse
description of html_reverse

Reverse htmlentities (un_htmlentities), sample: ã = ã
and un_accent, sample: ã = a

It is available for downlaod at http://www.phpclasses.org/browse.html/package/728.html

Pepe

Ok, all your suggestions have been very helpful.

Pepe Peinado🙂

[deleted]

You should never ever use htmlentities() or nl2br() for inserting data into a database, because that is never what you want. You waste a lot of resources to do womthing pointless.

Only use htmlentities() when you print the data in an HTML page (and even then it is not required at all)

Use addslashes() for inserting data into a database, and nothing else.