You'll be wanting to use placeholders, which are described in further detail in the DBI docs. This gist of it would be the following, in the context of your code:

sub dbh{
    my ($statement, $dbh2, @values) = @_;
    my $sth = $dbh2->prepare($statement) or die "dbh2 = $dbh2 Couldn't
+ prepare statement: $statement ".$dbh2->errstr;
    $sth->execute(@values) or die "dbh2 = $dbh2 Couldn't execute state
+ment: $statement ".$dbh2->errstr;
    return $sth;
}

# ........................
sub insert_lexicon{

    my ($self) = @_;

    $statement = 'INSERT INTO lexicon (
        word,
        meaning,
        auid) VALUES(
        ?,
        ?,
        ?)';

    return ($statement);
}
[download]

You basically put question-marks in the SQL statement, and pass a list of values to the $dbh->execute() method.

HTH

I thought about place holders. If I do that then I believe I will have to go over a bunch of if-elsif statements so that certain values are passed to execute based on what $action is (it is defined from several forms e.g. "insert_lexicon, update_lexicon".

Neil Watson
watson-wilson.ca

You would need some code, at any rate. Personally, I solved that part of the problem by keeping an array of bind parameters, and then bits of SQL that form that part of the WHERE clause. I would assume the same theory would hold with insert and update.

sub options_sql_filter
{
   my($self, $request) = @_;
   my @options = $self->options;
   my @sql_bits;
   my @bind_params;
   foreach my $option (@options){
       my($sql_bit, @bind_param) = $option->sql_filter($request);
       push @sql_bits, $sql_bit;
       push @bind_params, @bind_param;
   }
   return (join ' AND ', @sql_bits), @bind_params;
}
[download]

sql_filter here returns a list that looks like ('foo = ?', 'bar')

This lets you piece together SQL that uses bind parameters but is generated dynamically.

Doing that work is better than leaving major security holes in your application. See Use placeholders. For SECURITY! for a brief explanation of what SQL injection attacks are, why you're vulnerable, and why you should care.

print ' blah blah @this blah $that blah $that $blah $blah $blash $blah
+ $blah';
[download]