File Coverage

cgi-bin/webmysql/webmysql.cgi
Criterion Covered Total %
statement 13 15 86.6
branch n/a
condition n/a
subroutine 5 5 100.0
pod n/a
total 18 20 90.0


\n";} #convert to html format \n";} #convert to html format "; "; "; "; ";} ";} \n"; \n"; \n"; \n"; \n"; \n"; \n";} #cant delete this table \n"; ";} #create the field name headings "; ";} #show that this field is null ";} \n"; "; "; \n"; "; \n"; #make an extra column for the delete buttons "; "; ";} #no value entered for this field \n"; \n";}
line stmt bran cond sub pod time code
1             #!/usr/bin/env perl
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 1     1   4 use strict;
  1         1  
  1         24  
17 1     1   2 use warnings;
  1         1  
  1         19  
18 1     1   789 use CGI;
  1         20733  
  1         6  
19 1     1   1508 use DBI;
  1         11674  
  1         54  
20 1     1   247 use DBD::mysql;
  0            
  0            
21             use lib "lib";
22             use Plack::App::WebMySQL;
23             use Plack::App::WebMySQL::Key;
24             use Plack::App::WebMySQL::General;
25             use Plack::App::WebMySQL::Sql;
26             use constant; #for perl2exe
27             $error = undef; #reset this every time, as PSGI will keep it for the lifetime of the server
28             $| = 1; #disable output buffering, helps with CGIWrap
29             &expireKeys; #remove old keys from server
30             if(&getData()){ #get the data from the last page's form
31             if($form{'key'}){ #got a key do normal actions
32             if(&readKey($form{'key'})){ #read the server side cookie for state
33             $form{'menu'} = &parseFragmentToString("menu"); #load the top menu
34             if($form{'action'} eq "mainmenu"){} #just display a template
35             elsif($form{'action'} eq "logout"){&deleteKey($form{'key'});} #remove the server side cookie
36             elsif($form{'action'} eq "query"){ #pick what type of query to run
37             &updateKey($form{'key'});
38             }
39             elsif($form{'action'} eq "selectchoosetable"){ #pick what table to run the query type on
40             $form{'tablelist'} = "";
41             if(my @tables = &getTables($form{'host'}, $form{'user'}, $form{'password'}, $form{'database'})){
42             for(my $tCount = 0; $tCount <= $#tables; $tCount++){$form{'tablelist'} .= "
$tables[$tCount]
43             &updateKey($form{'key'});
44             }
45             }
46             elsif($form{'action'} eq "selectchoosefields"){ #pick what fields to use in the query
47             my @tablesTemp;
48             foreach my $name (keys %form){
49             if($name =~ m/^table\d+$/){push(@tablesTemp, $form{$name});}
50             }
51             if($#tablesTemp > -1){ #one or more tables have been selected
52             $form{'tables'} = join(", ", @tablesTemp); #for the server side cookie
53             if(my @fields = &getFields($form{'host'}, $form{'user'}, $form{'password'}, $form{'database'}, $form{'tables'})){
54             $form{'fieldlist'} = "";
55             for(my $count = 0; $count <= $#fields; $count++){$form{'fieldlist'} .= "
$fields[$count]
56             &updateKey($form{'key'});
57             }
58             }
59             else{$error = "You did not select any tables to query";}
60             }
61             elsif($form{'action'} eq "selectchoosecriteria"){ #pick the criteria for the query
62             my @tmpFields;
63             foreach my $name (keys %form){
64             if($name =~ m/^field\d+$/){push(@tmpFields, $form{$name});}
65             }
66             $form{'fields'} = join(", ", @tmpFields); #for the server side cookie
67             if(my @fields = &getFields($form{'host'}, $form{'user'}, $form{'password'}, $form{'database'}, $form{'tables'})){
68             if($form{'tables'} =~ m/, /){ #more than one table selected, show the join options
69             my @tables = split(/, /, $form{'tables'});
70             $form{'joinlist'} = "

Please select how you want to join the tables to $tables[0]

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