#!/usr/bin/perl -Tw
#
# Jon Hart <warchild@spoofed.org>
#
# 9/16/03
#
# Make unprotected mysql databases your personal ftp server.
# Connects to a specifified mysql server and database, and will
# allow you to 'get' and 'put' files much like you would with ftp
# or scp.
#
# For 'put', it creates a table, chunks up your local file, and inserts
# the chunks as entries into the newly created table;
#
# For 'get', it uses the specified table and retrieves all the 
# "chunks" which are reassembled locally to your file.
#
# For 'rm', it just drops the appropriate table.
#
# For 'ls', it uses the size of the 'data' field times
# the number of entries in the table for a rough approximation
# of size.
#
#
# From anywhere:
# $  mysql -h yourhost 
# Welcome to the MySQL monitor.  Commands end with ; or \g.
# Your MySQL connection id is 159 to server version: 4.0.12
#
# Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
#
# mysql> use test;
# Database changed
# mysql> show tables;
# Empty set (0.03 sec)
#
# From anywhere, using http://spoofed.org/files/mtp:
#
# $ cp /bin/bash .
# $ mtp --host yourhost put bash
#
# On yourhost: 
#
# mysql> show tables;
# +----------------+
# | Tables_in_test |
# +----------------+
# | bash           |
# +----------------+
# 1 row in set (0.03 sec)
#
# mysql> show fields from bash;
# +-------+---------+------+-----+---------+-------+
# | Field | Type    | Null | Key | Default | Extra |
# +-------+---------+------+-----+---------+-------+
# | chunk | int(11) | YES  |     | NULL    |       |
# | data  | blob    | YES  |     | NULL    |       |
# +-------+---------+------+-----+---------+-------+
# 2 rows in set (0.03 sec)
#
#
# Now retrieve the file:
#
# $ rm -f bash
# $ mtp --host yourhost get bash
# $ file bash
# bash: ELF 32-bit LSB executable, Intel 80386, version 1 (SYSV), for
# GNU/Linux 2.2.0, dynamically linked (uses shared libs), stripped
#
# $  ls -l bash /bin/bash
# -rwxr-xr-x    1 root     root       624652 Sep  8 17:56 /bin/bash
# -rw-r--r--    1 warchild users      624652 Sep 16 23:04 bash
# $  md5sum bash /bin/bash
# 775faf931c06c5e93bb06a4dfc891306  bash
# 775faf931c06c5e93bb06a4dfc891306  /bin/bash
#
#
#
# Requires DBD::mysql
#
#
use strict;
use DBI();
use Getopt::Long;
GetOptions(    "verbose"      => \(my $opt_verbose),
               "database=s"   => \(my $opt_database),
               "host=s"       =>\(my $opt_host),
               "pass=s"       => \(my $opt_pass),
               "size=s"       => \(my $opt_size),
               "table=s"      => \(my $opt_table),
               "user=s"       => \(my $opt_user)) or die "Invalid option: $!\n";
