If the former, something like

my $col="insert into table mytable (";
my $val="values (";
foreach (param()) {
  $col.="$_,";
  $val.="?,";
  push(@arr,param($_)); # Beware! this will break if any variables hav
+e multiple values.
}
$val=~s/,$//;
$col=~s/,$//;
$statement="$col) $val)";
$sth=$dbh->prepare($statement);
$sth->execute(@arr);
[download]

my $sth=$dbh->prepare("insert into table mytable (name,value) values (
+?,?)");
foreach (param()) {
  $sth->execute($_,param($_)); # will only insert the first value if a
+ variable has multiple values
}
[download]

Thanks Matija for your help. My table has column names same as field names in the form. So I think 1st part of the code will work for me. However I am not sure about one thing -

what happens if a field in the form is left blank? Will $val contain null for that field?

If the browser doesn't pass the empty field (which most browsers don't I think), then it won't be set at all (that is part of the purpose of enumerating the fieldnames in the INSERT statement) and it's value will be whatever default you set up for the table - most probably NULL or empty string.

If the browser does pass fields without values entered, then they will be set to empty string in the database.

Gernerally speaking, you haven't addressed much of the problem in your question. You had better check the data coming in from the CGI or you could run into trouble very quickly. What problems specifically? Your DB will throw an error if you try to set a column that doesn't exist; if the data is too long your DB will puke, etc. You'll most likely want a slick module like CGI::Application::ValidateRM to help you validate input.

Also, In my code, I keep an array of valid fields name that make up my DB table and I only pass through params that match those.

You may also want to wrap your DB routines in an eval to catch the exceptions cleanly and return a friendly HTML page. Or you can:

use CGI::Carp qw(fatalsToBrowser );
[download]

Check out the tutorials here on PM.

Good luck.