Re: Module and variable place holders?

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

--
edan

Comment on Re: Module and variable place holders? Download Code

Replies are listed 'Best First'.
Re^2: Module and variable place holders? by neilwatson (Priest) on Aug 29, 2004 at 15:14 UTC
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	[reply]
Re^3: Module and variable place holders? by Tuppence (Pilgrim) on Aug 30, 2004 at 02:20 UTC
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.	[reply] [d/l] [select]
Re^3: Module and variable place holders? by tilly (Archbishop) on Aug 30, 2004 at 15:36 UTC
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.	[reply]