Search code examples
sqlperldbidbd

Perl- insert data into SQL while reading the columns from an array?

The question is simpler than it sounds(i think). I have a database table called table and I'm trying to insert data in the column called first. Here is a part of the code:

 my $stmt = $dbh->prepare($sql);
@array=("first","second","third");    
$sql = "INSERT INTO table($array[0]) VALUES(?)";
$stmt->execute($some_value);

The program runs without warnings, but it simply fills the table with 0, which is different from $some_value.

Solution

  • First of all, naming a table table is a bad idea because it is likely that you have to escape its name every now and then.

    Secondly, your statements are in the wrong order. You firstly prepare some statement (with unknown content) and only then assign an SQL statement to that variable.

    Try this:

    my @columns = ("first","second","third");    
my $sql = "INSERT INTO table(" . $dbh->quote_identifier($columns[0]) . ") VALUES(?)";
my $stmt = $dbh->prepare($sql);
$stmt->execute($some_value);

    I didn't test this, but it may be necessary to write

    my $sql = "INSERT INTO `table` (" . $dbh->quote_identifier($columns[0]) . ") VALUES(?)";

    because table is a keyword in SQL. Better name it according to the things it contains, not to its shape.

    As @ikegami pointed out in a comment, it's also better to let the DBI driver quote the column names (with quote_identifier() instead of using them directly), because it's always dangerous to build SQL statements from (untrusted) variables.

    Perhaps you are confused how statement preparation works. The basic statement cannot be varied after a call to prepare, which means in

    INSERT INTO mytable (col1, col2) VALUES (?,?)

    you can only supply different values for the two ? placeholders and then re-execute the very same statement with different values for these ?s. For example, it's not possible to write

    INSERT INTO ? (?, ?) VALUES (?,?)

    and then prepare that statement and then run 

    $sth->execute('my_table', 'col1', 'col2', 'val1', 'val2');

    This won't work.

    If you want to run two different INSERT statements (for two different columns), then you need to prepare twice. Depending on your actual case you can either

    my $stmt_for_col1 = $dbh->prepare("INSERT INTO my_table (col1) VALUES (?)");
my $stmt_for_col2 = $dbh->prepare("INSERT INTO my_table (col2) VALUES (?)");

    and then execute the two statements as often as you like and in arbitrary order:

    $stmt_for_col1->execute('value_for_col1');
$stmt_for_col2->execute('value_for_col2');
$stmt_for_col1->execute('value_for_col1');

    or use only one statement at a time:

    my $stmt = $dbh->prepare("INSERT INTO my_table (col1) VALUES (?)");
$stmt->execute('value_for_col1');
$stmt->execute('value_for_col1');

$stmt = $dbh->prepare("INSERT INTO my_table (col2) VALUES (?)");
$stmt->execute('value_for_col2');
$stmt->execute('value_for_col2');

    The prepare step is done once per statement, the execute step can (and often is) run in a loop to make the statement execute with different values.