MySQL Double Quotes Problem(")

Spidy has asked for the wisdom of the Perl Monks concerning the following question:

I have a system where users contribute things, and once they are approved they're added to the database. However, recently users have been contributing things with double quotes (") inside them, and all of the content past the double quote is wiping out whatever was supposed to be put into the MySQL table. Here's the code:

            $sth = $dbh->prepare("INSERT INTO ljokes(content,contribid
+) VALUES (?,?)");
            $sth->execute($editpage,$uid);
[download]

$editpage has the contents to put inside the table in it. I've tried running it through these filters:

        $editpage =~ s/\"/\\"/g;
        $editpage =~ s/\"/\&quot\;/g;
[download]

But to no avail. Does anyone have any ideas on what's going wrong here?

Thanks,
Spidy

My Website
Get Eated!

Comment on MySQL Double Quotes Problem(") Select or Download Code

Replies are listed 'Best First'.
Re: MySQL Double Quotes Problem(") (html) by tye (Sage) on Dec 20, 2005 at 07:51 UTC
Double quotes won't cause a problem for the code that you've shown. So the problem is likely elsewhere. I'll bet that the problem is actually an HTML problem... - tye	[reply]
Re: MySQL Double Quotes Problem(") by blm (Hermit) on Dec 20, 2005 at 04:49 UTC
use `my $quotedString = $dbh->quote( $string );` to take care of quotes.So `my $quoted_editpage = $dbh->quote($editpage); $sth->execute($quoted_editpage,$uid);` [download] All the best! :-)	[reply] [d/l] [select]
Re^2: MySQL Double Quotes Problem(") by chromatic (Archbishop) on Dec 20, 2005 at 07:36 UTC
He's already using placeholders. I think everyone needs to see more code to be able to diagnose more effectively. I can't see any reason this oughtn't work from the code posted. Perhaps there's something going wrong in the output routine.	[reply]
Re: MySQL Double Quotes Problem(") by Spidy (Chaplain) on Dec 20, 2005 at 16:59 UTC
I have not gotten a single error showing up while running this code, and it can't be a problem with the output routine, because when I go into phpmyadmin and actually check the values of the table, the 'content' column will always be blank following the first double quote. My Website Get Eated!	[reply]
Re^2: MySQL Double Quotes Problem(") by injunjoel (Priest) on Dec 20, 2005 at 19:19 UTC
Greetings, If you are using phpmyadmin you are limited by HTML for display and PHP for dealing with the quotes when the come back out of your database. My thoughts are that the problem is somewhere between PHP and HTML. Looking at the page source to see if the value is the same as what is being displayed would be a good first step (is it truncated in the source as well?), but I would suggested using the tools that come with mysql (Im talking command line) to validate that the double quotes are truncating the values being inserted. Normally if it is and issue with quotes being inserted, be them single or double, then DBI or the corresponding DBD will warn you about that. Just a thought. -InjunJoel "I do not feel obliged to believe that the same God who endowed us with sense, reason and intellect has intended us to forego their use." -Galileo	[reply]
Re^2: MySQL Double Quotes Problem(") (input) by tye (Sage) on Dec 24, 2005 at 19:36 UTC
Actually, my guess was that is was a problem with how you get the input. Not getting errors reinforces the conclusion that it isn't an SQL problem and is consistent with the problem being with how you get input. - tye	[reply]
Re: MySQL Double Quotes Problem(") by arkturuz (Curate) on Dec 20, 2005 at 09:54 UTC
Do you get some MySQL errors while executing the query? I had simiral problem with single quotes and after `s/'/\'/g` it worked.	[reply] [d/l]