$| = 1;
my $action;
my $database;
my $dbh;
my $file;
my $host;
my $pass;
my $size;
my $table;
my $user;
unless (@ARGV) {
   &usage();
}
if (defined($opt_database)) {
   $database = $opt_database;
} else {
   $database = "test";
}
if (defined($opt_host)) {
   $host = $opt_host;
} else {
   $host = "localhost";
}
if (defined($opt_pass)) {
   $pass = $opt_pass;
} else {
   $pass = "";
}
if (defined($opt_size)) {
   if ($opt_size =~ /^\d+$/) {
      $size = $opt_size;
   } else {
      $size = 1024;
   }
} else {
   $size = 1024; 
}
if (defined($opt_table)) {
   $table = $opt_table;
} else {
   $table = "test";
}
if (defined($opt_user)) {
   $user = $opt_user;
} else {
   $user = "";
}
$action = $ARGV[0] || &usage();
if (defined($opt_verbose)) {
   print("Connecting to database $database on $host ... ");
}
eval {$dbh = DBI->connect("DBI:mysql:database=$database;host=$host",$user,$pass, {"RaiseError" => 0, "PrintError" => 0}) };
if ($@) {
   die("Couldn't connect to database $database on host $host: $@\n");
}
if (defined($opt_verbose)) {
   print("connected\n");
}
if ($action eq 'ls') {
   $file = $ARGV[1];
} else {
   $file = $ARGV[1] || &usage();
}
if ($action eq 'get') {
   &get($file, $table);
} elsif ($action eq 'ls') {
   if (defined($file)) {
      &ls($file);
   } else {
      &ls();
   }
} elsif ($action eq 'put') {
   &put($file, $table);
} elsif ($action eq 'rm') {
   &rm($file);
} else {
   print("No such action '$action'\n");
   exit(1);
}
if (defined($opt_verbose)) { print("Disconnecting ... "); }
$dbh->disconnect();
if (defined($opt_verbose)) { print("done\n"); }
sub checktable {
   my $table = shift;
   my $sth;   
   unless ($sth = $dbh->prepare("LISTFIELDS $table")) {
      printf("Error preparing LISTFIELDS query: %s\n", $dbh->errstr);
      return;
   }
   unless($sth->execute()) {
      printf("Error executing LISTFIELDS query: %s\n", $dbh->errstr);
      return;
   }
   my $names = $sth->{'NAME'};
   for (my $rownum = 0; $rownum < $sth->{'NUM_OF_FIELDS'}; $rownum++) {
      if ($$names[$rownum] =~ /^data_(\d+)$/) {
         # looks like this is the field we want
         return $$names[$rownum];
      }
   }
   return;
} 
sub get {
   # given a file and a table in the datebase, attempt to pull
   # all the correct records out of the database (which were hopefully
   # put there by a 'put') and reassemble the file locally
   my $chunk;
   my $colname;
   my $file = shift;
   my $sth;
   my $table = shift;
   unless ($file =~ /^([A-Za-z0-9-_]+)$/) {
      print("$file is an invalid filename\n");
      print("Remeber that the filename must also be a valid table name...\n");
      return;
   }
   $file = $1;
   unless (defined($opt_table)) { $table = $file; }
   open(FILE, ">$file") or die "Couldn't open $file for writing: $!\n";
   binmode(FILE);
   if (defined($opt_verbose)) { print("Getting $file from table $table\n"); }
   $colname = checktable($table);
   unless (defined($colname)) {
      print("Didn't find a matching table.  Are you sure '$table' is where you stored your file?\n");
      close(FILE);
      return;
   }
   $sth = $dbh->prepare("SELECT $colname from $table");
   $sth->execute();
   if (!$sth) {
      printf("Error in pulling data: %s\n", $dbh->errstr);
      close(FILE);
      return;
   }
   while ($chunk = $sth->fetchrow_hashref()) {
      print FILE $chunk->{$colname};
   }
   if (defined($opt_verbose)) { print("Done getting $file from table $table\n"); }
   close(FILE);
}
sub ls {
   # given a number of files >= 0, list them.  The listing is generated
   # by calculating the "chunk" sizes times the number of entries
   my $colname;
   my $file_target = shift;
   my %tables;
   my $sth;
   if (defined($file_target)) {
      $colname = &checktable($file_target);
      unless (defined($colname)) {
         print("No such file in database: $file_target\n");
         return;
      }
      $tables{$file_target} = $colname;
   } else {
      foreach my $listing (map { $_ =~ s/.*\.//; $_ =~ s/`//g; $_ } $dbh->tables()) {
         $colname = &checktable($listing);
         if (defined($colname)) {
            $tables{$listing} = $colname;
         }
      }
   }
   foreach my $table (keys %tables) {
      if (defined($opt_verbose)) { print("Listing for file $table\n"); }
      unless ($sth = $dbh->prepare("SELECT COUNT($tables{$table}) as count from $table")) {
         printf("Error preparing COUNT statement for $table: %s\n", $dbh->errstr);
         return;
      }
      unless ($sth->execute()) {
         printf("Error executing COUNT statement for $table: %s\n", $dbh->errstr);
         return;
      }
      while (my $row = $sth->fetchrow_hashref()) {
         $tables{$table} =~ /(\d+)/;
         my $chunksize = $1;
         printf("$table %s bytes in %s %s-byte chunks\n", $row->{'count'} * $chunksize, $row->{'count'}, $chunksize);
      }
   }   
}
sub put {
   # given a file and maybe a table to put it in, 
   # chunk up the file and store it in the database using
   # user defined chunk sizes.
   my $chunk;
   my $file = shift;
   my $table = shift;
   my $offset = 0;
   unless ($file =~ /^([A-Za-z0-9-_]+)$/) {
      print("$file is an invalid filename\n");
      return;
   }
   unless (defined($opt_table)) { $table = $1; }
   $file = $1;
   if (defined($opt_verbose)) { print("Putting $file into table $table with $size-byte sized chunks\n"); }
   open(FILE, $file) or die "Couldn't open $file for reading: $!\n";
   eval { $dbh->do("CREATE TABLE $table (num INTEGER, data_$size blob)") };
   if ($@) {
      print("Error creating table $table: $@\n");
      close(FILE);
      return;
   }
   while(read(FILE,$chunk,$size)) {
      $chunk = $dbh->quote($chunk);
      my $sth = $dbh->prepare("INSERT INTO $table VALUES ($offset,$chunk)");
      if (!$sth) {
         printf("Error in prepared statement: %s\n", $dbh->errstr);
         close(FILE);
         return;
      }
      if (!$sth->execute()) {
         printf("Error in executing prepared statement: %s\n", $sth->errstr);
         close(FILE);
         return;
      }
      $offset++;
   }
   if (defined($opt_verbose)) { print("Done putting file into table $table\n"); }
   close(FILE);
}
sub rm {
   # remove a file by simply dropping the table in the database
   my $file = shift;
   my $table = shift;
   unless (defined($opt_table)) { $table = $file; }
   if (defined($opt_verbose)) { print("Removing $file from table $table\n"); }
   eval { $dbh->do("DROP TABLE $table") };
   if ($@) {
      print("Had trouble dropping table $table: $@\n");
      close(FILE);
      return;
   }
   if (defined($opt_verbose)) { print("Removed $file from table $table\n"); }
   close(FILE);
}
sub usage {
    print << "EOF";
$0 -- mysql transfer protocol tool
Uses improperly secured databases to send/list/retrieve/remove files
Usage: $0 --file [options] < <get|put|rm> <filename> > | <ls [file]> >
Option            Description                Default
----------------------------------------------------
--database <d>    Database to connect to     test
--host <h>        Host to connect to         localhost
--pass <p>        Use this password          ""
--size <s>        Use this size file chunks  1024
--table <t>       Write to this table        test
--user <u>        Use this username          ""
--verbose         Be verbose
Examples:
$0 put bash # gets bash from the mysql database
$0 ls bash # lists the file
$0 ls # lists all files
$0 get bash # stores bash to localhost
$0 rm bash # removes the "file" bash from the database
$0 --host yourhost --table foobar put test # puts the file 'test' into the table named foobar on yourhost
EOF
exit(1);
}