Perl DBI insert of hash

Question

How can this method that inserts a hashref into a database table be improved? It assumes that the columns of the database table are named identically to the fields in the hashref. The return value is the id field created by the insert.

sub insert(){
    my ($table, $data) = @_;
    my ($fields, $placeholders, $values) = ("", "", []);
    while (my ($field, $value) = each %$data){
        $fields.=", " if ($fields);
        $fields.=$field;
        $placeholders.=", " if ($placeholders);
        $placeholders.="?";
        push(@$values, $value);
    }
    $dbh->prepare("INSERT INTO $table ($fields) VALUES ($placeholders)")->execute(@$values);
    return $dbh->last_insert_id(undef, undef, undef, undef);
}

It assumes that $dbh is a correctly initialized global database handle.

It is used like:

&insert("animals", {
    "type"=>"cat",
    "name"=>"Patches",
    "date-of-birth"=>"November 1, 2015",
    "date-of-death"=>undef
});

Which will insert a record into the animals table:

id | type | name    | date-of-birth    | date-of-death
=====================================================
 1 | cat  | Patches | November 1, 2015 | null

Answer 1

This might be a good candidate to use SQL::Abstract in, as per DBIx::Class.

sub insert {
    # insert takes ( $table, \%fieldmap )
    my $sql = SQL::Abstract->new();
    my ( $query, @binds ) = $sql->insert( @_ );
    $dbh->prepare($query)->execute(@binds);
    return $dbh->last_insert_id(undef, undef, undef, undef);
}

This gives you a few easy features that may come in handy in the future if you change your database, like:

• easily change quoting character for tables/fields
• guard against injection abuse in user-specified key names, because your code currently assumes all keys will be safe. But this will not necessarily be true, and is a vulnerability target even under taint

Answer 2

Creating an insert() method is a good idea when working with DBI so you can reduce redundant code and standardize (or omit) your error handling.

use arrays

If you use arrays to store your fields and placeholders you can save yourself some trouble. Your declaration can become:

my(@sql_fields,@sql_values);

and the loop is simplified to:

while (my ($field, $value) = each %$data) {
    push(@sql_fields,$field);
    push(@sql_values,$value);
}

and now we can join those back together:

my $sql_fields = join(",", @sql_fields);

and since we have the same number of placeholders as fields we can create that without an extra array:

my $sql_placeholders = join(",", map {"?"} @sql_fields);

which leads to a few tweaks on your prepare:

$dbh->prepare("INSERT INTO $table ($sql_fields) VALUES ($sql_placeholders)")->execute(@sql_values);

error checking

I hope you also take some time to check for potential errors. Maybe the disk is full. Maybe a field gets renamed. Providing useful diagnostics for those sorts of things would be a good idea. Some errors could be handled without hitting the database, like avoiding invalid characters in field names.

Answer 3

You can replace your while loop with more straightforward equivalent,

$fields  = join(", ", keys %$data);
@$values = values %$data;
$placeholders = join(", ", ("?") x @$values);