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 ".&quotestr($_[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'}."&nbsp;".
603			    &ui_select("dbs", $_[0], \@dbs, 1, 0, 0, 0, $js1) ],
604			  [ 0, $text{'host_otherdb'}."&nbsp;".
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 &quotestr($_[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		     &quotestr($in{"field_$i"}), $in{"type_$i"},$in{"size_$i"});
944		}
945	else {
946		push(@fields, sprintf "%s %s",
947			&quotestr($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 ".&quotestr($_[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 ".&quotestr($_[1])." add primary key (".
976		    join(",", map { &quotestr($_) } @pri).")");
977	}
978if (@auto) {
979	# Make field auto-increment
980	push(@sql, "alter table ".&quotestr($_[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, &section, 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 ".&quotestr($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 ".&quotestr($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