DBD::mysql::st execute failed: You have an error in your SQL syntax;

[Anonymous Monk]

the below code is giving following error: DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1 at /var/www/cgi-bin/tt.cgi line 35.

#!/usr/bin/perl
     
    use warnings FATAL => 'all';
    use CGI;
    use CGI::Carp qw(fatalsToBrowser);
    use DBI;
    use DBD::mysql;
     
    my $cgi = CGI->new;
     
    print $cgi->header, $cgi->start_html;
     
    my $title     = $cgi->param('TITLE');
    my $feature  = $cgi->param('FEATURE');
    my $type     = $cgi->param('TYPE');
    my $description  = $cgi->param('DESCRIPTION');
    my $status     = $cgi->param('STATUS');
    my $path  = $cgi->param('PATH');
    my $priority = $cgi->param('priority');





    my $database = "test_case";
    my $host     = "localhost";
    my $user     = "chiragk";
    my $pw       = "";
    my $dsn      = "dbi:mysql:$database:localhost";
    my $dbh      = DBI->connect($dsn, $user, $pw,
                               { RaiseError => 1 })        or die "una
+ble to connect:$DBI::errstr\n";
     
    my $query = "insert into test(title,feature_name,type,description,
+status,test_case_path,priority) values(?,?,?,?,?,?,?,)";
    my $sth   = $dbh->prepare($query); 
    $sth->execute("$title","$feature","$type","$description","$status"
+,"$path","$priority");
    print $cgi->p("[$title],[$feature],[$type],[$description],[$status
+],[$path],[$priority]");    
    print $cgi->end_html;

[Corion]

What part of the error message do you have a problem with? I think it is fairly explicit.

As a first step, I suggest inpecting the SQL statement in your script and checking it for syntax errors, especially near every ")" of that syntax.

Note that, unlike Perl, SQL does not allow trailing commas in lists.

[perlfan]

You have a trailing comma in the VALUES section of the SQL. I'd also not bother with $sth and prepare; use the do method and put your bind values (the proper number, that is) in an array (much cleaner).

[OpenSpace]

Though using the 'do' method is less code, using 'prepare' gives you the opportunity to add another 'die' command;

my $sth = $dbh->prepare($query) or die "unable to prepare:$DBI::errstr\n";

This will show if the error is in the SQL or in the execution

Good to see the ? placeholders in use. Our server failed a penetration test as my boss put the variables straight into the SQL statement! :)

I don't think you need the double quotes around the variables provided to the execute method, wont they take care of themselves?

[perlfan]

It's a personal preference, but all you need is RaiseError set to 1 (as you have).