Beefy Boxes and Bandwidth Generously Provided by pair Networks
There's more than one way to do things

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

Replies are listed 'Best First'.
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?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1064760]
Approved by marto
and the leaves swirl about...

How do I use this? | Other CB clients
Other Users?
Others lurking in the Monastery: (8)
As of 2018-03-17 19:10 GMT
Find Nodes?
    Voting Booth?
    When I think of a mole I think of:

    Results (225 votes). Check out past polls.