$quoted = $dbh->quote($raw);
[download]

But it's much better to use the placeholders, as they can be trained in the SQL datatypes so as to quote correctly for those DBDs that have specific needs.

-- Randal L. Schwartz, Perl hacker

OK, so, to do it right, I'd want :

sub create_SQLprepared{
    my ($l_database, $l_tablename,@l_fields)  = @_;
    my $l_fieldnames;
    my $l_placeholder;
    my $l_statement;

    my $l_fieldnames = join ",", (@l_fields);
    foreach (@l_fields) {$l_placeholder .= "?"}

    $l_placeholder = (join ",", (split //, $l_placeholder));
    print "INSERT $l_tablename (" . $l_fieldnames . ")\n VALUES (" . $
+l_placeholder . ")";
    $l_statement = $l_database->prepare("INSERT $l_tablename (" . $l_f
+ieldnames . ")\n VALUES (" . $l_placeholder . ")";
    return (\$l_prepare);
}
[download]

? Where $l_database is a reference to a database handle, $l_tablename is a string, and @l_fields is a list of field names for the insert. Returns a reference to the statement handle.

Still seems far too clunky.

sub create_SQLprepared {
  my ($db, $table, @fields) = @_;
  $db->prepare(
    "INSERT $table (".
    join(", ", @fields).
    ") VALUES (".
    join(", ", ("?") x @fields).
    ")"
  );
}
[download]

-- Randal L. Schwartz, Perl hacker

Yes, although a simpler way to generate $l_placeholder is:     my $l_placeholder = join ',', ('?') x @l_fields; ('?') x @l_fields creates a list of question marks, one for each element in @l_fields.

(See my node on Variable placeholders with DBI.)

It is not always possible to use placeholders with insert statements (Sybase and MS SQL server come to mind as not implementing these yet for anything other than a select), so you need to use $dbh->quote() in these cases, or if you want to be comaptible across a number of types of databases.

I thought that DBI is smart enough to emulate the placeholder functionality for servers that don't implement it natively.

---

I like computer programming because it's like Legos for the mind.