CODEWITHSUNDEEP
mysqlperl
norbdum
norbdum

Reputation: 2491

Adding backticks to a variable in perl

I have MYSQL database that has field names containing hyphens. Trying to insert data from XML to database using Perl.I have XML in %HEAD The keys are the column header and values are the corresponding data.

my %HEAD= ('REGISTRATION-NUMBER' => 'AAACT2727QXM003',
           'RETURN-YEAR' => '2013', 
           'MONTH' => 'July', 
           'LTU' => 'Yes',
           'NIL-RETURN' => 'No',
           'ASSESSEE-NAME' => 'TATA MOTORS LIMITED');

my @HEADER_keys= keys %HEAD;


foreach $key(@HEADER_keys) {    
    $value= $HEAD{$key};
    my $sth = $dbh->prepare("INSERT INTO indirect_taxes($key) VALUES ($value)");
    $sth->execute() or die $sth->errstr;
}

or instead of foreach

my @HEADER_values= values %HEAD;
my $sth = $dbh->prepare("INSERT INTO indirect_taxes(?) VALUES (?)");

$sth->execute_array(\@HEADER_keys, \@HEADER_values) or die "the failure cause: $DBI::errstr";

As the keys are containing hyphens i am getting MYSQL syntax error. From Can a table field contain a hyphen?

got the syntax but using perl not able to add backtick to the variable $key or @HEADER_keys.

Please suggest a way to add backticks to $keys.

Upvotes: 0

Views: 422

Answers (1)

mpapec
mpapec

Reputation: 50657

Try back sticks around $key and use sql placeholder ? to avoid sql injection

foreach my $key(keys %HEAD) {
  my $sql = sprintf("INSERT INTO indirect_taxes (%s) VALUES (?)",
      $dbh->quote_identifier($key));
  my $sth = $dbh->prepare($sql);
  $sth->execute($HEAD{$key})
      or die $sth->errstr;
}

Upvotes: 3

Related Questions