your first three points on security are absolutely correct. however, after reading on prepare_cached(), i don't think that's my solution. maybe i'm wrong, but it just doesn't seem much more secure to me than prepare(). and i think that doing further checking on my input and a sanity check on the final query, that i be much better. in fact, since you brought it up, i found this great node here that has tons of pointers:

http://www.perlmonks.org/?node_id=756058

and this discussion on sql injection

http://unixwiz.net/techtips/sql-injection.html

however, i think the first thing i'm going to be reading is the 'perlsec' doc mentioned at the top of that pm node. as for my code, i finally figured out how to make it more readable and usable.

since i'm wanting items based on each word (which lines a word is in and which strings from sql a word returns) i use %data{ $word } and put all associated data on that.

it's late, so this has issues that i need fixing, but my general idea is:

        while ( my $fields =  $sth->fetchrow_arrayref ) {
                foreach my $field (@fields) {
                        definition( $line, $word, $field );
                }
        }

}

$dbh->disconnect;


for my $word ( keys %data ) {
        while( my ($field, $type) = each %{ $data }{ $word } ) {
                print "$word,$field" if( $type eq 'field' );
                while( my ($line, $type) = each %{ $data }{ $word } ) 
+{
                        print ",$line" if( $type eq 'line' );
                }
        }
}


sub definition {

        my ($line, $word, $field) = @_;

        if( defined( $field ) {
                if( !defined( $data{ $word }{ $field } ) ) {
                        $data{ $word }{ $field} = field;
                }
                $data{ $word }{ $line } = line;
        }
}
[download]