1# mysql-lib.pl 2# Common MySQL functions 3 4BEGIN { push(@INC, ".."); }; 5use WebminCore; 6&init_config(); 7 8require 'view-lib.pl'; 9if ($config{'mysql_libs'}) { 10 $ENV{$gconfig{'ld_env'}} .= ':' if ($ENV{$gconfig{'ld_env'}}); 11 $ENV{$gconfig{'ld_env'}} .= $config{'mysql_libs'}; 12 } 13if ($config{'mysql'} =~ /^(\S+)\/bin\/mysql$/ && $1 ne '' && $1 ne '/usr') { 14 $ENV{$gconfig{'ld_env'}} .= ':' if ($ENV{$gconfig{'ld_env'}}); 15 $ENV{$gconfig{'ld_env'}} .= "$1/lib"; 16 } 17if ($module_info{'usermin'}) { 18 # Usermin always runs this module as the logged-in Unix user. 19 # %access is faked up to prevent attempts to create and delete DBs 20 &switch_to_remote_user(); 21 &create_user_config_dirs(); 22 %access = ( 'create', 0, 23 'delete', 0, 24 'bpath', '/', 25 'buser', 'root', 26 'edonly', 0 ); 27 if ($config{'useident'} ne 'yes') { 28 $mysql_login = $userconfig{'login'}; 29 $mysql_pass = $userconfig{'pass'}; 30 } 31 chop($mysql_version = &read_file_contents( 32 "$user_module_config_directory/version")); 33 $max_dbs = $userconfig{'max_dbs'}; 34 $commands_file = "$user_module_config_directory/commands"; 35 $sql_charset = $userconfig{'charset'}; 36 %displayconfig = %userconfig; 37 } 38else { 39 # Webmin connects to the database as the user specified in the per-user 40 # or global config 41 %access = &get_module_acl(); 42 if ($access{'user'} && !$use_global_login) { 43 $mysql_login = $access{'user'}; 44 $mysql_pass = $access{'pass'}; 45 } 46 else { 47 $mysql_login = $config{'login'}; 48 $mysql_pass = $config{'pass'}; 49 } 50 chop($mysql_version = &read_file_contents( 51 "$module_config_directory/version")); 52 $mysql_version ||= &get_mysql_version(); 53 $cron_cmd = "$module_config_directory/backup.pl"; 54 $max_dbs = $config{'max_dbs'}; 55 $commands_file = "$module_config_directory/commands"; 56 $sql_charset = $config{'charset'}; 57 %displayconfig = %config; 58 } 59$authstr = &make_authstr(); 60$master_db = 'mysql'; 61$password_func = $config{'passwd_mode'} ? "old_password" : "password"; 62 63@type_list = ('tinyint', 'smallint', 'mediumint', 'int', 'bigint', 64 'float', 'double', 'decimal', 'date', 'datetime', 'timestamp', 65 'time', 'year', 'char', 'varchar', 'tinyblob', 'tinytext', 66 'blob', 'text', 'mediumblob', 'mediumtext', 'longblob', 'longtext', 67 'enum', 'set'); 68 69@priv_cols = ('Host', 'User', 'Password', 'Select_priv', 'Insert_priv', 'Update_priv', 'Delete_priv', 'Create_priv', 'Drop_priv', 'Reload_priv', 'Shutdown_priv', 'Process_priv', 'File_priv', 'Grant_priv', 'References_priv', 'Index_priv', 'Alter_priv', 'Show_db_priv', 'Super_priv', 'Create_tmp_table_priv', 'Lock_tables_priv', 'Execute_priv', 'Repl_slave_priv', 'Repl_client_priv', 'Create_view_priv', 'Show_view_priv', 'Create_routine_priv', 'Alter_routine_priv', 'Create_user_priv'); 70 71if (!$config{'nodbi'}) { 72 # Check if we have DBI::mysql 73 eval <<EOF; 74use DBI; 75\$driver_handle = DBI->install_driver("mysql"); 76EOF 77} 78 79# Fix text if we're running MariaDB 80if ($mysql_version =~ /mariadb/i) { 81 foreach my $t (keys %text) { 82 $text{$t} =~ s/MySQL/MariaDB/g; 83 } 84 } 85 86if (&compare_version_numbers($mysql_version, "5.5") >= 0) { 87 @mysql_set_variables = ( "key_buffer_size", "sort_buffer_size", 88 "net_buffer_length" ); 89 } 90else { 91 @mysql_set_variables = ( "key_buffer", "sort_buffer", 92 "net_buffer_length" ); 93 } 94if (&compare_version_numbers($mysql_version, "5.6") >= 0) { 95 @mysql_number_variables = ( "table_open_cache", "max_connections" ); 96 } 97else { 98 @mysql_number_variables = ( "table_cache", "max_connections" ); 99 } 100@mysql_byte_variables = ( "query_cache_size", "max_allowed_packet" ); 101if (&compare_version_numbers($mysql_version, "5") >= 0) { 102 push(@mysql_byte_variables, "myisam_sort_buffer_size"); 103 } 104else { 105 push(@mysql_set_variables, "myisam_sort_buffer_size"); 106 } 107 108# make_authstr([login], [pass], [host], [port], [sock], [unix-user]) 109# Returns a string to pass to MySQL commands to login to the database 110sub make_authstr 111{ 112local $login = defined($_[0]) ? $_[0] : $mysql_login; 113local $pass = defined($_[1]) ? $_[1] : $mysql_pass; 114local $host = defined($_[2]) ? $_[2] : $config{'host'}; 115local $port = defined($_[3]) ? $_[3] : $config{'port'}; 116local $sock = defined($_[4]) ? $_[4] : $config{'sock'}; 117local $unix = $_[5]; 118if (&supports_env_pass($unix)) { 119 $ENV{'MYSQL_PWD'} = $pass; 120 } 121return ($sock ? " -S $sock" : ""). 122 ($host ? " -h $host" : ""). 123 ($port ? " -P $port" : ""). 124 ($login ? " -u ".quotemeta($login) : ""). 125 (&supports_env_pass($unix) ? "" : # Password comes from environment 126 $pass && &compare_version_numbers($mysql_version, "4.1") >= 0 ? 127 " --password=".quotemeta($pass) : 128 $pass ? " -p".quotemeta($pass) : ""); 129} 130 131# is_mysql_running() 132# Returns 1 if mysql is running, 0 if not, or -1 if running but 133# inaccessible without a password. When called in an array context, also 134# returns the full error message 135sub is_mysql_running 136{ 137# First type regular connection 138if ($driver_handle && !$config{'nodbi'}) { 139 local $main::error_must_die = 1; 140 local ($data, $rv); 141 eval { $data = &execute_sql_safe(undef, "select version()"); }; 142 local $err = $@; 143 $err =~ s/\s+at\s+\S+\s+line.*$//; 144 if ($@ =~ /denied|password/i) { 145 $rv = -1; 146 } 147 elsif ($@ =~ /connect/i) { 148 $rv = 0; 149 } 150 elsif ($data->{'data'}->[0]->[0] =~ /^\d/) { 151 $rv = 1; 152 } 153 if (defined($rv)) { 154 return wantarray ? ( $rv, $err ) : $rv; 155 } 156 } 157 158# Fall back to mysqladmin command 159local $out = &backquote_command( 160 "\"$config{'mysqladmin'}\" $authstr status 2>&1"); 161local $rv = $out =~ /uptime/i ? 1 : 162 $out =~ /denied|password/i ? -1 : 0; 163$out =~ s/^.*\Q$config{'mysqladmin'}\E\s*:\s*//; 164return wantarray ? ($rv, $out) : $rv; 165} 166 167# list_databases() 168# Returns a list of all databases 169sub list_databases 170{ 171local @rv; 172eval { 173 # First try using SQL 174 local $main::error_must_die = 1; 175 local $t = &execute_sql_safe($master_db, "show databases"); 176 @rv = map { $_->[0] } @{$t->{'data'}}; 177 }; 178if (!@rv || $@) { 179 # Fall back to mysqlshow command 180 open(DBS, "\"$config{'mysqlshow'}\" $authstr |"); 181 local $t = &parse_mysql_table(DBS); 182 close(DBS); 183 ref($t) || &error("Failed to list databases : $t"); 184 @rv = map { $_->[0] } @{$t->{'data'}}; 185 } 186return sort { lc($a) cmp lc($b) } @rv; 187} 188 189# list_tables(database, [empty-if-denied], [no-filter-views]) 190# Returns a list of tables in some database 191sub list_tables 192{ 193my ($db, $empty_denied, $include_views) = @_; 194my @rv; 195eval { 196 # First try using SQL 197 local $main::error_must_die = 1; 198 local $t = &execute_sql_safe($db, "show tables"); 199 @rv = map { $_->[0] } @{$t->{'data'}}; 200 }; 201if ($@) { 202 # Fall back to mysqlshow command 203 local $tspec = $db =~ /_/ ? "%" : ""; 204 open(DBS, "\"$config{'mysqlshow'}\" $authstr ". 205 quotemeta($db)." $tspec 2>&1 |"); 206 local $t = &parse_mysql_table(DBS); 207 close(DBS); 208 if ($t =~ /access denied/i) { 209 if ($empty_denied) { 210 return ( ); 211 } 212 else { 213 &error($text{'edenied'}); 214 } 215 } 216 elsif (!ref($t)) { 217 &error("<tt>".&html_escape($t)."</tt>"); 218 } 219 @rv = map { $_->[0] } @{$t->{'data'}}; 220 } 221 222# Filter out views 223if (!$include_views) { 224 if (&supports_views()) { 225 my %views = map { $_, 1 } &list_views($db); 226 @rv = grep { !$views{$_} } @rv; 227 } 228 } 229return @rv; 230} 231 232# table_structure(database, table) 233# Returns a list of hashes detailing the structure of a table 234sub table_structure 235{ 236local $s = &execute_sql_safe($_[0], "desc "."estr($_[1])); 237local (@rv, $r); 238local (%tp, $i); 239for($i=0; $i<@{$s->{'titles'}}; $i++) { 240 $tp{lc($s->{'titles'}->[$i])} = $i; 241 } 242my $i = 0; 243foreach $r (@{$s->{'data'}}) { 244 push(@rv, { 'field' => $r->[$tp{'field'}], 245 'type' => $r->[$tp{'type'}], 246 'null' => $r->[$tp{'null'}], 247 'key' => $r->[$tp{'key'}], 248 'default' => $r->[$tp{'default'}], 249 'extra' => $r->[$tp{'extra'}], 250 'index' => $i++ }); 251 } 252return @rv; 253} 254 255# table_field_sizes(db, table) 256# Returns a hash mapping field names to sizes 257sub table_field_sizes 258{ 259local %rv; 260foreach my $s (&table_structure(@_)) { 261 if ($s->{'type'} =~ /^\S+\((\d+)(,\d+)?\)/) { 262 $rv{lc($s->{'field'})} = $1; 263 } 264 } 265return %rv; 266} 267 268# execute_sql(database, command, [param, ...]) 269# Executes some SQL and returns the results, after checking for the user's 270# readonly status. 271sub execute_sql 272{ 273return { } if (&is_readonly_mode()); 274return &execute_sql_safe(@_); 275} 276 277# execute_sql_safe(database, command, [param, ...]) 278# Executes some SQL and returns the results as a hash ref with titles and 279# data keys. 280sub execute_sql_safe 281{ 282local $sql = $_[1]; 283@params = @_[2..$#_]; 284if ($gconfig{'debug_what_sql'}) { 285 # Write to Webmin debug log 286 local $params; 287 for(my $i=0; $i<@params; $i++) { 288 $params .= " ".$i."=".$params[$i]; 289 } 290 &webmin_debug_log('SQL', "db=$_[0] sql=$sql".$params); 291 } 292$sql = &escape_backslashes_in_quotes($sql); 293if ($driver_handle && !$config{'nodbi'}) { 294 # Use the DBI interface 295 local $cstr = "database=$_[0]"; 296 $cstr .= ";host=$config{'host'}" if ($config{'host'}); 297 $cstr .= ";port=$config{'port'}" if ($config{'port'}); 298 $cstr .= ";mysql_socket=$config{'sock'}" if ($config{'sock'}); 299 $cstr .= ";mysql_read_default_file=$config{'my_cnf'}" 300 if (-r $config{'my_cnf'}); 301 local $dbh = $driver_handle->connect($cstr, $mysql_login, $mysql_pass, 302 { }); 303 $dbh || &error("DBI connect failed : ",$driver_handle->errstr); 304 if ($sql_charset) { 305 # Switch to correct character set 306 local $sql = "set names '$sql_charset'"; 307 local $cmd = $dbh->prepare($sql); 308 if (!$cmd) { 309 &error(&text('esql', "<tt>".&html_escape($sql)."</tt>", 310 "<tt>".&html_escape($dbh->errstr)."</tt>")); 311 } 312 if (!$cmd->execute()) { 313 &error(&text('esql', "<tt>".&html_escape($sql)."</tt>", 314 "<tt>".&html_escape($dbh->errstr)."</tt>")); 315 } 316 $cmd->finish(); 317 } 318 local $cmd = $dbh->prepare($sql); 319 if (!$cmd) { 320 &error(&text('esql', "<tt>".&html_escape($_[1])."</tt>", 321 "<tt>".&html_escape($dbh->errstr)."</tt>")); 322 } 323 if (!$cmd->execute(@params)) { 324 &error(&text('esql', "<tt>".&html_escape($_[1])."</tt>", 325 "<tt>".&html_escape($dbh->errstr)."</tt>")); 326 } 327 local (@data, @row); 328 local @titles = @{$cmd->{'NAME'}}; 329 while(@row = $cmd->fetchrow()) { 330 push(@data, [ @row ]); 331 } 332 $cmd->finish(); 333 $dbh->disconnect(); 334 return { 'titles' => \@titles, 335 'data' => \@data }; 336 } 337else { 338 # Use the mysql command program 339 local $temp = &transname(); 340 if (@params) { 341 # Sub in ? parameters 342 $sql = &replace_sql_parameters($sql, @params); 343 } 344 open(TEMP, ">$temp"); 345 if ($sql_charset) { 346 print TEMP "set names '$sql_charset';\n"; 347 } 348 print TEMP $sql,"\n"; 349 close(TEMP); 350 open(DBS, "\"$config{'mysql'}\" $authstr -E -t ".quotemeta($_[0])." <$temp 2>&1 |"); 351 local $t = &parse_mysql_vertical(DBS); 352 close(DBS); 353 unlink($temp); 354 if (!ref($t)) { 355 $t =~ s/^ERROR[^:]*://; 356 &error(&text('esql', "<tt>".&html_escape($_[1])."</tt>", 357 "<tt>".&html_escape($t)."</tt>")); 358 } 359 return $t; 360 } 361} 362 363# replace_sql_parameters(sql, params) 364# Returns a string with ? replaced by parameter text 365sub replace_sql_parameters 366{ 367my ($sql, @params) = @_; 368my $pos = -1; 369foreach my $p (@params) { 370 $pos = index($sql, '?', $pos+1); 371 &error("Incorrect number of parameters") if ($pos < 0); 372 local $qp = $p; 373 $qp =~ s/'/''/g; 374 $qp = !defined($qp) ? 'NULL' : "'$qp'"; 375 $sql = substr($sql, 0, $pos).$qp.substr($sql, $pos+1); 376 $pos += length($qp)-1; 377 } 378return $sql; 379} 380 381# execute_sql_logged(database, command, param, ...) 382# Calls execute_sql, but logs the command first 383sub execute_sql_logged 384{ 385local ($db, $sql, @params) = @_; 386if (@params) { 387 eval { 388 local $main::error_must_die = 1; 389 $sql = &replace_sql_parameters($sql, @params); 390 } 391 } 392&additional_log('sql', $db, $sql); 393return &execute_sql(@_); 394} 395 396# parse_mysql_table(handle) 397# Given a filehandle, parses a text table in the format mysql uses 398sub parse_mysql_table 399{ 400local $fh = $_[0]; 401local ($line, $i, @edge); 402do { 403 # skip to table top 404 $line = <$fh>; 405 return $line if ($line =~ /^(ERROR|\S*mysqlshow:)/); 406 } while($line && $line !~ /^\+/); 407for($i=0; $i<length($line); $i++) { 408 push(@edge, $i) if (substr($line, $i, 1) eq '+'); 409 } 410$line = <$fh>; # skip first row of -'s 411local @titles = &parse_mysql_line($line, \@edge); 412$line = <$fh>; # skip next row of -'s 413local @data; 414while(1) { 415 $line = <$fh>; 416 last if (!$line || $line !~ /^\|/); 417 while($line !~ /\|\s+$/) { 418 # Line has a return in it! 419 $line .= <$fh>; 420 } 421 push(@data, [ &parse_mysql_line($line, \@edge) ]); 422 } 423return { 'titles' => \@titles, 424 'data' => \@data }; 425} 426 427# parse_mysql_line(line, &edges) 428sub parse_mysql_line 429{ 430local @rv; 431for($i=0; $i<@{$_[1]}-1; $i++) { 432 local $w = substr($_[0], $_[1]->[$i]+1, 433 $_[1]->[$i+1] - $_[1]->[$i] - 2); 434 $w =~ s/^\s//; 435 $w =~ s/\s+$//; 436 $w =~ s/\\/\\\\/g; 437 $w =~ s/\n/\\n/g; 438 push(@rv, $w); 439 } 440return @rv; 441} 442 443# parse_mysql_vertical(handle) 444# Parses mysql output in the -E format 445sub parse_mysql_vertical 446{ 447local (@data, @titles, $row = -1, $col, %hascol); 448local $fh = $_[0]; 449local $line = <$fh>; 450if (!$line) { 451 # No output at all - must be a non-select 452 return { }; 453 } 454return $line if ($line =~ /^ERROR/); 455local $errtxt = &text('eparse', "<tt>mysql</tt>", "<tt>DBI</tt>", 456 "<tt>DBD::mysql</tt>"); 457while($line) { 458 $line =~ s/\r|\n//g; 459 if ($line =~ /^\*\*\*/) { 460 # A row header 461 $row++; 462 $col = -1; 463 $data[$row] = [ ]; 464 } 465 elsif ($line =~ /^\s*([^:\s]+): (.*)/ && ($row == 0 || $hascol{$1})) { 466 # A new column 467 $col++; 468 $titles[$col] = $1; 469 $row >= 0 || return $errtxt; 470 $data[$row]->[$col] = $2; 471 $hascol{$1}++; 472 } 473 else { 474 # Continuing the last column 475 $row >= 0 || return $errtxt; 476 $data[$row]->[$col] .= "\n".$line; 477 } 478 $line = <$fh>; 479 } 480return { 'titles' => \@titles, 481 'data' => \@data }; 482} 483 484sub can_edit_db 485{ 486if ($module_info{'usermin'}) { 487 foreach $l (split(/\t/, $config{'access'})) { 488 if ($l =~ /^(\S+):\s*(.*)$/ && 489 ($1 eq $remote_user || $1 eq '*')) { 490 local @dbs = split(/\s+/, $2); 491 local $d; 492 foreach $d (@dbs) { 493 $d =~ s/\$REMOTE_USER/$remote_user/g; 494 return 1 if ($d eq '*' || $_[0] =~ /^$d$/); 495 } 496 return 0; 497 } 498 } 499 return 0; 500 } 501else { 502 local $d; 503 return 1 if ($access{'dbs'} eq '*'); 504 foreach $d (split(/\s+/, $access{'dbs'})) { 505 return 1 if ($d && $d eq $_[0]); 506 } 507 return 0; 508 } 509} 510 511# supports_backup_db(name) 512# Returns 1 if some database can be backed up 513sub supports_backup_db 514{ 515return $_[0] ne "information_schema" && 516 $_[0] ne "performance_schema"; 517} 518 519# list_accessible_databases() 520# Returns a list of databases that the current user may access to. Returns 521# an empty list if he has all of them. 522sub list_accessible_databases 523{ 524if ($module_info{'usermin'}) { 525 # From Usermin list 526 local @rv; 527 foreach $l (split(/\t/, $config{'access'})) { 528 if ($l =~ /^(\S+):\s*(.*)$/ && 529 ($1 eq $remote_user || $1 eq '*')) { 530 push(@rv, split(/\s+/, $2)); 531 } 532 } 533 return @rv; 534 } 535else { 536 # From Webmin access control list 537 return ( ) if ($access{'dbs'} eq '*'); 538 return split(/\s+/, $access{'dbs'}); 539 } 540} 541 542# split_table(&titles, &checkboxes, &links, &col1, &col2, ...) 543# Outputs a table that is split into two parts 544sub split_table 545{ 546local $mid = int((@{$_[2]}+1) / 2); 547local ($i, $j); 548print "<table width=100%><tr>\n"; 549foreach $s ([0, $mid-1], [$mid, @{$_[2]}-1]) { 550 print "<td width=50% valign=top>\n"; 551 552 # Header 553 local @tds = $_[1] ? ( "width=5" ) : ( ); 554 if ($s->[0] <= $s->[1]) { 555 local @hcols; 556 foreach $t (@{$_[0]}) { 557 push(@hcols, $t); 558 } 559 print &ui_columns_start(\@hcols, 100, 0, \@tds); 560 } 561 562 for($i=$s->[0]; $i<=$s->[1]; $i++) { 563 local @cols; 564 push(@cols, "<a href='$_[2]->[$i]'>$_[3]->[$i]</a>"); 565 for($j=4; $j<@_; $j++) { 566 push(@cols, $_[$j]->[$i]); 567 } 568 if ($_[1]) { 569 print &ui_checked_columns_row(\@cols, \@tds, "d", $_[1]->[$i]); 570 } 571 else { 572 print &ui_columns_row(\@cols, \@tds); 573 } 574 } 575 if ($s->[0] <= $s->[1]) { 576 print &ui_columns_end(); 577 } 578 print "</td>\n"; 579 } 580print "</tr></table>\n"; 581} 582 583# select_db(db) 584sub select_db 585{ 586local $rv; 587local @dbs = &list_databases(); 588local $d; 589if ($access{'perms'} == 2 && $access{'dbs'} ne '*') { 590 # Can only select his own databases 591 $rv = &ui_select("dbs", $_[0], 592 [ grep { &can_edit_db($_) } @dbs ], 1, 0, $_[0] ? 1 : 0); 593 } 594else { 595 # Can select any databases 596 local $ind = &indexof($_[0],@dbs) >= 0; 597 local $js1 = "onChange='form.db_def[1].checked = true'"; 598 local $js2 = "onClick='form.db_def[2].checked = true'"; 599 $rv = &ui_radio("db_def", $_[0] eq '%' || $_[0] eq '' ? 1 : 600 $ind ? 2 : 0, 601 [ [ 1, $text{'host_any'} ], 602 [ 2, $text{'host_sel'}." ". 603 &ui_select("dbs", $_[0], \@dbs, 1, 0, 0, 0, $js1) ], 604 [ 0, $text{'host_otherdb'}." ". 605 &ui_textbox("db", $_[0] eq '%' || $_[0] eq '' || 606 $ind ? '' : $_[0], 30, 0, 607 undef, $js2) ] ]); 608 } 609return $rv; 610} 611 612sub quote_table 613{ 614return "estr($_[0]); 615} 616 617# quotestr(string) 618sub quotestr 619{ 620local $rv = $_[0]; 621if (&supports_quoting()) { 622 return "`$rv`"; 623 } 624else { 625 return $rv; 626 } 627} 628 629# escapestr(string) 630# Returns a string with quotes escaped, for use in SQL 631sub escapestr 632{ 633local $rv = $_[0]; 634$rv =~ s/'/''/g; 635return $rv; 636} 637 638# escape_backslashes_in_quotes(string) 639# Escapes backslashes, but only inside quoted strings 640sub escape_backslashes_in_quotes 641{ 642local ($str) = @_; 643local $rv; 644while($str =~ /^([^"]*)"([^"]*)"(.*)$/) { 645 local ($before, $quoted, $after) = ($1, $2, $3); 646 $quoted =~ s/\\/\\\\/g; 647 $rv .= $before.'"'.$quoted.'"'; 648 $str = $after; 649 } 650$rv .= $str; 651return $rv; 652} 653 654# supports_quoting() 655# Returns 1 if running mysql version 3.23.6 or later 656sub supports_quoting 657{ 658return &compare_version_numbers($mysql_version, "3.23.6") >= 0; 659} 660 661# supports_mysqldump_events() 662# Returns 1 if running mysqldump 5.1.8 or later, which supports (and needs) 663# the events flag 664sub supports_mysqldump_events 665{ 666return &compare_version_numbers($mysql_version, "5.1.8") >= 0; 667} 668 669# supports_routines() 670# Returns 1 if mysqldump supports routines 671sub supports_routines 672{ 673local $out = &backquote_command("$config{'mysqldump'} --help 2>&1 </dev/null"); 674return $out =~ /--routines/ ? 1 : 0; 675} 676 677# supports_views() 678# Returns 1 if this MySQL install supports views 679sub supports_views 680{ 681return &compare_version_numbers($mysql_version, "5") >= 0; 682} 683 684# supports_variables() 685# Returns 1 if running mysql version 4.0.3 or later 686sub supports_variables 687{ 688return &compare_version_numbers($mysql_version, "4.0.3") >= 0; 689} 690 691# supports_hosts() 692# Returns 1 if the hosts table exists 693sub supports_hosts 694{ 695return &compare_version_numbers($mysql_version, "5.7.16") < 0; 696} 697 698# supports_env_pass([run-as-user]) 699# Returns 1 if passing the password via an environment variable is supported 700sub supports_env_pass 701{ 702local ($user) = @_; 703if (&compare_version_numbers($mysql_version, "4.1") >= 0 && !$config{'nopwd'}) { 704 # Theortically possible .. but don't do this if ~/.my.cnf contains 705 # a [client] block with password= in it 706 my @uinfo = $user ? getpwnam($user) : getpwuid($<); 707 foreach my $cf ($config{'my_cnf'}, "$uinfo[7]/.my.cnf", 708 "$ENV{'HOME'}/.my.cnf") { 709 next if (!$cf || !-r $cf); 710 local @cf = &parse_mysql_config($cf); 711 local $client = &find("client", \@cf); 712 next if (!$client); 713 local $password = &find("password", $client->{'members'}); 714 return 0 if ($password ne ''); 715 } 716 return 1; 717 } 718return 0; 719} 720 721# working_env_pass() 722# Returns 1 if MYSQL_PWD can be used to pass the password to mysql 723sub working_env_pass 724{ 725return 1 if (!&supports_env_pass()); # Not even used 726local $config{'nodbi'} = 1; 727local $data; 728local $main::error_must_die = 1; 729eval { $data = &execute_sql_safe(undef, "select version()") }; 730return $@ || !$data ? 0 : 1; 731} 732 733# priv_fields(type) 734# Returns the names and descriptions of fields for user/db/host privileges 735sub priv_fields 736{ 737my ($type) = @_; 738if (!$priv_fields{$type}) { 739 $priv_fields{$type} = []; 740 foreach my $s (&table_structure("mysql", $type)) { 741 if ($s->{'field'} =~ /^(.*)_priv/i) { 742 push(@{$priv_fields{$type}}, 743 [ $s->{'field'}, $text{'user_priv_'.lc($1)} || 744 $s->{'field'} ]); 745 } 746 } 747 } 748return @{$priv_fields{$type}}; 749} 750 751# ssl_fields() 752# Returns the names of SSL fields that need to be set for new users 753sub ssl_fields 754{ 755my @desc = &table_structure($master_db, 'user'); 756my %fieldmap = map { $_->{'field'}, $_->{'index'} } @desc; 757return grep { $fieldmap{$_} } ('ssl_type', 'ssl_cipher', 758 'x509_issuer', 'x509_subject'); 759} 760 761# other_user_fields() 762# Returns the names of other non-default new user fields 763sub other_user_fields 764{ 765my @desc = &table_structure($master_db, 'user'); 766my %fieldmap = map { $_->{'field'}, $_->{'index'} } @desc; 767return grep { $fieldmap{$_} } ('authentication_string'); 768} 769 770sub is_blob 771{ 772return $_[0]->{'type'} =~ /(text|blob)$/i; 773} 774 775# get_mysql_version(&out) 776# Returns a version number, undef if one cannot be found, or -1 for a .so 777# problem. This is the version of the *local* mysql command, not necessarily 778# the remote server. Maybe include the suffix -MariaDB. 779sub get_mysql_version 780{ 781local $out = &backquote_command("\"$config{'mysql'}\" -V 2>&1"); 782${$_[0]} = $out if ($_[0]); 783if ($out =~ /lib\S+\.so/) { 784 return -1; 785 } 786elsif ($out =~ /(distrib|Ver)\s+((3|4|5|6|7|8|9|10)\.[0-9\.]*(\-[a-z0-9]+)?)/i) { 787 return $2; 788 } 789else { 790 return undef; 791 } 792} 793 794# get_remote_mysql_version() 795# Returns the version of the MySQL server, or -1 if unknown 796sub get_remote_mysql_version 797{ 798local $main::error_must_die = 1; 799local $data; 800eval { $data = &execute_sql_safe(undef, "select version()"); }; 801return -1 if ($@); 802return -1 if (!@{$data->{'data'}}); 803return $data->{'data'}->[0]->[0]; 804} 805 806# get_remote_mysql_variant() 807# Like get_remote_mysql_version, but returns a version number and variant 808sub get_remote_mysql_variant 809{ 810my $rv = &get_remote_mysql_version(); 811return ($rv) if ($rv <= 0); 812my $variant = "mysql"; 813my ($ver, $variant_) = $rv =~ /^([0-9\.]+)\-(.*)/; 814if ($ver && $variant_ && 815 ($rv !~ /ubuntu/i || ($rv =~ /ubuntu/i && $rv =~ /mariadb/i && $ver > 10))) { 816 $rv = $ver; 817 $variant = $variant_; 818 if ($variant =~ /mariadb/i) { 819 $variant = "mariadb"; 820 } 821 else { 822 $variant = "mysql"; 823 } 824 } 825return ($rv, $variant); 826} 827 828# save_mysql_version([number]) 829# Update the saved local MySQL version number 830sub save_mysql_version 831{ 832local ($ver) = @_; 833$ver ||= &get_mysql_version(); 834if ($ver) { 835 &open_tempfile(VERSION, ">$module_config_directory/version"); 836 &print_tempfile(VERSION, $ver,"\n"); 837 &close_tempfile(VERSION); 838 } 839} 840 841# date_subs(filename) 842# Does strftime-style date substitutions on a filename, if enabled 843sub date_subs 844{ 845local ($path) = @_; 846local $rv; 847if ($config{'date_subs'}) { 848 eval "use POSIX"; 849 eval "use posix" if ($@); 850 local @tm = localtime(time()); 851 &clear_time_locale(); 852 $rv = strftime($path, @tm); 853 &reset_time_locale(); 854 } 855else { 856 $rv = $path; 857 } 858if ($config{'webmin_subs'}) { 859 $rv = &substitute_template($rv, { }); 860 } 861return $rv; 862} 863 864# execute_before(db, handle, escape, path, db-for-config) 865# Executes the before-backup command for some DB, and sends output to the 866# given file handle. Returns 1 if the command succeeds, or 0 on failure 867sub execute_before 868{ 869local $cmd = $config{'backup_before_'.$_[4]}; 870if ($cmd) { 871 $ENV{'BACKUP_FILE'} = $_[3]; 872 local $h = $_[1]; 873 local $out; 874 local $rv = &execute_command($cmd, undef, \$out, \$out); 875 if ($h && $out) { 876 print $h $_[2] ? "<pre>".&html_escape($out)."</pre>" : $out; 877 } 878 return !$rv; 879 } 880return 1; 881} 882 883# execute_after(db, handle, escape, path, db-for-config) 884sub execute_after 885{ 886local $cmd = $config{'backup_after_'.$_[4]}; 887if ($cmd) { 888 $ENV{'BACKUP_FILE'} = $_[3]; 889 local $h = $_[1]; 890 local $out; 891 local $rv = &execute_command($cmd, undef, \$out, \$out); 892 if ($h && $out) { 893 print $h $_[2] ? "<pre>".&html_escape($out)."</pre>" : $out; 894 } 895 return !$rv; 896 } 897return 1; 898} 899 900# show_table_form(count) 901sub show_table_form 902{ 903my $rv; 904$rv = &ui_columns_start([ $text{'field_name'}, $text{'field_type'}, 905 $text{'field_size'}, $text{'table_nkey'}, 906 $text{'field_auto'}, $text{'field_null'}, 907 $text{'field_unsigned'}, $text{'field_default'} ]); 908for(my $i=0; $i<$_[0]; $i++) { 909 my @cols; 910 push(@cols, &ui_textbox("field_$i", undef, 20)); 911 push(@cols, &ui_select("type_$i", "", [ "", @type_list ])); 912 push(@cols, &ui_textbox("size_$i", undef, 10)); 913 push(@cols, &ui_checkbox("key_$i", 1, $text{'yes'}, 0)); 914 push(@cols, &ui_checkbox("auto_$i", 1, $text{'yes'}, 0)); 915 push(@cols, &ui_checkbox("null_$i", 1, $text{'yes'}, 1)); 916 push(@cols, &ui_checkbox("unsigned_$i", 1, $text{'yes'}, 0)); 917 push(@cols, &ui_textbox("default_$i", undef, 20)); 918 $rv .= &ui_columns_row(\@cols); 919 } 920$rv .= &ui_columns_end(); 921return $rv; 922} 923 924# parse_table_form(&extrafields, tablename) 925sub parse_table_form 926{ 927local @fields = @{$_[0]}; 928local $i; 929local (@auto, @pri); 930for($i=0; defined($in{"field_$i"}); $i++) { 931 next if (!$in{"field_$i"}); 932 $in{"field_$i"} =~ /^\S+$/ || 933 &error(&text('table_efield', $in{"field_$i"})); 934 $in{"type_$i"} || &error(&text('table_etype', $in{"field_$i"})); 935 if ($in{"type_$i"} eq 'enum' || $in{"type_$i"} eq 'set') { 936 local @ev = split(/\s+/, $in{"size_$i"}); 937 @ev || &error(&text('table_eenum', $in{"type_$i"}, 938 $in{"field_$i"})); 939 $in{"size_$i"} = join(",", map { "'$_'" } @ev); 940 } 941 if ($in{"size_$i"}) { 942 push(@fields, sprintf "%s %s(%s)", 943 "estr($in{"field_$i"}), $in{"type_$i"},$in{"size_$i"}); 944 } 945 else { 946 push(@fields, sprintf "%s %s", 947 "estr($in{"field_$i"}), $in{"type_$i"}); 948 } 949 if ($in{"unsigned_$i"}) { 950 $fields[@fields-1] .= " unsigned"; 951 } 952 if (!$in{"null_$i"}) { 953 $fields[@fields-1] .= " not null"; 954 } 955 if ($in{"key_$i"}) { 956 $in{"null_$i"} && &error(&text('table_epnull',$in{"field_$i"})); 957 push(@pri, $in{"field_$i"}); 958 } 959 if ($in{"auto_$i"}) { 960 push(@auto, $fields[@fields-1]); 961 push(@autokey, $in{"key_$i"}); 962 } 963 if ($in{"default_$i"}) { 964 $fields[@fields-1] .= " default '".$in{"default_$i"}."'"; 965 } 966 } 967@auto < 2 || &error($text{'table_eauto'}); 968@fields || &error($text{'table_enone'}); 969local @sql; 970local $sql = "create table "."estr($_[1])." (".join(",", @fields).")"; 971$sql .= " engine $in{'type'}" if ($in{'type'}); 972push(@sql, $sql); 973if (@pri) { 974 # Setup primary fields too 975 push(@sql, "alter table "."estr($_[1])." add primary key (". 976 join(",", map { "estr($_) } @pri).")"); 977 } 978if (@auto) { 979 # Make field auto-increment 980 push(@sql, "alter table "."estr($_[1]). 981 " modify $auto[0] auto_increment ". 982 ($autokey[0] ? "" : "unique")); 983 } 984return @sql; 985} 986 987# execute_sql_file(database, file, [user, pass], [unix-user]) 988# Executes some file of SQL commands, and returns the exit status and output 989sub execute_sql_file 990{ 991if (&is_readonly_mode()) { 992 return (0, undef); 993 } 994local ($db, $file, $user, $pass) = @_; 995local $authstr = &make_authstr($user, $pass); 996local $cs = $sql_charset ? "--default-character-set=".quotemeta($sql_charset) 997 : ""; 998local $temp = &transname(); 999&open_tempfile(TEMP, ">$temp"); 1000&print_tempfile(TEMP, "source ".$file.";\n"); 1001&close_tempfile(TEMP); 1002&set_ownership_permissions(undef, undef, 0644, $temp); 1003local $cmd = "$config{'mysql'} $authstr -t ".quotemeta($db)." ".$cs. 1004 " <".quotemeta($temp); 1005-r $file || return (1, "$file does not exist"); 1006if ($_[4] && $_[4] ne 'root' && $< == 0) { 1007 # Restoring as a Unix user 1008 $cmd = &command_as_user($_[4], 0, $cmd); 1009 } 1010local $out = &backquote_logged("$cmd 2>&1"); 1011local @rv = ($?, $? ? $out || "$cmd failed" : $out); 1012&make_authstr(); # Put back old password environment variable 1013return @rv; 1014} 1015 1016# start_mysql() 1017# Starts the MySQL database server, and returns undef on success or an 1018# error message on failure. 1019sub start_mysql 1020{ 1021local $temp = &transname(); 1022local $rv = &system_logged("($config{'start_cmd'}) >$temp 2>&1"); 1023local $out = `cat $temp`; unlink($temp); 1024if ($rv || $out =~ /failed/i) { 1025 return "<pre>".&html_escape($out)."</pre>"; 1026 } 1027return undef; 1028} 1029 1030# stop_mysql() 1031# Halts the MySQL database server, and returns undef on success or an 1032# error message on failure. 1033sub stop_mysql 1034{ 1035local $out; 1036if ($config{'stop_cmd'}) { 1037 $out = &backquote_logged("$config{'stop_cmd'} 2>&1"); 1038 } 1039else { 1040 $out = &backquote_logged("$config{'mysqladmin'} $authstr shutdown 2>&1"); 1041 } 1042if ($? || $out =~ /failed/i) { 1043 return "<pre>".&html_escape($out)."</pre>"; 1044 } 1045return undef; 1046} 1047 1048# split_enum(type) 1049# Returns a list of allowed values for an enum 1050sub split_enum 1051{ 1052local ($type) = @_; 1053if ($type =~ /^(enum|set)\((.*)\)$/) { 1054 $type = $2; 1055 } 1056local $esize = $type; 1057local @ev; 1058while($esize =~ /^'([^']*)'(,?)(.*)$/) { 1059 push(@ev, $1); 1060 $esize = $3; 1061 } 1062return @ev; 1063} 1064 1065# Returns 1 if the mysql server being managed is on this system 1066sub is_mysql_local 1067{ 1068return $config{'host'} eq '' || $config{'host'} eq 'localhost' || 1069 $config{'host'} eq &get_system_hostname() || 1070 &to_ipaddress($config{'host'}) eq &to_ipaddress(&get_system_hostname()); 1071} 1072 1073# get_mysql_config() 1074# Returns the parsed my.cnf file 1075sub get_mysql_config 1076{ 1077if (!scalar(@mysql_config_cache)) { 1078 if (!-r $config{'my_cnf'}) { 1079 return undef; 1080 } 1081 @mysql_config_cache = &parse_mysql_config($config{'my_cnf'}); 1082 } 1083return \@mysql_config_cache; 1084} 1085 1086# parse_mysql_config(file) 1087# Reads one MySQL config file 1088sub parse_mysql_config 1089{ 1090local ($file) = @_; 1091local @rv; 1092local $sect; 1093local $lnum = 0; 1094local $lref = &read_file_lines($file, 1); 1095local $_; 1096foreach (@$lref) { 1097 s/\r|\n//g; 1098 s/#.*$//; 1099 s/\s+$//; 1100 if (/^\s*\[(\S+)\]$/) { 1101 # Start of a section 1102 $sect = { 'name' => $1, 1103 'members' => [ ], 1104 'file' => $file, 1105 'line' => $lnum, 1106 'eline' => $lnum }; 1107 push(@rv, $sect); 1108 } 1109 elsif (/^\s*(\S+)\s*=\s*(.*)$/ && $sect) { 1110 # Variable in a section 1111 push(@{$sect->{'members'}}, 1112 { 'name' => $1, 1113 'value' => $2, 1114 'file' => $file, 1115 'line' => $lnum }); 1116 $sect->{'eline'} = $lnum; 1117 } 1118 elsif (/^\s*(\S+)$/ && $sect) { 1119 # Single directive in a section 1120 push(@{$sect->{'members'}}, 1121 { 'name' => $1, 1122 'file' => $file, 1123 'line' => $lnum }); 1124 $sect->{'eline'} = $lnum; 1125 } 1126 elsif (/^\s*\!include\s+(\S+)/) { 1127 # Including sections from a file 1128 foreach my $file (glob($1)) { 1129 push(@rv, &parse_mysql_config($file)); 1130 } 1131 } 1132 elsif (/^\s*\!includedir\s+(\S+)/) { 1133 # Including sections from files in a directory 1134 my $dir = $1; 1135 $dir =~ s/\/$//; 1136 opendir(DIR, $dir); 1137 my @files = map { $dir."/".$_ } readdir(DIR); 1138 closedir(DIR); 1139 foreach my $file (@files) { 1140 push(@rv, &parse_mysql_config($file)); 1141 } 1142 } 1143 $lnum++; 1144 } 1145return @rv; 1146} 1147 1148# find(name, &conf) 1149sub find 1150{ 1151local ($name, $conf) = @_; 1152local @rv = grep { lc($_->{'name'}) eq lc($name) } @$conf; 1153return wantarray ? @rv : $rv[0]; 1154} 1155 1156# find_value(name, &conf) 1157sub find_value 1158{ 1159local ($name, $conf) = @_; 1160local @rv = map { $_->{'value'} } &find($name, $conf); 1161return wantarray ? @rv : $rv[0]; 1162} 1163 1164# save_directive(&conf, §ion, name, &values) 1165# Updates one or multiple lines in a my.cnf section 1166sub save_directive 1167{ 1168local ($conf, $sect, $name, $values) = @_; 1169local @old = &find($name, $sect->{'members'}); 1170local $file = @old ? $old[0]->{'file'} : 1171 $sect ? $sect->{'file'} : $config{'my_cnf'}; 1172local $lref = &read_file_lines($file); 1173 1174for(my $i=0; $i<@old || $i<@$values; $i++) { 1175 local $old = $old[$i]; 1176 local $line = $values->[$i] eq "" ? $name : 1177 "$name = $values->[$i]"; 1178 if ($old && defined($values->[$i])) { 1179 # Updating 1180 $lref->[$old->{'line'}] = $line; 1181 $old->{'value'} = $values->[$i]; 1182 } 1183 elsif (!$old && defined($values->[$i])) { 1184 # Adding 1185 splice(@$lref, $sect->{'eline'}+1, 0, $line); 1186 &renumber($conf, $sect->{'eline'}+1, 1, $file); 1187 push(@{$sect->{'members'}}, 1188 { 'name' => $name, 1189 'value' => $values->[$i], 1190 'line' => $sect->{'eline'}+1 }); 1191 } 1192 elsif ($old && !defined($values->[$i])) { 1193 # Deleting 1194 splice(@$lref, $old->{'line'}, 1); 1195 &renumber($conf, $old->{'line'}, -1, $file); 1196 @{$sect->{'members'}} = grep { $_ ne $old } 1197 @{$sect->{'members'}}; 1198 } 1199 } 1200} 1201 1202sub renumber 1203{ 1204local ($conf, $line, $offset, $file) = @_; 1205foreach my $sect (@$conf) { 1206 next if ($sect->{'file'} ne $file); 1207 $sect->{'line'} += $offset if ($sect->{'line'} >= $line); 1208 $sect->{'eline'} += $offset if ($sect->{'eline'} >= $line); 1209 foreach my $m (@{$sect->{'members'}}) { 1210 $m->{'line'} += $offset if ($m->{'line'} >= $line); 1211 } 1212 } 1213} 1214 1215# parse_set_variables(value, ...) 1216# Returns a hash of variable mappings 1217sub parse_set_variables 1218{ 1219local %vars; 1220foreach my $v (@_) { 1221 if ($v =~ /^(\S+)=(\S+)$/) { 1222 $vars{$1} = $2; 1223 } 1224 } 1225return %vars; 1226} 1227 1228sub mysql_size_input 1229{ 1230local ($name, $value) = @_; 1231local $units; 1232if ($value =~ /^(\d+)([a-z])$/i) { 1233 $value = $1; 1234 $units = $2; 1235 } 1236$units = "" if ($units eq "b"); 1237return &ui_textbox($name, $value, 8)."\n". 1238 &ui_select($name."_units", $units, 1239 [ [ "", "bytes" ], [ "K", "kB" ], 1240 [ "M", "MB" ], [ "G", "GB" ] ]); 1241} 1242 1243# list_indexes(db) 1244# Returns the names of all indexes in some database 1245sub list_indexes 1246{ 1247local ($db) = @_; 1248local (@rv, $r); 1249foreach my $table (&list_tables($db)) { 1250 local $s = &execute_sql_safe($db, "show index from "."estr($table)); 1251 local (%tp, $i); 1252 for($i=0; $i<@{$s->{'titles'}}; $i++) { 1253 $tp{lc($s->{'titles'}->[$i])} = $i; 1254 } 1255 foreach $r (@{$s->{'data'}}) { 1256 if ($r->[$tp{'key_name'}] ne 'PRIMARY') { 1257 push(@rv, $r->[$tp{'key_name'}]); 1258 } 1259 } 1260 } 1261return &unique(@rv); 1262} 1263 1264# index_structure(db, indexname) 1265# Returns information on an index 1266sub index_structure 1267{ 1268local ($db, $index) = @_; 1269local (@rv, $r); 1270local $info; 1271foreach my $table (&list_tables($db)) { 1272 local $s = &execute_sql_safe($db, "show index from "."estr($table)); 1273 local (%tp, $i); 1274 for($i=0; $i<@{$s->{'titles'}}; $i++) { 1275 $tp{lc($s->{'titles'}->[$i])} = $i; 1276 } 1277 foreach $r (@{$s->{'data'}}) { 1278 if ($r->[$tp{'key_name'}] eq $index) { 1279 # Found some info 1280 $info->{'table'} = $r->[$tp{'table'}]; 1281 $info->{'name'} = $index; 1282 $info->{'type'} = lc($r->[$tp{'index_type'}]) || 1283 lc($r->[$tp{'comment'}]); 1284 push(@{$info->{'cols'}}, $r->[$tp{'column_name'}]); 1285 } 1286 } 1287 } 1288return $info; 1289} 1290 1291# list_views(db) 1292# Returns the names of all views in some database 1293sub list_views 1294{ 1295local ($db) = @_; 1296local @rv; 1297local $d = &execute_sql($db, "select table_schema,table_name from information_schema.views where table_schema = ?", $db); 1298foreach $r (@{$d->{'data'}}) { 1299 push(@rv, $r->[1]); 1300 } 1301return @rv; 1302} 1303 1304# view_structure(db, viewname) 1305# Returns information about a view 1306sub view_structure 1307{ 1308local ($db, $view) = @_; 1309local $info = { 'name' => $view }; 1310local $d = &execute_sql($db, "show create view $view"); 1311local $c = $d->{'data'}->[0]->[1]; 1312if ($c =~ /algorithm\s*=\s*(\S+)/i) { 1313 $info->{'algorithm'} = lc($1); 1314 } 1315if ($c =~ /definer\s*=\s*`(\S+)`\@`(\S+)`/i) { 1316 $info->{'definer'} = "$1\@$2"; 1317 } 1318elsif ($c =~ /definer\s*=\s*(\S+)/i) { 1319 $info->{'definer'} = $1; 1320 } 1321if ($c =~ /sql\s+security\s+(\S+)/i) { 1322 $info->{'security'} = lc($1); 1323 } 1324if ($c =~ s/\s+with\s+(cascaded|local)\s+check\s+option//i) { 1325 $info->{'check'} = lc($1); 1326 } 1327if ($c =~ /view\s+(`\S+`|\S+)\s+as\s+(.*)/i) { 1328 $info->{'query'} = $2; 1329 } 1330return $info; 1331} 1332 1333# list_character_sets([db]) 1334# Returns a list of supported character sets. Each row is an array ref of 1335# a code and name 1336sub list_character_sets 1337{ 1338local @rv; 1339local $db = $_[0] || $master_db; 1340if (&compare_version_numbers(&get_remote_mysql_version(), "4.1") < 0) { 1341 local $d = &execute_sql($db, "show variables like 'character_sets'"); 1342 @rv = map { [ $_, $_ ] } split(/\s+/, $d->{'data'}->[0]->[1]); 1343 } 1344else { 1345 local $d = &execute_sql($db, "show character set"); 1346 @rv = map { [ $_->[0], "$_->[1] ($_->[0])" ] } @{$d->{'data'}}; 1347 } 1348return sort { lc($a->[1]) cmp lc($b->[1]) } @rv; 1349} 1350 1351# list_collation_orders([db]) 1352# Returns a list of supported collation orders. Each row is an array ref of 1353# a code and character set it can work with. 1354sub list_collation_orders 1355{ 1356local @rv; 1357local $db = $_[0] || $master_db; 1358if (&compare_version_numbers(&get_remote_mysql_version(), "5") >= 0) { 1359 local $d = &execute_sql($db, "show collation"); 1360 @rv = map { [ $_->[0], $_->[1] ] } @{$d->{'data'}}; 1361 } 1362return sort { lc($a->[0]) cmp lc($b->[0]) } @rv; 1363} 1364 1365# list_system_variables() 1366# Returns a list of all system variables, and their default values 1367sub list_system_variables 1368{ 1369local $mysqld = $config{'mysqld'}; 1370if (!$mysqld) { 1371 # Mysqld path not in config .. guess from mysql path 1372 $mysqld = $config{'mysql'}; 1373 $mysqld =~ s/mysql$/mysqld/g; 1374 $mysqld =~ s/bin/sbin/g; 1375 if (!-x $mysqld) { 1376 $mysqld = $config{'mysql'}; 1377 $mysqld =~ s/mysql$/mysqld/g; 1378 $mysqld =~ s/bin/libexec/g; 1379 if (!-x $mysqld) { 1380 # Look in Webmin path 1381 &error($mysqld); 1382 $mysqld = &has_command("mysqld"); 1383 } 1384 } 1385 } 1386return ( ) if (!$mysqld); 1387 1388# Read supported variables 1389local @rv; 1390&open_execute_command(MYSQLD, "$mysqld --verbose --help", 1, 1); 1391while(<MYSQLD>) { 1392 s/\r|\n//g; 1393 if (/^(\S+)\s+current\s+value:\s+(\S*)/) { 1394 push(@rv, [ $1, $2 ]); 1395 } 1396 elsif (/^\-\-\-\-/) { 1397 $started = 1; 1398 } 1399 elsif ($started && /^(\S+)\s+(.*)/) { 1400 push(@rv, [ $1, $2 eq "(No default value)" ? undef : $2 ]); 1401 } 1402 } 1403close(MYSQL); 1404return @rv; 1405} 1406 1407# list_compatible_formats() 1408# Returns a list of two-element arrays, containing compatibility format 1409# codes and descriptions 1410sub list_compatible_formats 1411{ 1412return map { [ $_, $text{'compat_'.$_} ] } 1413 ( "ansi", "mysql323", "mysql40", "postgresql", "oracle", "mssql", 1414 "db2", "maxdb" ); 1415} 1416 1417# list_compatible_options() 1418# Returns a list of two-element arrays, containing compatibility options 1419sub list_compatible_options 1420{ 1421return map { [ $_, $text{'compat_'.$_} ] } 1422 ( "no_key_options", "no_table_options", "no_field_options" ); 1423} 1424 1425# compression_format(file) 1426# Returns 0 if uncompressed, 1 for gzip, 2 for compress, 3 for bzip2 or 1427# 4 for zip 1428sub compression_format 1429{ 1430open(BACKUP, "<".$_[0]); 1431local $two; 1432read(BACKUP, $two, 2); 1433close(BACKUP); 1434return $two eq "\037\213" ? 1 : 1435 $two eq "\037\235" ? 2 : 1436 $two eq "PK" ? 4 : 1437 $two eq "BZ" ? 3 : 0; 1438} 1439 1440# backup_database(db, dest-file, compress-mode, drop-flag, where-clause, 1441# charset, &compatible, &only-tables, run-as-user, 1442# single-transaction-flag, quick-flag, force-flag, parameters) 1443# Backs up a database to the given file, optionally with compression. Returns 1444# undef on success, or an error message on failure. 1445sub backup_database 1446{ 1447local ($db, $file, $compress, $drop, $where, $charset, $compatible, 1448 $tables, $user, $single, $quick, $force, $parameters) = @_; 1449if ($compress == 0) { 1450 $writer = "cat >".quotemeta($file); 1451 } 1452elsif ($compress == 1) { 1453 $writer = "gzip -c >".quotemeta($file); 1454 } 1455elsif ($compress == 2) { 1456 $writer = "bzip2 -c >".quotemeta($file); 1457 } 1458local $dropsql = $drop ? "--add-drop-table" : ""; 1459local $singlesql = $single ? "--single-transaction" : ""; 1460local $forcesql = $force ? "--force" : ""; 1461local $quicksql = $quick ? "--quick" : ""; 1462local $parameterssql = $parameters ? quotemeta($parameters) : ""; 1463local $wheresql = $where ? "--where=".quotemeta($in{'where'}) : ""; 1464local $charsetsql = $charset ? 1465 "--default-character-set=".quotemeta($charset) : ""; 1466local $compatiblesql = @$compatible ? 1467 "--compatible=".join(",", @$compatible) : ""; 1468local $quotingsql = &supports_quoting() ? "--quote-names" : ""; 1469local $routinessql = &supports_routines() ? "--routines" : ""; 1470local $tablessql = join(" ", map { quotemeta($_) } @$tables); 1471local $eventssql = &supports_mysqldump_events() ? "--events" : ""; 1472local $gtidsql = ""; 1473eval { 1474 $main::error_must_die = 1; 1475 local $d = &execute_sql($master_db, "show variables like 'gtid_mode'"); 1476 if (@{$d->{'data'}} && uc($d->{'data'}->[0]->[1]) eq 'ON' && 1477 &compare_version_numbers($mysql_version, "5.6") >= 0) { 1478 # Add flag to support GTIDs 1479 $gtidsql = "--set-gtid-purged=OFF"; 1480 } 1481 }; 1482if ($user && $user ne "root") { 1483 # Actual writing of output is done as another user 1484 $writer = &command_as_user($user, 0, $writer); 1485 } 1486local $cmd = "$config{'mysqldump'} $authstr $dropsql $singlesql $forcesql $quicksql $parameterssql $wheresql $charsetsql $compatiblesql $quotingsql $routinessql ".quotemeta($db)." $tablessql $eventssql $gtidsql | $writer"; 1487if (&shell_is_bash()) { 1488 $cmd = "set -o pipefail ; $cmd"; 1489 } 1490local $out = &backquote_logged("($cmd) 2>&1"); 1491if ($? || !-s $file) { 1492 return $out; 1493 } 1494return undef; 1495} 1496 1497# delete_database_backup_job(db) 1498# If there is a backup scheduled for some database, remove it 1499sub delete_database_backup_job 1500{ 1501my ($db) = @_; 1502&foreign_require("cron"); 1503my @jobs = &cron::list_cron_jobs(); 1504my $cmd = "$cron_cmd $db"; 1505my ($job) = grep { $_->{'command'} eq $cmd } @jobs; 1506if ($job) { 1507 &lock_file(&cron::cron_file($job)); 1508 &cron::delete_cron_job($job); 1509 &unlock_file(&cron::cron_file($job)); 1510 } 1511} 1512 1513# get_all_mysqld_files() 1514# Returns all config files used by MySQLd 1515sub get_all_mysqld_files 1516{ 1517my $conf = &get_mysql_config(); 1518return &unique(map { $_->{'file'} } @$conf); 1519} 1520 1521# get_change_pass_sql(unescaped_plaintext_password, user, host) 1522# Get the right query for changing user password 1523sub get_change_pass_sql 1524{ 1525my ($unescaped_plainpass, $user, $host) = @_; 1526my $plugin = &get_mysql_plugin(1); 1527my $escaped_pass = &escapestr($unescaped_plainpass); 1528my $sql; 1529my ($ver, $variant) = &get_remote_mysql_variant(); 1530my $mysql_mariadb_with_auth_string = 1531 $variant eq "mariadb" && &compare_version_numbers($ver, "10.2") >= 0 || 1532 $variant eq "mysql" && &compare_version_numbers($ver, "5.7.6") >= 0; 1533if ($mysql_mariadb_with_auth_string && $unescaped_plainpass) { 1534 $sql = "alter user '$user'\@'$host' identified $plugin by '$escaped_pass'"; 1535 } 1536else { 1537 $sql = "set password for '".$user."'\@'".$host."' = ". 1538 "$password_func('$escaped_pass')"; 1539 } 1540return $sql; 1541} 1542 1543# get_mysql_plugin(query_ready) 1544# Returns the name of the default plugin used by MySQL/MariaDB 1545sub get_mysql_plugin 1546{ 1547my ($query) = @_; 1548my @plugin = &execute_sql($master_db, 1549 "show variables LIKE '%default_authentication_plugin%'"); 1550my $plugin = $plugin[0]->{'data'}->[0]->[1]; 1551if ($plugin && $query) { 1552 $plugin = " with $plugin "; 1553 } 1554return $plugin; 1555} 1556 1557# perms_column_to_privilege_map(col) 1558# Returns a privilege name based on given column for MySQL 8+ and MariaDB 10.4 1559sub perms_column_to_privilege_map 1560{ 1561my ($column) = @_; 1562my %priv = ( 1563 'Alter_priv', 'alter', 1564 'Alter_routine_priv', 'alter routine', 1565 'Create_priv', 'create', 1566 'Create_routine_priv', 'create routine', 1567 'Create_tablespace_priv', 'create tablespace', 1568 'Create_tmp_table_priv', 'create temporary tables', 1569 'Create_user_priv', 'create user', 1570 'Create_view_priv', 'create view', 1571 'Delete_priv', 'delete', 1572 'Drop_priv', 'drop', 1573 'Event_priv', 'event', 1574 'Execute_priv', 'execute', 1575 'File_priv', 'file', 1576 'Grant_priv', 'grant option', 1577 'Index_priv', 'index', 1578 'Insert_priv', 'insert', 1579 'Lock_tables_priv', 'lock tables', 1580 'Process_priv', 'process', 1581 'References_priv', 'references', 1582 'Reload_priv', 'reload', 1583 'Repl_client_priv', 'replication client', 1584 'Repl_slave_priv', 'replication slave', 1585 'Select_priv', 'select', 1586 'Show_db_priv', 'show databases', 1587 'Show_view_priv', 'show view', 1588 'Shutdown_priv', 'shutdown', 1589 'Super_priv', 'super', 1590 'Trigger_priv', 'trigger', 1591 'Update_priv', 'update', 1592 1593 'Delete_history_priv', 'delete history', 1594 1595 # 'Create_role_priv', 'create role', 1596 # 'Drop_role_priv', 'drop role', 1597 # 'proxies_priv', 'proxy', 1598 1599 ); 1600return defined($column) ? $priv{$column} : \%priv; 1601} 1602 1603# update_privileges(\%sconfig) 1604# Update user privileges 1605sub update_privileges 1606{ 1607my ($sc) = @_; 1608 1609my $user = $sc->{'user'}; 1610my $host = $sc->{'host'}; 1611my $perms = $sc->{'perms'}; 1612my $pfields = $sc->{'pfields'}; 1613 1614my ($ver, $variant) = &get_remote_mysql_variant(); 1615 1616if ($variant eq "mariadb" && &compare_version_numbers($ver, "10.4") >= 0) { 1617 # Assign permissions 1618 my $col_to_priv_map = &perms_column_to_privilege_map(); 1619 foreach my $grant (keys %{ $perms }) { 1620 my $grant_priv = &perms_column_to_privilege_map($grant); 1621 &execute_sql_logged($mysql::master_db, "grant $grant_priv on *.* to '$user'\@'$host'"); 1622 delete $col_to_priv_map->{$grant}; 1623 } 1624 foreach my $revoke_priv (values %{ $col_to_priv_map }) { 1625 &execute_sql_logged($mysql::master_db, "revoke $revoke_priv on *.* from '$user'\@'$host'"); 1626 } 1627 } 1628else { 1629 $sql = "update user set ". 1630 join(", ",map { "$_ = ?" } @{ $pfields }). 1631 " where host = ? and user = ?"; 1632 &execute_sql_logged($master_db, $sql, 1633 (map { $perms{$_} ? 'Y' : 'N' } @{ $pfields }), 1634 $host, $user); 1635 } 1636&execute_sql_logged($master_db, 'flush privileges'); 1637} 1638 1639 1640# rename_user(\%sconfig) 1641# Rename SQL user 1642sub rename_user 1643{ 1644my ($sc) = @_; 1645my $user = $sc->{'user'}; 1646my $olduser = $sc->{'olduser'}; 1647my $host = $sc->{'host'}; 1648my $oldhost = $sc->{'oldhost'}; 1649 1650my ($ver, $variant) = &get_remote_mysql_variant(); 1651my $sql; 1652if ($variant eq "mariadb" && &compare_version_numbers($ver, "10.4") >= 0) { 1653 &execute_sql_logged($master_db, "rename user '$olduser'\@'$oldhost' to '$user'\@'$host'"); 1654 } 1655else { 1656 &execute_sql_logged($master_db, 1657 "update user set host = ?, user = ? where host = ? and user = ?", 1658 $host, $user, 1659 $oldhost, $olduser); 1660 } 1661&update_config_credentials({ 1662 'user', $user, 1663 'olduser', $olduser, 1664 }); 1665&execute_sql_logged($master_db, 'flush privileges'); 1666} 1667 1668# create_user(\%sconfig) 1669# Create new SQL user 1670sub create_user 1671{ 1672my ($sc) = @_; 1673my $user = $sc->{'user'}; 1674my $pass = $sc->{'pass'}; 1675my $host = $sc->{'host'}; 1676my $perms = $sc->{'perms'}; 1677my $pfields = $sc->{'pfields'}; 1678my $ssl_field_names = $sc->{'ssl_field_names'}; 1679my $ssl_field_values = $sc->{'ssl_field_values'}; 1680my $other_field_names = $sc->{'other_field_names'}; 1681my $other_field_values = $sc->{'other_field_values'}; 1682 1683my ($ver, $variant) = &get_remote_mysql_variant(); 1684my $plugin = &get_mysql_plugin(1); 1685 1686if ($variant eq "mariadb" && &compare_version_numbers($ver, "10.4") >= 0) { 1687 my $sql = "create user '$user'\@'$host' identified $plugin by ". 1688 "'".&escapestr($pass)."'"; 1689 &execute_sql_logged($master_db, $sql); 1690 &execute_sql_logged($master_db, 'flush privileges'); 1691 1692 # Update existing user privileges 1693 &update_privileges({( 1694 'user', $user, 1695 'host', $host, 1696 'perms', $perms, 1697 'pfields', $pfields 1698 )}); 1699 } 1700else { 1701 my $sql = "insert into user (host, user, ". 1702 join(", ", @{ $pfields }, @{ $ssl_field_names }, 1703 @{ $other_field_names }). 1704 ") values (?, ?, ". 1705 join(", ", map { "?" } (@{ $pfields }, @{ $ssl_field_names }, 1706 @{ $other_field_names })).")"; 1707 &execute_sql_logged($master_db, $sql, 1708 $host, $user, 1709 (map { $perms->{$_} ? 'Y' : 'N' } @{ $pfields }), 1710 @{ $ssl_field_values }, @{ $other_field_values }); 1711 &execute_sql_logged($master_db, 'flush privileges'); 1712 } 1713} 1714 1715# change_user_password(plainpass, user, host) 1716# Change user password 1717sub change_user_password 1718{ 1719my ($plainpass, $user, $host) = @_; 1720 1721my ($ver, $variant) = &get_remote_mysql_variant(); 1722my $plugin = &get_mysql_plugin(1); 1723my $lock_supported = $variant eq "mysql" && &compare_version_numbers($ver, "8.0.19"); 1724my $mysql_mariadb_with_auth_string = 1725 $variant eq "mariadb" && &compare_version_numbers($ver, "10.4") >= 0 || 1726 $variant eq "mysql" && &compare_version_numbers($ver, "5.7.6") >= 0; 1727 1728my $sql; 1729my $pass = &escapestr($plainpass); 1730$host ||= '%'; 1731my $lock = !defined($plainpass); 1732if ($lock) { 1733 $pass = sprintf("%x", rand 16) for 1..30; 1734 } 1735if ($mysql_mariadb_with_auth_string) { 1736 my $sp = "identified $plugin by '".$pass."'"; 1737 if ($lock_supported) { 1738 $sp = $lock ? "account lock" : "$sp account unlock"; 1739 } 1740 $sql = "alter user '$user'\@'$host' $sp"; 1741 &execute_sql_logged($master_db, $sql); 1742 } 1743else { 1744 $sql = &get_change_pass_sql($plainpass, $user, $host); 1745 &execute_sql_logged($master_db, $sql); 1746 } 1747 1748# Update module password when needed 1749&update_config_credentials({ 1750 'user', $user, 1751 'olduser', $user, 1752 'pass', $plainpass, 1753 }); 1754&execute_sql_logged($master_db, 'flush privileges'); 1755} 1756 1757# Update Webmin module login and pass 1758sub update_config_credentials 1759{ 1760return if($access{'user'}); 1761my ($c) = @_; 1762my $conf_user = $config{'login'} || "root"; 1763return if($c->{'olduser'} ne $conf_user); 1764return if(!$c->{'user'}); 1765 1766$config{'login'} = $c->{'user'}; 1767$mysql_login = $c->{'user'}; 1768if (defined($c->{'pass'})) { 1769 $config{'pass'} = $c->{'pass'}; 1770 $mysql_pass = $c->{'pass'}; 1771 } 1772&lock_file($module_config_file); 1773&save_module_config(); 1774&unlock_file($module_config_file); 1775&stop_mysql(); 1776&start_mysql(); 1777} 1778 1779# force_set_mysql_admin_pass(user, pass) 1780# Forcibly change MySQL admin password, if lost or forgotten 1781sub force_set_mysql_admin_pass 1782{ 1783my ($user, $pass) = @_; 1784&error_setup($text{'mysqlpass_err'}); 1785&foreign_require("proc"); 1786 1787# Find the mysqld_safe command 1788my $safe = &has_command("mysqld_safe"); 1789if (!$safe) { 1790 &error(&text('mysqlpass_esafecmd', "<tt>mysqld_safe</tt>")); 1791 } 1792 1793# Shut down server if running 1794if (&is_mysql_running()) { 1795 my $err = &stop_mysql(); 1796 if ($err) { 1797 &error(&text('mysqlpass_esafecmdeshutdown', $err)); 1798 } 1799 } 1800 1801# Start up with skip-grants flag 1802my $cmd = $safe." --skip-grant-tables"; 1803 1804# Running with `mysqld_safe` - when called, command doesn't create "mysqld" directory under 1805# "/var/run" eventually resulting in DBI connect failed error on all MySQL versions 1806my $ver = &get_mysql_version(); 1807if ($ver !~ /mariadb/i) { 1808 my $mysockdir = '/var/run/mysqld'; 1809 my $myusergrp = 'mysql'; 1810 my $myconf = &get_mysql_config(); 1811 if ($myconf) { 1812 my ($mysqld) = grep { $_->{'name'} eq 'mysqld' } @$myconf; 1813 if ($mysqld) { 1814 my $members = $mysqld->{'members'}; 1815 1816 # Look for user 1817 my $myusergrp_ = &find_value("user", $members); 1818 if ($myusergrp_) { 1819 $myusergrp = $myusergrp_; 1820 } 1821 1822 # Look for socket 1823 my $mysockdir_ = &find_value("socket", $members); 1824 if ($mysockdir_) { 1825 $mysockdir = $mysockdir_; 1826 $mysockdir =~ s/^(.+)\/([^\/]+)$/$1/; 1827 } 1828 } 1829 } 1830 $cmd = "mkdir -p $mysockdir && chown $myusergrp:$myusergrp $mysockdir && $cmd"; 1831 } 1832my ($pty, $pid) = &proc::pty_process_exec($cmd, 0, 0); 1833sleep(5); 1834if (!$pid || !kill(0, $pid)) { 1835 my $err = <$pty>; 1836 &error(&text('mysqlpass_esafe', $err)); 1837 } 1838 1839# Update password by running command directly 1840my $cmd = $config{'mysql'} || 'mysql'; 1841my $sql = &get_change_pass_sql($pass, $user, 'localhost'); 1842my $out = &backquote_command("$cmd -D $master_db -e ". 1843 quotemeta("flush privileges; $sql")." 2>&1 </dev/null"); 1844if ($?) { 1845 $out =~ s/\n/ /gm; 1846 &error(&text('mysqlpass_echange', "$out")); 1847 } 1848else { 1849 1850 # Update root password now for other 1851 # hosts, using regular database connection 1852 my $d = &execute_sql_safe($master_db, 1853 "select host from user where user = ?", $user); 1854 @hosts = map { $_->[0] ne 'localhost' } @{$d->{'data'}}; 1855 foreach my $host (@hosts) { 1856 $sql = get_change_pass_sql($pass, $user, $host); 1857 eval { 1858 local $main::error_must_die = 1; 1859 &execute_sql_logged($master_db, 'flush privileges'); 1860 &execute_sql_logged($master_db, $sql); 1861 &execute_sql_logged($master_db, 'flush privileges'); 1862 sleep 1; 1863 }; 1864 } 1865 } 1866 1867# Shut down again, with the mysqladmin command 1868my $mysql_shutdown = $config{'mysqladmin'} || 'mysqladmin'; 1869my $out = &backquote_logged("$mysql_shutdown shutdown 2>&1 </dev/null"); 1870if ($?) { 1871 $out =~ s/\n/ /gm; 1872 &error(&text('mysqlpass_eshutdown', $out)); 1873 } 1874 1875# Finally, re-start in normal mode 1876my $err = &start_mysql(); 1877if ($err) { 1878 &error(&text('mysqlpass_estartup', $err)); 1879 } 1880&error_setup($text{'login_err'}); 1881} 1882 1883# create_module_info_overrides() 1884# Update the overrides file used for module.info to reflect MariaDB 1885sub create_module_info_overrides 1886{ 1887my %info = &get_module_info(&get_module_name(), 0, 1); 1888my %overs; 1889if ($mysql_version =~ /mariadb/i) { 1890 $overs{'desc'} = $info{'original_desc'} || $info{'desc'}; 1891 $overs{'desc'} =~ s/MySQL/MariaDB/g; 1892 } 1893&write_file("$module_config_directory/module.info.override", \%overs); 1894} 1895 18961; 1897 1898