Check for null results

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

I am using a Perl script to let people vote on there favorite images. In this script I connect to a DB and want to check to see if the file name of the image is already there or not, if it not there that would make this the first vote and then I would add it into the DB. Unfortunately I am a novice with T-SQL and Perl and can not figure out a way to check to see if my select statement (ran by perl) pulls results or not, I was also thinking that I might just pull all the names in the DB and use Perl's if control structure to go though the names and compare, but since that could get to be a lot of names I am not sure that is the best way. The code I have so fare is:

$filename = param('name');
$rating = param('rating');
$dbh = DBI -> connect ('dbi:ODBC:', '', '') or die "$DBI::errstr;";
$sth = $dbh -> prepare (qq~select One, Two, Three, Four, Five from Fan
+Ratings where Title = '$filename'~) or die "$DBI::errstr;";
$sth -> execute or die "$DBI::errstr;";
[download]

Any help you can give is appreciated.

Comment on Check for null results Download Code

Replies are listed 'Best First'.
Re: Check for null results by shmem (Chancellor) on Jun 27, 2007 at 00:32 UTC
Use placeholders to avoid SQL injection attacks, and don't interpolate DBI::errstr in double quotes: `$sth = $dbh -> prepare (qq~select One, Two, Three, Four, Five from Fan +Ratings where Title = ?~) or die $DBI::errstr; $sth -> execute ($filename);` [download] But there's something missing? How do you retrieve the data? Look for the fetchrow_* mehods in DBI. --shmem _($_=" "x(1<<5)."?\n".q·/)Oo. G°\ / /\_¯/(q / ---------------------------- \__(m.====·.(_("always off the crowd"))."· ");sub _{s./.($e="'Itrs `mnsgdq Gdbj O`qkdq")=~y/"-y/#-z/;$e.e && print}	[reply] [d/l]
Re^2: Check for null results by Eagle_f90 (Acolyte) on Jun 27, 2007 at 17:18 UTC
Thanks for the help, I will look up DBI and check out the fetchrow commands. As for the place holders I am not sure what your saying. is the "?" the placeholder command and when I pass a file name via execute it replaces "?" with "$filename"? Also how does passing the variable via execute and not prepare stop an injection attack?	[reply]
Re^3: Check for null results by shmem (Chancellor) on Jun 27, 2007 at 20:37 UTC
Yes, ? is the placeholder character; see the section Placeholders and Bind Values in the DBI documentation. `$sth->execute($var)` replaces the first placeholder with the contents of `$var`, taking care of proper escaping, and thus preventing SQL injection. Consider `$filename = "random.jpg'; drop table FanRatings; select 2 * 5,'"; $sth = $dbh -> prepare ( qq~select One, Two, Three from FanRatings where Title = '$filename +'~ ) or die $DBI::errstr; $sth->execute;` [download] Oops, table FanRatings gone, and 10 and the empty string returned... Whereas with placeholders, the embedded quotings in $filename will be escaped as the current database driver requires. --shmem _($_=" "x(1<<5)."?\n".q·/)Oo. G°\ / /\_¯/(q / ---------------------------- \__(m.====·.(_("always off the crowd"))."· ");sub _{s./.($e="'Itrs `mnsgdq Gdbj O`qkdq")=~y/"-y/#-z/;$e.e && print}	[reply] [d/l] [select]
Re^3: Check for null results by Eagle_f90 (Acolyte) on Jun 27, 2007 at 21:42 UTC
Thanks for all the help.	[reply]
Re: Check for null results by graff (Chancellor) on Jun 27, 2007 at 03:53 UTC
The first reply should help a lot -- use placeholders, and get acquainted with the "fetch..." functions in DBI. As for this part: I ... can not figure out a way to check to see if my select statement (ran by perl) pulls results or not, I was also thinking that I might just pull all the names in the DB and use Perl's if control structure to go though the names and compare... If the "fetch" function (e.g. "fetchrow_array") returns an empty list, there was no row in the table that matched the "Title=?" condition. So you would need to insert a new row with a vote tally of "1". But if the return from the "fetch" is not empty, one of the returned fields ought to be the current value of the vote tally, so you just increment that and update the row with the new value (using the same "where Title=?" condition, and the same filename parameter when you execute the update). Doing a query for all rows in the table would work to: you would want a query like "select Title,Tally from FanRatings", and use a loop, something like: `my %known_ratings; while ( my @ary = $sth->fetchrow_array ) { $known_ratings( $ary[0] } = $ary[1]; }` [download] You now have a hash whose keys are the current set of known "Titles" (filenames); as you look at an incoming file name, if it exists as a hash key, it's already in the table, so increment/update the tally; otherwise, insert a new row (and add the new filename as a new hash element, with a value of "1"). But this latter is better suited to a case where a single process would be handling lots of input file names and votes in one run -- there are fewer select operations (fewer database transactions) that way. If you're just doing a single filename (selecting to check for existence, then either doing a single insert or a single update), loading the whole table is a lot more work than necessary.	[reply] [d/l]