"Fossies" - the Fresh Open Source Software Archive

Member "webmysql-2.7/cgi-bin/webmysql-2.7/webmysql.cgi" (12 Feb 2008, 39784 Bytes) of package /linux/www/old/webmysql-2.7.tar.gz:


The requested HTML page contains a <FORM> tag that is unusable on "Fossies" in "automatic" (rendered) mode so that page is shown as HTML source code syntax highlighting (style: standard) with prefixed line numbers and code folding option. Alternatively you can here view or download the uninterpreted source code file.

A hint: This file contains one or more very long lines, so maybe it is better readable using the pure text view mode that shows the contents as wrapped lines within the browser window.


    1 #!/usr/bin/perl -w
    2 #web interface to a mysql server
    3 #mt 21/09/2003 2.3  fixed bug when deleting the current database
    4 #mt 28/09/2003 2.3  fixed ie logon by removing logon confirmation page
    5 #mt 29/09/2003 2.3  fixed mysqldump import bug
    6 #                           wipe database now supported
    7 #mt 16/11/2003 2.4  import file multiline single query bug fixed
    8 #                           empty table now supported
    9 #mt 17/11/2003 2.4  fixed msdos import file bug
   10 #mt 29/11/2003 2.5  Updated processfile sub to cope with ";" characters in sql commands
   11 #mt 14/01/2004  2.6 Added mysqldump export support
   12 #                           improved processFile sub to do only one db connect, much faster now
   13 #mt 16/03/2005  2.7 finished insert code
   14 #                           added explain to select queries
   15 #                           added table status info
   16 use strict;
   17 use CGI;
   18 use DBI;
   19 use DBD::mysql;
   20 use lib ".";
   21 use DTWebMySQL::Main;
   22 use DTWebMySQL::Key;
   23 use DTWebMySQL::General;
   24 use DTWebMySQL::Sql;
   25 use constant;   #for perl2exe
   26 $| = 1; #disable output buffering, helps with CGIWrap
   27 &expireKeys;    #remove old keys from server
   28 if(&getData()){ #get the data from the last page's form
   29     if($form{'key'}){   #got a key do normal actions
   30         if(&readKey($form{'key'})){ #read the server side cookie for state
   31             $form{'menu'} = &parseFragmentToString("menu"); #load the top menu
   32             if($form{'action'} eq "mainmenu"){} #just display a template
   33             elsif($form{'action'} eq "logout"){&deleteKey($form{'key'});}   #remove the server side cookie
   34             elsif($form{'action'} eq "query"){  #pick what type of query to run
   35                 &updateKey($form{'key'});
   36             }
   37             elsif($form{'action'} eq "selectchoosetable"){  #pick what table to run the query type on
   38                 $form{'tablelist'} = "";
   39                 if(my @tables = &getTables($form{'host'}, $form{'user'}, $form{'password'}, $form{'database'})){
   40                     for(my $tCount = 0; $tCount <= $#tables; $tCount++){$form{'tablelist'} .= "<tr><th><input type=\"checkbox\" name=\"table$tCount\" value=\"$tables[$tCount]\"></th><td>$tables[$tCount]</td></tr>\n";}   #convert to html format
   41                     &updateKey($form{'key'});
   42                 }
   43             }
   44             elsif($form{'action'} eq "selectchoosefields"){ #pick what fields to use in the query
   45                 my @tablesTemp;
   46                 foreach my $name (keys %form){
   47                     if($name =~ m/^table\d+$/){push(@tablesTemp, $form{$name});}
   48                 }
   49                 if($#tablesTemp > -1){  #one or more tables have been selected
   50                     $form{'tables'} = join(", ", @tablesTemp);  #for the server side cookie
   51                     if(my @fields = &getFields($form{'host'}, $form{'user'}, $form{'password'}, $form{'database'}, $form{'tables'})){
   52                         $form{'fieldlist'} = "";
   53                         for(my $count = 0; $count <= $#fields; $count++){$form{'fieldlist'} .= "<tr><th><input type=\"checkbox\" name=\"field" . ($count + 1) . "\" value=\"$fields[$count]\"></th><td>$fields[$count]</td></tr>\n";}   #convert to html format
   54                         &updateKey($form{'key'});
   55                     }
   56                 }
   57                 else{$error = "You did not select any tables to query";}
   58             }
   59             elsif($form{'action'} eq "selectchoosecriteria"){   #pick the criteria for the query
   60                 my @tmpFields;
   61                 foreach my $name (keys %form){
   62                     if($name =~ m/^field\d+$/){push(@tmpFields, $form{$name});}
   63                 }
   64                 $form{'fields'} = join(", ", @tmpFields);   #for the server side cookie
   65                 if(my @fields = &getFields($form{'host'}, $form{'user'}, $form{'password'}, $form{'database'}, $form{'tables'})){
   66                     if($form{'tables'} =~ m/, /){   #more than one table selected, show the join options
   67                         my @tables = split(/, /, $form{'tables'});
   68                         $form{'joinlist'} = "<p>Please select how you want to join the tables to $tables[0]</p>\n";
   69                         $form{'joinlist'} .= "<table border=\"1\" align=\"center\" bgcolor=\"#8899DD\">\n";
   70                         for(my $tCount = 1; $tCount <= $#tables; $tCount++){
   71                             $form{'joinlist'} .= "<tr>\n";
   72                             $form{'joinlist'} .= "<td>left join $tables[$tCount] on</td>\n";
   73                             $form{'joinlist'} .= "<td>\n";
   74                             $form{'joinlist'} .= "<select name=\"joinfield1_$tables[$tCount]\">\n";
   75                             foreach(@fields){
   76                                 if($_ !~ m/\*$/){   #ignore these fields
   77                                     $form{'joinlist'} .= "<option value=\"$_\">$_</option>";
   78                                 }
   79                             }
   80                             $form{'joinlist'} .= "</select>\n";
   81                             $form{'joinlist'} .= "</td>\n";
   82                             $form{'joinlist'} .= "<td>=</td>\n";
   83                             $form{'joinlist'} .= "<td>\n";
   84                             $form{'joinlist'} .= "<select name=\"joinfield2_$tables[$tCount]\">\n";
   85                             foreach(@fields){
   86                                 if($_ !~ m/\*$/){   #ignore these fields
   87                                     $form{'joinlist'} .= "<option value=\"$_\">$_</option>";
   88                                 }
   89                             }
   90                             $form{'joinlist'} .= "</select>\n";
   91                             $form{'joinlist'} .= "</td>\n";
   92                             $form{'joinlist'} .= "</tr>\n";
   93                         }
   94                         $form{'joinlist'} .= "</table>\n";
   95                     }
   96                     else{$form{'joinlist'} = "";}   #join not used for just one table
   97                     $form{'criterialist'} = "";
   98                     for(my $count = 0; $count <= 5; $count++){
   99                         $form{'criterialist'} .= "<tr>";
  100                         $form{'criterialist'} .= "<td><select name=\"critname$count\"><option value=\"\"></option>";
  101                         foreach(@fields){
  102                             if($_ !~ m/\*$/){   #ignore these fields
  103                                 $form{'criterialist'} .= "<option value=\"$_\">$_</option>";
  104                             }
  105                         }
  106                         $form{'criterialist'} .= "</select></td>";
  107                         $form{'criterialist'} .= "<td><select name=\"crithow$count\">";
  108                         foreach("=", ">=", "<=", ">", "<", "!=", "LIKE", "REGEXP"){$form{'criterialist'} .= "<option value=\"$_\">$_</option>";}
  109                         $form{'criterialist'} .= "</select></td>";
  110                         $form{'criterialist'} .= "<td><input type=\"text\" name=\"crit$count\"></td>";
  111                         if($count < 5){$form{'criterialist'} .= "<td><select name=\"critappend$count\"><option value=\"AND\">AND</option><option value=\"OR\">OR</option></select></td>";}
  112                         else{$form{'criterialist'} .= "<td>&nbsp;</td>";}
  113                         $form{'criterialist'} .= "</tr>\n";
  114                     }
  115                     $form{'orderbylist'} = "";
  116                     foreach(@fields){
  117                         if($_ !~ m/\*$/){   #ignore these fields
  118                             $form{'orderbylist'} .= "<option value=\"$_\">$_</option>\n";
  119                         }
  120                     }
  121                     &updateKey($form{'key'});
  122                 }
  123             }
  124             elsif($form{'action'} eq "runquery"){   #run the query
  125                 $form{'sql'} = &composeSelect();
  126                 $form{'explainrecords'} = &explainQuery($form{'host'}, $form{'user'}, $form{'password'}, $form{'database'}, $form{'sql'});
  127                 $form{'queryrecords'} = &runQuery($form{'host'}, $form{'user'}, $form{'password'}, $form{'database'}, $form{'sql'});
  128             }
  129             elsif($form{'action'} eq "managetables"){   #show table list
  130                 $form{'tablelist'} = "";
  131                 if(my @tables = &getTables($form{'host'}, $form{'user'}, $form{'password'}, $form{'database'})){
  132                     foreach(@tables){   #convert to html format
  133                         $form{'tablelist'} .= "<tr>\n";
  134                         $form{'tablelist'} .= "<td>$_</td>\n";
  135                         $form{'tablelist'} .= "<th>\n";
  136                         $form{'tablelist'} .= "<form action=\"$ENV{'SCRIPT_NAME'}\" method=\"POST\">\n";
  137                         $form{'tablelist'} .= "<input type=\"hidden\" name=\"key\" value=\"$form{'key'}\">\n";
  138                         $form{'tablelist'} .= "<input type=\"hidden\" name=\"tables\" value=\"$_\">\n";
  139                         $form{'tablelist'} .= "<input type=\"hidden\" name=\"action\" value=\"describe\">\n";
  140                         $form{'tablelist'} .= "<input type=\"submit\" value=\"Info\">\n";
  141                         $form{'tablelist'} .= "</form>\n";
  142                         $form{'tablelist'} .= "</th>\n";
  143                         $form{'tablelist'} .= "<th>\n";
  144                         $form{'tablelist'} .= "<form action=\"$ENV{'SCRIPT_NAME'}\" method=\"POST\">\n";
  145                         $form{'tablelist'} .= "<input type=\"hidden\" name=\"key\" value=\"$form{'key'}\">\n";
  146                         $form{'tablelist'} .= "<input type=\"hidden\" name=\"tables\" value=\"$_\">\n";
  147                         $form{'tablelist'} .= "<input type=\"hidden\" name=\"action\" value=\"emptytable\">\n";
  148                         $form{'tablelist'} .= "<input type=\"submit\" value=\"Empty\">\n";
  149                         $form{'tablelist'} .= "</form>\n";
  150                         $form{'tablelist'} .= "</th>\n";
  151                         $form{'tablelist'} .= "<th>\n";
  152                         $form{'tablelist'} .= "<form action=\"$ENV{'SCRIPT_NAME'}\" method=\"POST\">\n";
  153                         $form{'tablelist'} .= "<input type=\"hidden\" name=\"key\" value=\"$form{'key'}\">\n";
  154                         $form{'tablelist'} .= "<input type=\"hidden\" name=\"tables\" value=\"$_\">\n";
  155                         $form{'tablelist'} .= "<input type=\"hidden\" name=\"action\" value=\"droptable\">\n";
  156                         $form{'tablelist'} .= "<input type=\"submit\" value=\"Drop\">\n";
  157                         $form{'tablelist'} .= "</form>\n";
  158                         $form{'tablelist'} .= "</th>\n";
  159                         $form{'tablelist'} .= "</tr>\n";
  160                     }
  161                     delete $form{'tables'};
  162                     &updateKey($form{'key'});
  163                 }
  164             }
  165             elsif($form{'action'} eq "describe"){   #display table list
  166                 if($form{'tables'} =~ m/^(\w+)$/){  #safety check on table name
  167                     $form{'queryrecords'} = &runQuery($form{'host'}, $form{'user'}, $form{'password'}, $form{'database'}, "DESCRIBE $1;");
  168                     $form{'statusrecords'} = &runQueryVert($form{'host'}, $form{'user'}, $form{'password'}, $form{'database'}, "SHOW TABLE STATUS LIKE '$1';");
  169                 }
  170                 else{$error = "Table name contains invalid characters";}
  171             }
  172             elsif($form{'action'} eq "serverinfo"){ #shows processlist
  173                 $form{'queryrecords'} = &runQuery($form{'host'}, $form{'user'}, $form{'password'}, $form{'database'}, "SHOW PROCESSLIST;");
  174             }
  175             elsif($form{'action'} eq "droptable"){
  176                 if($form{'tables'} =~ m/^(\w+)$/){  #safety check on table name
  177                     $form{'rows'} = &getTableRows($form{'host'}, $form{'user'}, $form{'password'}, $form{'database'}, $form{'tables'});
  178                     &updateKey($form{'key'});
  179                 }
  180                 else{$error = "Table name contains invalid characters";}
  181             }
  182             elsif($form{'action'} eq "droptableconfirm"){
  183                 if($form{'answer'} eq "yes"){   #user confirmed drop
  184                     if($form{'tables'} =~ m/^(\w+)$/){  #safety check on table name
  185                         $form{'queryrecords'} = &runNonSelect($form{'host'}, $form{'user'}, $form{'password'}, $form{'database'}, "DROP TABLE $1;");
  186                     }
  187                     else{$error = "Table name contains invalid characters";}
  188                 }
  189                 else{$error = "You did not confirm that you wanted the table dropped";}
  190             }
  191             elsif($form{'action'} eq "emptytable"){
  192                 if($form{'tables'} =~ m/^(\w+)$/){  #safety check on table name
  193                     $form{'rows'} = &getTableRows($form{'host'}, $form{'user'}, $form{'password'}, $form{'database'}, $form{'tables'});
  194                     &updateKey($form{'key'});
  195                 }
  196                 else{$error = "Table name contains invalid characters";}
  197             }
  198             elsif($form{'action'} eq "emptytableconfirm"){
  199                 if($form{'answer'} eq "yes"){   #user confirmed drop
  200                     if($form{'tables'} =~ m/^(\w+)$/){  #safety check on table name
  201                         $form{'queryrecords'} = &runNonSelect($form{'host'}, $form{'user'}, $form{'password'}, $form{'database'}, "DELETE FROM $1;");
  202                     }
  203                     else{$error = "Table name contains invalid characters";}
  204                 }
  205                 else{$error = "You did not confirm that you wanted the table dropped";}
  206             }
  207             elsif($form{'action'} eq "createtable"){    #chose a new table name
  208                 delete $form{'tables'};
  209                 &updateKey($form{'key'});
  210             }
  211             elsif($form{'action'} eq "createtablefields"){  #show table creation page
  212                 if($form{'tables'} ne ""){
  213                     if(length($form{'tables'}) <= 64){
  214                         if($form{'tables'} =~ m/^\w+$/){
  215                             my @tables = &getTables($form{'host'}, $form{'user'}, $form{'password'}, $form{'database'});
  216                             if($#tables > -1){  # the current database already has some tables in it
  217                                 my $exists = 0;
  218                                 foreach(@tables){
  219                                     if($_ eq $form{'tables'}){  #found this table name already
  220                                         $exists = 1;
  221                                         last;
  222                                     }
  223                                 }
  224                                 if(!$exists){   #this name name does not exist already
  225                                     $form{'currentfields'} = &getCreationFields();
  226                                     $form{'removefields'} = "";
  227                                     if($form{'creationfnames'}){
  228                                         my @fields = split(//, $form{'creationfnames'});
  229                                         foreach(@fields){$form{'removefields'} .= "<option value=\"$_\">$_</option>\n";}
  230                                     }
  231                                     &updateKey($form{'key'});
  232                                 }
  233                                 else{$error = "The table name you specified already exists in the current database";}
  234                             }
  235                             elsif(!$error){ #no current tables in database
  236                                 delete $form{'creationfnames'};
  237                                 delete $form{'creationftypes'};
  238                                 delete $form{'creationfsizes'};
  239                                 delete $form{'creationfnull'};
  240                                 $form{'currentfields'} = "";
  241                                 $form{'removefields'} = "";
  242                                 &updateKey($form{'key'});
  243                             }
  244                         }
  245                         else{$error = "The table name you specified contains invalid characters";}
  246                     }
  247                     else{$error = "The table name you specified is too long";}
  248                 }
  249                 else{$error = "You did not enter a name for the new table";}
  250             }
  251             elsif($form{'action'} eq "createtableaddfield"){    #add a new field to the table
  252                 if($form{'fname'} ne ""){   #the user has typed a field name in
  253                     if($form{'fsize'} eq ""){$form{'fsize'} = 0;}
  254                     my $found = 0;
  255                     if($form{'creationfnames'}){    #we have some fields already
  256                         foreach(split(//, $form{'creationfnames'})){   #search the current list of field names to be
  257                             if($_ eq $form{'fname'}){
  258                                 $found = 1;
  259                                 last;
  260                             }
  261                         }
  262                     }
  263                     if(!$found){
  264                         if(defined($form{'fnull'}) && $form{'fnull'} eq "on"){$form{'fnull'} = "Y";}
  265                         else{$form{'fnull'} = "N";}
  266                         if(!exists($form{'creationfnames'})){
  267                             $form{'creationfnames'} = $form{'fname'};
  268                             $form{'creationftypes'} = $form{'ftype'};
  269                             $form{'creationfsizes'} = $form{'fsize'};
  270                             $form{'creationfnulls'} = $form{'fnull'};
  271                         }
  272                         else{
  273                             $form{'creationfnames'} .= "$form{'fname'}";
  274                             $form{'creationftypes'} .= "$form{'ftype'}";
  275                             $form{'creationfsizes'} .= "$form{'fsize'}";
  276                             $form{'creationfnulls'} .= "$form{'fnull'}";
  277                         }   #append
  278                         &updateKey($form{'key'});
  279                         $form{'currentfields'} = &getCreationFields();
  280                         my @fields = split(//, $form{'creationfnames'});
  281                         $form{'removefields'} = "";
  282                         foreach(@fields){$form{'removefields'} .= "<option value=\"$_\">$_</option>\n";}
  283                         $form{'action'} = "createtablefields";  #send user back to the table creation page
  284                     }
  285                     else{$error = "A field with the name specified already exists in this table";}
  286                 }
  287                 else{$error = "You did not specify a field name";}
  288             }
  289             elsif($form{'action'} eq "createtablenow"){ #create the table now
  290                 if($form{'creationfnames'}){
  291                     my $sql = "CREATE TABLE $form{'tables'} (";
  292                     my @names = split(//, $form{'creationfnames'});
  293                     my @types = split(//, $form{'creationftypes'});
  294                     my @sizes = split(//, $form{'creationfsizes'});
  295                     my @nulls = split(//, $form{'creationfnulls'});
  296                     for(my $count = 0; $count <= $#names; $count++){
  297                         $sql .= "$names[$count] $types[$count]";
  298                         if($sizes[$count] != 0){$sql .= "($sizes[$count])";}    #include size for this field
  299                         if($nulls[$count] eq "N"){$sql .= " NOT NULL";} #this field is not null
  300                         if($count < $#names){$sql .= ", ";}
  301                     }
  302                     $sql .= ");";
  303                     #print STDERR "$sql\n";
  304                     $form{'queryrecords'} = &runNonSelect($form{'host'}, $form{'user'}, $form{'password'}, $form{'database'}, $sql);
  305                 }
  306                 else{$error = "This table has no fields yet";}
  307             }
  308             elsif($form{'action'} eq "createtableremovefield"){
  309                 if($form{'fname'} ne ""){
  310                     my @names = split(//, $form{'creationfnames'});
  311                     my @types = split(//, $form{'creationftypes'});
  312                     my @sizes = split(//, $form{'creationfsizes'});
  313                     my @nulls = split(//, $form{'creationfnulls'});
  314                     $form{'creationfnames'} = "";
  315                     $form{'creationftypes'} = "";
  316                     $form{'creationfsizes'} = "";
  317                     for(my $count = 0; $count <= $#names; $count++){
  318                         if($names[$count] ne $form{'fname'}){
  319                             if($form{'creationfnames'} eq ""){
  320                                 $form{'creationfnames'} .= $names[$count];
  321                                 $form{'creationftypes'} .= $types[$count];
  322                                 $form{'creationfsizes'} .= $sizes[$count];
  323                                 $form{'creationfnulls'} .= $nulls[$count];
  324                             }
  325                             else{
  326                                 $form{'creationfnames'} .= "$names[$count]";
  327                                 $form{'creationftypes'} .= "$types[$count]";
  328                                 $form{'creationfsizes'} .= "$sizes[$count]";
  329                                 $form{'creationfnulls'} .= "$nulls[$count]";
  330                             }
  331                         }
  332                     }
  333                     if($form{'creationfnames'} eq ""){  #remove empty hash elements
  334                         delete $form{'creationfnames'};
  335                         delete $form{'creationftypes'};
  336                         delete $form{'creationfsizes'};
  337                         delete $form{'creationfnulls'};
  338                     }
  339                     &updateKey($form{'key'});
  340                     $form{'currentfields'} = &getCreationFields();
  341                     $form{'removefields'} = "";
  342                     if($form{'creationfnames'}){    #if we have some fields already
  343                         @names = split(//, $form{'creationfnames'});   #get the new list of names
  344                         foreach(@names){$form{'removefields'} .= "<option value=\"$_\">$_</option>\n";}
  345                     }
  346                     $form{'action'} = "createtablefields";  #send user back to the table creation page
  347                 }
  348                 else{$error = "You did not specify a field name to remove";}
  349             }
  350             elsif($form{'action'} eq "managedatabases"){
  351                 if(my @dbs = &getDatabases($form{'host'}, $form{'user'}, $form{'password'}, $form{'database'})){
  352                     $form{'databaselist'} = "";
  353                     foreach(@dbs){  #convert to html format
  354                         $form{'databaselist'} .= "<tr>\n";
  355                         $form{'databaselist'} .= "<td>$_</td>\n";
  356                         $form{'databaselist'} .= "<th>";
  357                         $form{'databaselist'} .= "<form action=\"$ENV{'SCRIPT_NAME'}\" method=\"POST\">";
  358                         $form{'databaselist'} .= "<input type=\"hidden\" name=\"key\" value=\"$form{'key'}\">";
  359                         $form{'databaselist'} .= "<input type=\"hidden\" name=\"db\" value=\"$_\">";
  360                         $form{'databaselist'} .= "<input type=\"hidden\" name=\"action\" value=\"usedatabase\">";
  361                         $form{'databaselist'} .= "<input type=\"submit\" value=\"Use\">";
  362                         $form{'databaselist'} .= "</form>";
  363                         $form{'databaselist'} .= "</th>\n";
  364                         if($_ eq "mysql"){$form{'databaselist'} .= "<th>&nbsp;</th><th>&nbsp;</th>\n";} #cant delete this table
  365                         else{
  366                             $form{'databaselist'} .= "<th>";
  367                             $form{'databaselist'} .= "<form action=\"$ENV{'SCRIPT_NAME'}\" method=\"POST\">";
  368                             $form{'databaselist'} .= "<input type=\"hidden\" name=\"key\" value=\"$form{'key'}\">";
  369                             $form{'databaselist'} .= "<input type=\"hidden\" name=\"db\" value=\"$_\">";
  370                             $form{'databaselist'} .= "<input type=\"hidden\" name=\"action\" value=\"dropdatabase\">";
  371                             $form{'databaselist'} .= "<input type=\"submit\" value=\"Drop\">";
  372                             $form{'databaselist'} .= "</form>";
  373                             $form{'databaselist'} .= "</th>";
  374                             $form{'databaselist'} .= "<th>";
  375                             $form{'databaselist'} .= "<form action=\"$ENV{'SCRIPT_NAME'}\" method=\"POST\">";
  376                             $form{'databaselist'} .= "<input type=\"hidden\" name=\"key\" value=\"$form{'key'}\">";
  377                             $form{'databaselist'} .= "<input type=\"hidden\" name=\"db\" value=\"$_\">";
  378                             $form{'databaselist'} .= "<input type=\"hidden\" name=\"action\" value=\"wipedatabase\">";
  379                             $form{'databaselist'} .= "<input type=\"submit\" value=\"Empty\">";
  380                             $form{'databaselist'} .= "</form>";
  381                             $form{'databaselist'} .= "</th>\n";
  382                         }
  383                         $form{'databaselist'} .= "</tr>\n";
  384                     }
  385                     delete $form{'db'};
  386                     &updateKey($form{'key'});
  387                 }
  388             }
  389             elsif($form{'action'} eq "dropdatabase"){
  390                 if($form{'db'} =~ m/^(\w+)$/){  #safety check on table name
  391                     $form{'numtables'} = &getTables($form{'host'}, $form{'user'}, $form{'password'}, $form{'db'});
  392                     &updateKey($form{'key'});
  393                 }
  394                 else{$error = "Database name contains invalid characters";}
  395             }
  396             elsif($form{'action'} eq "dropdatabaseconfirm"){
  397                 if($form{'answer'} eq "yes"){   #user confirmed drop
  398                     if($form{'db'} =~ m/^(\w+)$/){  #safety check on table name
  399                         $form{'queryrecords'} = &runNonSelect($form{'host'}, $form{'user'}, $form{'password'}, $form{'database'}, "DROP DATABASE $1;");
  400                         if($form{'queryrecords'}){  #drop database worked
  401                             if($form{'db'} eq $form{'database'}){   #dropped the current database
  402                                 delete $form{'database'};   #stop using the now deleted database
  403                                 &updateKey($form{'key'});   #update the session                             
  404                             }
  405                         }
  406                     }
  407                     else{$error = "Database name contains invalid characters";}
  408                 }
  409                 else{$error = "You did not confirm that you wanted the database dropped";}
  410             }
  411             elsif($form{'action'} eq "wipedatabase"){
  412                 if($form{'db'} =~ m/^(\w+)$/){  #safety check on table name
  413                     $form{'numtables'} = &getTables($form{'host'}, $form{'user'}, $form{'password'}, $form{'db'});
  414                     &updateKey($form{'key'});
  415                 }
  416                 else{$error = "Database name contains invalid characters";}
  417             }
  418             elsif($form{'action'} eq "wipedatabaseconfirm"){
  419                 if($form{'answer'} eq "yes"){   #user confirmed drop
  420                     if($form{'db'} =~ m/^(\w+)$/){  #safety check on table name
  421                         my @tables = &getTables($form{'host'}, $form{'user'}, $form{'password'}, $form{'db'});  #find the tables for this database
  422                         foreach(@tables){   #delete every table
  423                             my $result = &runNonSelect($form{'host'}, $form{'user'}, $form{'password'}, $form{'db'}, "DROP TABLE $_;");
  424                             if(!$result){last;} #if we get an error stop now
  425                         }
  426                     }
  427                     else{$error = "Database name contains invalid characters";}
  428                 }
  429                 else{$error = "You did not confirm that you wanted the database dropped";}
  430             }
  431             elsif($form{'action'} eq "createdatabase"){ #chose a new database name
  432                 delete $form{'db'};
  433                 &updateKey($form{'key'});
  434             }
  435             elsif($form{'action'} eq "createdatabasenow"){
  436                 if($form{'db'} ne ""){
  437                     if(length($form{'db'}) <= 64){
  438                         if($form{'db'} =~ m/^\w+$/){
  439                             if(my @dbs = &getDatabases($form{'host'}, $form{'user'}, $form{'password'}, $form{'database'})){
  440                                 my $exists = 0;
  441                                 foreach(@dbs){
  442                                     if($_ eq $form{'db'}){  #found this database name already
  443                                         $exists = 1;
  444                                         last;
  445                                     }
  446                                 }
  447                                 if(!$exists){   #this name name does not exist already
  448                                     &runNonSelect($form{'host'}, $form{'user'}, $form{'password'}, $form{'database'}, "CREATE DATABASE $form{'db'};");
  449                                 }
  450                                 else{$error = "The database name you specified already exists";}
  451                             }
  452                         }
  453                         else{$error = "The database name you specified contains invalid characters";}
  454                     }
  455                     else{$error = "The database name you specified is too long";}
  456                 }
  457                 else{$error = "You did not enter a name for the new database";}
  458             }
  459             elsif($form{'action'} eq "usedatabase"){
  460                 if($form{'db'} ne ""){
  461                     if(length($form{'db'}) <= 64){
  462                         if($form{'db'} =~ m/^\w+$/){
  463                             if(my @dbs = &getDatabases($form{'host'}, $form{'user'}, $form{'password'}, $form{'database'})){
  464                                 my $exists = 0;
  465                                 foreach(@dbs){
  466                                     if($_ eq $form{'db'}){  #found this database name already
  467                                         $exists = 1;
  468                                         last;
  469                                     }
  470                                 }
  471                                 if($exists){    #this name name does not exist already
  472                                     $form{'database'} = $form{'db'};    #save the new database
  473                                     delete $form{'db'};
  474                                     &updateKey($form{'key'});
  475                                 }
  476                                 else{$error = "The database name you specified already exists";}
  477                             }
  478                         }
  479                         else{$error = "The database name you specified contains invalid characters";}
  480                     }
  481                     else{$error = "The database name you specified is too long";}
  482                 }
  483                 else{$error = "You did not enter a name for the new database";}
  484             }
  485             elsif($form{'action'} eq "importdumpform"){}    #just display template
  486             elsif($form{'action'} eq "importdump"){
  487                 my @parts = split(/\\/, $form{'dumpfile'}); #ms browser fix
  488                 my $file = $parts[$#parts];
  489                 if($file){
  490                     if($file =~ m/^(\w|\.|\-|\_)+$/){   #make sure filename is not silly
  491                         if(&uploadFile($file)){
  492                             $form{'commands'} = &processFile($file);    #execute the sql statements and count them
  493                             unlink("dump_uploads/$file");
  494                         }
  495                     }
  496                     else{$error = "Dumpfile name contains invalid characters";}
  497                 }
  498                 else{$error = "You did not select a dumpfile to import";}
  499             }
  500             elsif($form{'action'} eq "insertchoosetable"){  #pick what table to run the query type on
  501                 $form{'tablelist'} = "";
  502                 if(my @tables = &getTables($form{'host'}, $form{'user'}, $form{'password'}, $form{'database'})){
  503                     for(my $tCount = 0; $tCount <= $#tables; $tCount++){$form{'tablelist'} .= "<option value=\"$tables[$tCount]\">$tables[$tCount]</option>\n";}    #convert to html format
  504                     delete($form{'tables'});    #wipe this before the user makes a talbe choice
  505                     foreach my $key (keys %form){   #delete any pending insert records from a unfinished insert
  506                         if($key =~ m/^insertdata\d+$/){delete $form{$key};}
  507                     }
  508                     &updateKey($form{'key'});
  509                 }
  510             }
  511             elsif($form{'action'} eq "insertform"){ #display insert form
  512                 if($form{'tables'} =~ m/^(\w+)$/){  #safety check on table name
  513                     my $table = $1;
  514                     if(my @fields = &getFieldsShort($form{'host'}, $form{'user'}, $form{'password'}, $form{'database'}, $form{'tables'})){
  515                         $form{'input'} = &createInsertForm($form{'host'}, $form{'user'}, $form{'password'}, $form{'database'}, $table);
  516                         $form{'fields'} = "";
  517                         foreach(@fields){$form{'fields'} .= "<th>$_</th>";} #create the field name headings
  518                         &updateKey($form{'key'});
  519                     }
  520                 }   
  521                 else{$error = "Table name contains invalid characters";}
  522             }
  523             elsif($form{'action'} eq "insert"){ #add the record to the list of pending records
  524                 if($form{'tables'} =~ m/^(\w+)$/){  #safety check on table name
  525                     my $table = $1;
  526                     if(my @fields = &getFieldsShort($form{'host'}, $form{'user'}, $form{'password'}, $form{'database'}, $form{'tables'})){
  527                         my $sql = "INSERT INTO $table (";   #starting sql
  528                         $sql .= join(",", @fields) . ") VALUES(";   #add the table fields   
  529                         my $dbh = DBI -> connect("DBI:mysql:database=$form{'database'};host=$form{'host'}", $form{'user'}, $form{'password'});
  530                         if($dbh){
  531                             for(my $i = 0; $i <= $#fields; $i++){
  532                                 if($form{"insert_" . $i} eq ""){$sql .= "'',";} #now value entered
  533                                 else{$sql .= $dbh -> quote($form{"insert_" . $i}) . ",";}
  534                             }
  535                             $dbh -> disconnect();
  536                         }
  537                         else{$error = "Cant connect to MySQL server: " . $DBI::errstr;}
  538                         $sql = substr($sql, 0, (length($sql) - 1)); #get rid of the last comma
  539                         $sql .= ");";   #ending sql
  540                         $form{'affected'} = &runNonSelect($form{'host'}, $form{'user'}, $form{'password'}, $form{'database'}, $sql);    #insert the data
  541                         $form{'code'} = &displaySQL($sql);
  542                         $form{'action'} = "insertcomplete"; #got back to the same page we came from
  543                     }
  544                 }
  545             }
  546             elsif($form{'action'} eq "exportdump"){ #send the dump file to the browser
  547                 &createDumpFile();  #make the export file
  548                 if(!$error){    #display an error message instead of the dump file
  549                     if(open(EXPORT, "<dump_downloads/$form{'key'}.sql")){   #able to read the export file
  550                         print "Content type: application/oct-stream\n\n";   #tell the browser it's a binary file
  551                         while(<EXPORT>){print;} #send the whole file to the browser
  552                         close(EXPORT);
  553                     }
  554                     else{$error = "Unable to read export file: $!";}
  555                 }
  556                 unlink("dump_downloads/$form{'key'}.sql");  #remove temp file
  557             }
  558             else{$error = "Invalid action: $form{'action'}";}   #a strange action has been found
  559         }
  560         else{$form{'action'} = "login";}    #send to the starting page if no key has been given, or not logging in
  561     }
  562     else{   #must be a starting page or a login
  563         if($form{'action'} && $form{'action'} eq "connect"){    #a login
  564             if(&testConnect($form{'host'}, $form{'user'}, $form{'password'}, $form{'database'})){   #mysql login detail are correct
  565                 $form{'key'} = &createKey();    #created new server side cookie file
  566                 &updateKey($form{'key'});
  567                 $form{'action'} = "mainmenu";   #display the main menu
  568                 $form{'menu'} = &parseFragmentToString("menu"); #load the top menu
  569             }
  570         }
  571         else{$form{'action'} = "login";}    #display the starting page
  572     }
  573 }
  574 if($form{'action'} ne "exportdump" || $error){  #only show a html template if we are not outputting text etc.
  575     print "Content-type: text/html\n\n";
  576     &parsePage("header", 1);
  577     &parsePage($form{'action'}, 0);
  578     &parsePage("footer", 1);
  579 }
  580 exit(0);
  581 ##################################################################################################################
  582 sub composeSelect{  #generates the sql code for a select query
  583     my $code = "SELECT ";
  584     if($form{'distinct'}){$code .= "DISTINCT ";}    #distinct results only
  585     $code .= "$form{'fields'}"; #add the fields to show
  586     if($form{'groupby'} ne "" && $form{'groupfunc'} ne "" && $form{'funcfield'} ne ""){ #user is grouping with a group function
  587         $code .= ", $form{'groupfunc'}($form{'funcfield'})";
  588     }
  589     $code .= " FROM ";
  590     my @tables = split(/, /, $form{'tables'});
  591     $code .= $tables[0];
  592     if($form{'tables'} =~ m/, /){
  593         for(my $tCount = 1; $tCount <= $#tables; $tCount++){
  594             $code .= " LEFT JOIN $tables[$tCount] ON $form{'joinfield1_' . $tables[$tCount]} = $form{'joinfield2_' . $tables[$tCount]}";
  595         }
  596     }
  597     my $criteria = "";
  598     my $count = 0;
  599     while($form{'critname' . $count} ne ""){
  600         $criteria .= $form{'critname' . $count} . " " . $form{'crithow' . $count} . " '" . $form{'crit' . $count} . "'";
  601         if(exists($form{'critname' . ($count + 1)}) && $form{'critname' . ($count + 1)}){$criteria .= " " . $form{'critappend' . $count} . " ";}
  602         $count++;
  603     }
  604     if($criteria ne ""){$code .= " WHERE $criteria";}
  605     if($form{'groupby'} ne ""){$code .= " GROUP BY $form{'groupby'}";}  #add grouping
  606     if($form{'orderby'} ne ""){
  607         $code .= " ORDER BY $form{'orderby'}";  #add sorting
  608         if($form{'desc'}){$code .= " DESC";}    #reverse sorting
  609     }
  610     $code .= ";";
  611     return $code;
  612 }
  613 ##############################################################################################################
  614 sub getCreationFields{
  615     my $html = "";
  616     if(exists($form{'creationfnames'})){    #user has chosen some fields already
  617         my @names = split(//, $form{'creationfnames'});
  618         my @types = split(//, $form{'creationftypes'});
  619         my @sizes = split(//, $form{'creationfsizes'});
  620         my @nulls = split(//, $form{'creationfnulls'});
  621         for(my $count = 0; $count <= $#names; $count++){
  622             $html .= "<tr><td>$names[$count]</td><td>$types[$count]";
  623             if($sizes[$count] > 0){$html .= "($sizes[$count])";}    #print the size
  624             $html .= "</td>";
  625             if($nulls[$count] eq "Y"){$html .= "<td>YES</td>";} #show that this field is null
  626             else{$html .= "<td></td>";}
  627             $html .= "<td></td><td></td><td></td></tr>\n";
  628         }
  629     }
  630     return $html;
  631 }
  632 ############################################################################################################################
  633 sub uploadFile{
  634     my $file = shift;
  635     my $result = 0;
  636     if(open(SAVE, ">dump_uploads/$file")){  #create a new temp file on the server
  637         my $data;
  638         my $totalsize = 0;
  639         while(my $size = read($form{'dumpfile'}, $data, 1024)){ #read the contents of the file
  640             print SAVE $data;
  641             $totalsize += $size;    #save the size of this file
  642         }
  643         close SAVE;
  644         if($totalsize > 0){$result = 1;}    #got a valid file
  645         else{
  646             unlink("dump_uploads/$file");
  647             $error = "File: $file was empty";
  648         }
  649     }
  650     else{$error = "Could not save file: $file";}
  651     return $result;
  652 }
  653 ###############################################################################################################################
  654 sub processFile{
  655     my $file = shift;
  656     if(open(DUMP, "<dump_uploads/$file")){
  657         my @allSql; #this is where all the commands will be stored
  658         my $count = 0;  #this counts the commands found
  659         while(<DUMP>){
  660             chomp $_;
  661             $_ =~ s/\r//g;  #get rid of all trace of dos
  662             if($_ !~ m/^(--|#)/ && $_ ne ""){   #read all of the file in excluding comments and blank lines
  663                 if($_ =~ m/;$/){    #this a whole command or the end of one
  664                     $allSql[$count] .= $_;
  665                     $count++;
  666                 }
  667                 else{$allSql[$count] .= $_;}    #it is the first or middle part of a command
  668             }
  669         }
  670         close(DUMP);
  671         my $dbh = DBI -> connect("DBI:mysql:database=$form{'database'};host=$form{'host'}", $form{'user'}, $form{'password'});  #connect once incase we need to change databases
  672         if($dbh){
  673             foreach (@allSql){  #execute all of the commands
  674                 if($_ =~ m/^\w/){   #queries must start with a word
  675                     if(!$dbh -> do($_)){    #throw an error and end the loop if there is a problem with the query
  676                         $error = "Problem with query: " . $dbh -> errstr;
  677                         last;
  678                     }
  679                 }
  680             }
  681             return $count;
  682         }
  683         else{$error = "Cant connect to MySQL server: " . $DBI::errstr;}
  684     }
  685     else{$error = "Could not read dump file: $0";}
  686     return undef;
  687 }
  688 ##################################################################################################################
  689 sub createInsertForm{
  690     my($host, $user, $password, $database, $table) = @_;
  691     my $dbh = DBI -> connect("DBI:mysql:database=$database;host=$host", $user, $password);
  692     if($dbh){
  693         my $query = $dbh -> prepare("DESCRIBE $table;");
  694         if($query -> execute()){
  695             my $names = $query ->{'NAME'};  #all returned field names
  696             my $html = "";
  697             my $fCount = 0;
  698             #print "Content-type: text\html\n\n";
  699             while(my @row = $query -> fetchrow_array()){
  700                 $html .= "<tr><th valign=\"top\">$row[0]</th>";
  701                 $html .= "<td>";
  702                 #print "row[1] = $row[1]<br>\n";
  703                 if($row[1] =~ m/^tinytext|text|mediumtext|longtext|tinyblob|blob|mediumblob|longblob$/){    #these types need an text area instead
  704                     $html .= "<textarea name=\"insert_$fCount\" wrap=\"off\" cols=\"30\" rows=\"10\">";
  705                     if($row[4]){$html .= $row[4];}  #add default value
  706                     $html .= "</textarea>";
  707                 }
  708                 else{   #text type entry (defualt)
  709                     $html .= "<input type=\"text\" name=\"insert_$fCount\" size=\"35\"";
  710                     if($row[4]){$html .= " value=\"$row[4]\">";}    #add default value
  711                     else{$html .= ">";}
  712                 }
  713                 $html .= "</td><td valign=\"top\">$row[1]</td>";                
  714                 $html .= "</tr>\n";
  715                 $fCount++;
  716             }
  717             $query -> finish();
  718             return $html;
  719         }
  720         else{$error = "Problem with query: " . $dbh -> errstr;}
  721         $dbh -> disconnect();
  722     }
  723     else{$error = "Cant connect to MySQL server: " . $DBI::errstr;}
  724     return undef;
  725 }
  726 ##################################################################################################################
  727 sub createDumpFile{
  728     if(open(EXPORT, ">dump_downloads/$form{'key'}.sql")){   #able to create the export file
  729         print EXPORT "#WebMySQL $version dump\n\n";
  730         print EXPORT "#Host: $form{'host'}\n";
  731         print EXPORT "#Database: $form{'database'}\n";
  732         print EXPORT "#Server version: " . &getVariable($form{'host'}, $form{'user'}, $form{'password'}, $form{'database'}, "version") . "\n\n";
  733         print EXPORT "CREATE DATABASE IF NOT EXISTS $form{'database'};\n";
  734         print EXPORT "USE $form{'database'};\n";
  735         if(my @tables = &getTables($form{'host'}, $form{'user'}, $form{'password'}, $form{'database'})){
  736             my $dbh = DBI -> connect("DBI:mysql:database=$form{'database'};host=$form{'host'}", $form{'user'}, $form{'password'});
  737             if($dbh){
  738                 for(my $tCount = 0; $tCount <= $#tables; $tCount++){    #work through all of the tables
  739                     print EXPORT "\n#Table structure for table '$tables[$tCount]'\n";
  740                     my $query = $dbh -> prepare("SHOW CREATE TABLE $form{'database'}.$tables[$tCount];");
  741                     if($query -> execute()){
  742                         my(undef, $creation) = $query -> fetchrow_array();
  743                         $query -> finish();
  744                         print EXPORT "$creation;\n";
  745                     }
  746                     else{$error = "Cant retrieve creation details $tables[$tCount] table: " . $dbh -> errstr;}
  747                     print EXPORT "\n#Dumping data for table '$tables[$tCount]'\n";
  748                     $query = $dbh -> prepare("SELECT * FROM $tables[$tCount];");
  749                     if($query -> execute()){
  750                         while(my @fields = $query -> fetchrow_array()){ #print a row at a time
  751                             print EXPORT "INSERT INTO $tables[$tCount] VALUES(";
  752                             for(my $f = 0; $f <= $#fields; $f++){   #loop over each field
  753                                 print EXPORT "'";
  754                                 if(defined($fields[$f])){
  755                                     $fields[$f] =~ s/'/\\'/g;   #so field values dont break out of the surrounding quotes
  756                                     print EXPORT $fields[$f];}  #only print the value if its something
  757                                 print EXPORT "'";
  758                                 if($f < $#fields){print EXPORT ", ";}   #print the field separator
  759                             }
  760                             print EXPORT ");\n";
  761                         }
  762                         $query -> finish();
  763                     }
  764                                 
  765                 }
  766                 $dbh -> disconnect();
  767             }
  768             else{$error = "Cant connect to MySQL server: " . $DBI::errstr;}
  769         }
  770         close(EXPORT);
  771     }
  772     else{$error = "Unable to create export file: $!";}
  773 }
  774 ####################################################################################################################
  775 sub queueInsert{    #display the insert page and queue the pending insert records
  776     print "Content-type: text/html\n\n";
  777                     use Data::Dumper;
  778                     print Dumper(%form);
  779     if($form{'tables'} =~ m/^(\w+)$/){  #safety check on table name
  780         my $table = $1;
  781         if(my @fields = &getFields($form{'host'}, $form{'user'}, $form{'password'}, $form{'database'}, $form{'tables'})){
  782             my $rCount = 0;
  783             while(exists($form{'insertdata' . $rCount})){
  784                 #print "insertdata$rCount alread exists with '" . $form{'insertdata' . $rCount} . "'<br0>\n";
  785             
  786             $rCount++;} #find how many insert records we already have
  787             print "got a total of $rCount previous records<br>\n";
  788             $form{'insertdata' . $rCount} = "";
  789             for(my $fCount = 0; $fCount <= $#fields; $fCount++){    #loop through all of the fields creating an insert record
  790                 $form{'insertdata' . $rCount} .= &toHex($form{'insert_' . $fCount}) . "";
  791             }
  792             chop $form{'insertdata' . $rCount}; #get rid of the last separator
  793             $form{'input'} = &createInsertForm($form{'host'}, $form{'user'}, $form{'password'}, $form{'database'}, $table);
  794             $form{'fields'} = "";
  795             foreach(@fields){   #create the field name headings
  796                 $_ =~ s/^$table\.//;    #we just want the field name not the table name aswell
  797                 $form{'fields'} .= "<th>$_</th>";
  798             }
  799             $form{'fields'} .= "<th>&nbsp;</th>\n"; #make an extra column for the delete buttons
  800             #now show the previously stored rows
  801             $form{'currentrecords'} = "";
  802             foreach my $key (keys %form){   #search through the form/session data
  803                 print "key $key value $form{$key}<br>\n";
  804                 if($key =~ m/^insertdata(\d+)$/){   #found a preveious record
  805                     if($form{$key} ne ""){  #we have some data in the current record
  806                         my $id = $1;    #so we can delete this record
  807                         $form{'currentrecords'} .= "<tr>";
  808                         my @pFields = split(//, $form{$key});
  809                         for(my $pCount = 0; $pCount <= $#fields; $pCount++){    #find the different fields
  810                             if(defined($pFields[$pCount])){ #display the entered value
  811                                 $pFields[$pCount] = &fromHex($pFields[$pCount]);    #convert from hex to display
  812                                 $form{'currentrecords'} .= "<td>$pFields[$pCount]</td>";
  813                             }
  814                             else{$form{'currentrecords'} .= "<td>&nbsp;</td>";} #no value entered for this field
  815                         }
  816                         $form{'currentrecords'} .= "<td valign=\"top\"><form action=\"$ENV{'SCRIPT_NAME'}\" method=\"post\">";
  817                         $form{'currentrecords'} .= "<input type=\"hidden\" name=\"key\" value=\"$form{'key'}\">";
  818                         $form{'currentrecords'} .= "<input type=\"hidden\" name=\"action\" value=\"insertremovequeued\">";
  819                         $form{'currentrecords'} .= "<input type=\"hidden\" name=\"qid\" value=\"$id\">";
  820                         $form{'currentrecords'} .= "<input type=\"submit\" value=\"Remove\">";
  821                         $form{'currentrecords'} .= "</form></td></tr>\n";
  822                     }
  823                     else{delete($form{$key});}  #we fix our own problems here!
  824                 }
  825             }
  826             if($form{'currentrecords'} eq ""){$form{'currentrecords'} = "<tr><td colspan=\"" . ($#fields + 1) . "\"><i>No records waiting to be inserted</i></td></tr>\n";}
  827             &updateKey($form{'key'});
  828         }
  829     }
  830     else{$error = "Table name contains invalid characters";}                
  831 }
  832 ##################################################################################################################
  833 sub toHex{  #converts a string to hex
  834     my $string = shift;
  835     $string =~ s/([\W|\w])/"\\x" . uc(sprintf("%2.2x",ord($1)))/eg;
  836     return $string;
  837 }
  838 ##################################################################################################################
  839 sub fromHex{    #converts from hex to ASCII
  840     my $string = shift;
  841     $string =~ s/\\x([a-fA-F0-9]{2,2})/chr(hex($1))/eg;
  842     return $string;
  843 }
  844 ##################################################################################################################
  845 sub displaySQL{ #safely formats sql for displaying in a browser
  846     my $sql = shift;
  847     $sql =~ s/</&lt;/g;
  848     $sql =~ s/>/&gt;/g; 
  849     $sql =~ s/\n/<br>/g;
  850     return $sql;
  851 }