Re: Escaping then un-escaping an apostrophe

Don't use raw sql statements like this:

$SQL = "UPDATE $regtable SET favorites='$favorites' WHERE ID='$ID'";
[download]

Use placeholders instead.

my $sth = $dbh->prepare(qq{UPDATE $regtable SET favorites=? WHERE ID=?
+});
$sth->execute($favorites, $ID) or die $dbh->errstr;
[download]

That way the escaping is done for you, and you avoid sql injection attacks.

Also, I advise you to use strict and use warnings. It appears that you're relying on global variables instead of passing parameters to your subs. This is very bad practice.

Comment on Re: Escaping then un-escaping an apostrophe Select or Download Code

Replies are listed 'Best First'.
Re^2: Escaping then un-escaping an apostrophe by htmanning (Friar) on Mar 10, 2011 at 20:53 UTC
Thanks for the suggestions. Here is the Do_Sql sub referred to in my code. Is this still bad practice? `$SQL = "UPDATE $regtable SET favorites='$favorites' WHERE ID='$ID'"; &Do_SQL; sub Do_SQL{ eval { $sth = $dbh->prepare($SQL); }; # end of eval # check for errors if($@){ $dbh->disconnect; print "Content-type: text/html\n\n"; print "An ERROR occurred! $@\n"; exit; } else { $sth->execute; } # end of if/else return ($sth); }` [download]	[reply] [d/l]
Re^3: Escaping then un-escaping an apostrophe by wind (Priest) on Mar 10, 2011 at 21:09 UTC
Yes, there are a few problems with that code: 1) Your subroutine relies on the global variable $SQL, instead of a passed parameter. 2) Your $SQL statements are vulnerable to injection attacks since they aren't escaped and/or you're not using placeholders. 3) You rolling your own to browser error reporting, instead of letting CGI::Carp do the work for you. `#!/usr/bin/perl -wT use CGI; use CGI::Carp qw(fatalsToBrowser); use DBI; use strict; use warnings; ... my $sth = $dbh->prepare(qq{UPDATE $regtable SET favorites=? WHERE +ID=?}); $sth->execute($favorites, $ID) or die $dbh->errstr; ...` [download] The above is all you need to accomplish the same thing. If there is an error with your statement, then CGI::Carp will trap it and display it in the browser and also the error log.	[reply] [d/l]
Re^3: Escaping then un-escaping an apostrophe by Anonymous Monk on Mar 10, 2011 at 21:07 UTC
Congratulations, you appear to be preventing sql injection, but your program flow is buried -- a function called Do_SQL usually has no business calling exit Try something like this `Main(@ARGS); exit(0); sub Main { my $query = CGI->new; my $ret = eval { DoSql($query); }; if( $@ ){ print_error_response(); } else { print_results($ret, $query); } }` [download] See CGI::Application/Catalyst/CGI::Prototype for different ways of simplifying this separation of concerns	[reply] [d/l]