Beefy Boxes and Bandwidth Generously Provided by pair Networks
laziness, impatience, and hubris
 
PerlMonks  

SQL Statement Not Firing Correctly?

by jdlev (Scribe)
on Nov 28, 2013 at 08:10 UTC ( #1064760=perlquestion: print w/ replies, xml ) Need Help??
jdlev has asked for the wisdom of the Perl Monks concerning the following question:

I haven't been able to figure this out. The program is throwing an error when I execute the update statement. All of the other regex expressions seem to work just fine, but when a person has an appostrophe in their name, the sql syntax get's messed up. I tried adding a second apostrophe in front of the one in the person's name, and it WORKS in phpmysql when I run the SQL statement. But when I run it in the following code, it doesn't work????
my $query = "SELECT Name FROM Table WHERE Name= 'Will Tu''ufo''ou'"; my $sth = $dbh->prepare($query); $sth->execute(); while($name = $sth->fetchrow_array) { $originalName = $name; $name =~ s/jr//i; #removes 'jr' from names $name =~ s/III//i; #removes 'III' from names $name =~ s/\s+$//; #removes ' ' from end of name #print $name . "\n"; if ($originalName =~ m/'/) { $originalName =~ s/'/''/g; print $originalName; } $updateQuery = "UPDATE week13dk SET Name = '$name' WHERE Name += '$originalName'"; my $uth = $dbh->prepare($updateQuery); $uth->execute(); }
I love it when a program comes together - jdhannibal

Comment on SQL Statement Not Firing Correctly?
Download Code
Re: SQL Statement Not Firing Correctly?
by Corion (Pope) on Nov 28, 2013 at 08:17 UTC

    Consider DBI->quote or, even better, using SQL placeholders instead of constructing your SQL statement as a string.

    Also see Bobby Tables.

      I strongly second Corion's suggestions. I would only add that failing to use either (especially placeholders IMO) can lead to some serious security issues in some cases.

      It helps to remember that the primary goal is to drain the swamp even when you are hip-deep in alligators.

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others having an uproarious good time at the Monastery: (7)
As of 2014-07-12 21:31 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    When choosing user names for websites, I prefer to use:








    Results (241 votes), past polls