Beefy Boxes and Bandwidth Generously Provided by pair Networks
No such thing as a small change
 
PerlMonks  

MySQL Double Quotes Problem(")

by Spidy (Chaplain)
on Dec 20, 2005 at 04:43 UTC ( #517965=perlquestion: print w/ replies, xml ) Need Help??
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);


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

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

Thanks,
Spidy

Comment on MySQL Double Quotes Problem(")
Select or Download Code
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);
    All the best! :-)

      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.

Re: MySQL Double Quotes Problem(") (html)
by tye (Cardinal) 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        

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

      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        

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://517965]
Approved by sk
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others romping around the Monastery: (13)
As of 2014-07-29 13:36 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My favorite superfluous repetitious redundant duplicative phrase is:









    Results (217 votes), past polls