#!/usr/bin/perl

use strict;
use warnings;
use DBI;

# Database configuration
my $dsn = "DBI:mysql:database=badstoredb;host=localhost;port=3306";
my $username = "root";
my $password = "secret";
my @tables = qw(acctdb itemdb orderdb userdb);

# Output file
my $output_file = "dump.sql";

# Connect to the database
my $dbh = DBI->connect($dsn, $username, $password, { RaiseError => 1, PrintError => 0 }) 
    or die "Could not connect to database: $DBI::errstr";

# Open the output file
open(my $fh, '>', $output_file) or die "Could not open file '$output_file' $!";

# Table to dump
for my $table (@tables) {

# Fetch table structure
my $sth = $dbh->prepare("SHOW CREATE TABLE $table");
$sth->execute();
my ($table_name, $create_table) = $sth->fetchrow_array();
print $fh "$create_table;\n\n";

# Fetch table data
$sth = $dbh->prepare("SELECT * FROM $table");
$sth->execute();
my $columns = $sth->{NAME_lc};
my $num_columns = $sth->{NUM_OF_FIELDS};

# Write INSERT statements
while (my @row = $sth->fetchrow_array()) {
    my @values;
    for my $value (@row) {
        if (defined $value) {
            $value =~ s/\\/\\\\/g;
            $value =~ s/'/\\'/g;
            push @values, "'$value'";
        } else {
            push @values, "NULL";
        }
    }
    print $fh "INSERT INTO $table (", join(", ", @$columns), ") VALUES (", join(", ", @values), ");\n";
}
}

# Close the file and the database connection
close($fh);
$dbh->disconnect();

print "Dump completed successfully.\n";