MySql statement variable not working

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

I have cgi script using PERL and mysql variables the second sql statement is not working. The $id keeps coming up empty. any help would be appreciated.


my $sql ="select gene_id from gene_product where gene_desc like '%heat
+%'";
my $sql1="select *  from gene where gene_id = '$id'";

my $rows_ref = $db->sqlexec($sql, '\@@');
my $key_word_json = '';
foreach $row_ref(@$rows_ref){
        my $key_word = $row_ref->[0];


           $key_word=~s/_G//;
           print $key_word;
        push(@keyword, $key_word);
        }


my @results;

foreach my $ids(@keyword) {

my $id=$ids;
print $id;
print $sql1;
        my $rows_ref = $db->sqlexec($sql1, '\@@');
        foreach my $row_ref (@$rows_ref){
                push(@results, $row_ref);
        }

        print $row_ref;
}

print Dumper(@results);
[download]

Comment on MySql statement variable not working Download Code

Replies are listed 'Best First'.
Re: MySql statement variable not working by Eliya (Vicar) on Feb 29, 2012 at 15:50 UTC
The $id keeps coming up empty. That's because `$id` interpolates into the double-quoted string at the time the (Perl) statement is executed. When you later assign some value to `$id`, that value won't magically be updated in the string. (Under the hood, interpolation is implemented as concatenation, i.e., it's essentially just another way of saying `"select * from gene where gene_id = '" . $id . "'"`.) In other words, move `my $sql1="select * from gene where gene_id = '$id'";` [download] down to after you've assigned something to `$id`. That said, it would be better still to use a placeholder...	[reply] [d/l] [select]
Re^2: MySql statement variable not working by glcld (Initiate) on Feb 29, 2012 at 16:46 UTC
thanks	[reply]
Re: MySql statement variable not working by locked_user sundialsvc4 (Abbot) on Feb 29, 2012 at 18:52 UTC
Placeholders would definitely be the way to go here, IMHO. Your SQL statement would be `... where gene_id = ?` (notice that the question-mark is not quoted), and when you prepare or execute the query you supply `$id` as the only entry in an array of values. Where this really starts to pay off is when you need to execute the same query many times, only with different values. You “prepare” the statement once, then execute it as many times as you wish, providing a different `$id` each time. All of the work involved in “deciding how to carry out this query” only occurs one time. You never have to worry about SQL-injection or any of that nonsense. I frankly make it a point to do every SQL query this way. I never, ever include literal values in the string. Placeholders are more expressive, less difficult, and technically superior.