Is this old Perl and not compatible with the newer version of MYSQL?

It's a style of Perl that is not often seen anymore, but that doesn't mean that it won't actually run - even with a modern target database. The problem is that your error message is going to be 'Could not execute SQL statement ... maybe invalid?' regardless of what the actual underlying cause is. That's no use for debugging. Consider something like this instead:

$sth->execute(@bargs) or die "Could not execute '$query' with argument
+s '@bargs': " . $dbh->errstr;
[download]

That would give you already a lot more information about the problem. I've also passed an array of bind arguments @bargs to the execute call. This means using placeholders which is far, far safer than the prepare statement that you have. Definitely something else to look into.

The new server runs a newer version of PHP (version 7.X)

I do hope that's irrelevant. You aren't actually using the PHP for anything, are you?

> This means using placeholders which is far, far safer than the prepare statement that you have

Kind of an understatement, Little Bobby Tables would really love this particular entry $form{'ITEM'} ...

Cheers Rolf
_{(addicted to the Perl Programming Language :)
Wikisyntax for the Monastery}

Not really bringing new information into play, but just stressing what the others said: YOU ARE DOING IT WRONG!

The way you use SQL is extremely unsafe, and if you don't change that, you will hear somewhere in the near future "I told you so".

# prepare query
my $query = <<"EOSQL";
INSERT INTO closedauctions (
 seller, sellerusername, category, title, bids,
 pounds, description, winningbid, price_per_pound,
 highbidder, buyerusername, month, day, year,
 itemnum, filename)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
EOSQL

my $sth = $dbh->prepare ($query) or die $sbh->errstr;     

# Execute query
$sth->execute (
    $sellername, $sellerusername, $quota, $category, 1,
    $pounds, $desc, $buyit, $price_per_pound,
    $buyername, $buyerusername, $month, $day, $year,
    $form{ITEM}, $cat
    ) or die $dbh->errstr;

# Done inserting
$sth->finish;

# disconnect from database
$dbh->disconnect;
[download]

Doesn't that look a lot easier to maintain? Besides that this is safer against attacks, it is also portable. e.g. your code would horribly fail if any of the data variable contans a ".

There is a subtle difference with your code: as you force quotation (which is unreliabvle to say the least) in your statement, undefined values will be stored as an empty string in the database (which should generate tons of warnings if you are running under use strict; and use warnings; (but I fear you are not). In this rewritten example, undefined values are stored as NULL.

One last thing to note that would help you find bugs, it to enable showing DBI errors when they happen:

my $dbh = DBI->connect ($dsn, $user, $pass, {
    RaiseError         => 1,
    PrintError         => 1,
    ShowErrorStatement => 1,
    });
[download]

Thanks for your comments. I ran things with your suggested language and it flagged the issue. Most sales involve pounds of quota, but when one sells fishing gear for example, there are no pounds. In that case the pounds field went into the database as null. All of my tests were using fishing gear and the newer version of mysql was not as forgiving as the older version. But at least I now know what needs to be done.

As far as using PHP on the site, I do use it on some presentation tables displaying past sales. But for my current issue that is not involved.

In case it’s not clear from the references to placeholders and Little Bobby Tables, the way your query works is very dangerous. Someone who knows what they’re doing could cause your database to run their SQL statements with the privileges of your CGI program.

The problem is that the backslash escaping doesn’t escape everything — in particular, not the input from the form. If an attacker doctored up a form submission so that it included their own SQL in the right place, and fixed up the front and back of that string so that the whole combination of your SQL query plus whatever they submitted in $form{'ITEM'} still resulted in a valid series of SQL statements, your DB will cheerfully run the whole thing. An attacker could potentially steal customers’ private data, submit bogus transactions, or clear the whole database.

It’s error-prone at best to pre-process $form{'ITEM'} to escape or strip stuff like that. The placeholders that hippo and LanX refer to solve the problem by telling the DB that your data is only data and not to run it no matter what it looks like.

You really need to do this, especially if you’re dealing with money. It might be some work now, but it will correct a very dangerous problem, and your code will likely end up easier to read (and write) to boot.


– Aaron
Preliminary operational tests were inconclusive. (The damn thing blew up.)

> But at least I now know what needs to be done.

I hope you know that using SQL-placeholders are the easiest, most robust and safest solution.

Cheers Rolf
_{(addicted to the Perl Programming Language :)
Wikisyntax for the Monastery}

what error exactly? what do the server logs or use CGI::Carp qw(fatalsToBrowser); say exactly?