From 08679016bd83d2b10bfe47f9e9d45bcae1e9981a Mon Sep 17 00:00:00 2001 From: Jeff Lawson Date: Wed, 13 Mar 2024 15:00:00 -0500 Subject: [PATCH] Bug 1592129: [MYSQL8] The groups table in Bugzilla conflicts with reserved keyword in MySQL 8.x (#153) * [MYSQL8] The groups table in Bugzilla conflicts with reserved keyword in MySQL 8.x --- Bugzilla/Bug.pm | 2 +- Bugzilla/DB.pm | 15 +++++-- Bugzilla/DB/Mysql.pm | 37 +++++++++++++---- Bugzilla/DB/Schema.pm | 79 +++++++++++++++++++++++++++--------- Bugzilla/DB/Schema/Mysql.pm | 54 +++++++++++++++--------- Bugzilla/DB/Schema/Sqlite.pm | 7 +++- Bugzilla/Group.pm | 3 +- Bugzilla/Install/DB.pm | 52 +++++++++++++----------- Bugzilla/Migrate.pm | 9 +++- Bugzilla/Object.pm | 38 ++++++++++------- Bugzilla/Product.pm | 26 ++++++++---- Bugzilla/Search.pm | 7 ++-- Bugzilla/User.pm | 4 +- editgroups.cgi | 5 ++- editproducts.cgi | 4 +- editusers.cgi | 18 ++++---- sanitycheck.cgi | 30 +++++++------- userprefs.cgi | 11 ++--- whine.pl | 3 +- 19 files changed, 264 insertions(+), 140 deletions(-) diff --git a/Bugzilla/Bug.pm b/Bugzilla/Bug.pm index 31138b67e7..d34a5cc13c 100644 --- a/Bugzilla/Bug.pm +++ b/Bugzilla/Bug.pm @@ -3984,7 +3984,7 @@ sub groups { . " THEN 1 ELSE 0 END," . " CASE WHEN groups.id IN($grouplist) THEN 1 ELSE 0 END," . " isactive, membercontrol, othercontrol" - . " FROM groups" + . " FROM " . $dbh->quote_identifier('groups') . " LEFT JOIN bug_group_map" . " ON bug_group_map.group_id = groups.id" . " AND bug_id = ?" diff --git a/Bugzilla/DB.pm b/Bugzilla/DB.pm index 0a83bcf268..ecd1560ed0 100644 --- a/Bugzilla/DB.pm +++ b/Bugzilla/DB.pm @@ -1559,7 +1559,11 @@ sub _check_references { # reserved words. my $bad_values = $self->selectcol_arrayref( "SELECT DISTINCT tabl.$column - FROM $table AS tabl LEFT JOIN $foreign_table AS forn + FROM " + . $self->quote_identifier($table) + . " AS tabl LEFT JOIN " + . $self->quote_identifier($foreign_table) + . " AS forn ON tabl.$column = forn.$foreign_column WHERE forn.$foreign_column IS NULL AND tabl.$column IS NOT NULL" @@ -1569,7 +1573,10 @@ sub _check_references { my $delete_action = $fk->{DELETE} || ''; if ($delete_action eq 'CASCADE') { $self->do( - "DELETE FROM $table WHERE $column IN (" . join(',', ('?') x @$bad_values) . ")", + "DELETE FROM " + . $self->quote_identifier($table) + . " WHERE $column IN (" + . join(',', ('?') x @$bad_values) . ")", undef, @$bad_values ); if (Bugzilla->usage_mode == USAGE_MODE_CMDLINE) { @@ -1590,7 +1597,9 @@ sub _check_references { } elsif ($delete_action eq 'SET NULL') { $self->do( - "UPDATE $table SET $column = NULL + "UPDATE " + . $self->quote_identifier($table) + . " SET $column = NULL WHERE $column IN (" . join(',', ('?') x @$bad_values) . ")", undef, @$bad_values ); diff --git a/Bugzilla/DB/Mysql.pm b/Bugzilla/DB/Mysql.pm index 625b825394..e6422389c2 100644 --- a/Bugzilla/DB/Mysql.pm +++ b/Bugzilla/DB/Mysql.pm @@ -338,10 +338,17 @@ sub bz_setup_database { if ($self->utf8_charset eq 'utf8mb4') { my %global = map {@$_} @{$self->selectall_arrayref(q(SHOW GLOBAL VARIABLES LIKE 'innodb_%'))}; + + # In versions of MySQL > 8, the default value for innodb_file_format is Barracuda + # and the setting was deprecated. Also innodb_file_per_table also now defaults + # to ON. innodb_large_prefix has also been removed in newer MySQL versions. my $utf8mb4_supported - = $global{innodb_file_format} eq 'Barracuda' - && $global{innodb_file_per_table} eq 'ON' - && $global{innodb_large_prefix} eq 'ON'; + = (!exists $global{innodb_file_format} + || $global{innodb_file_format} eq 'Barracuda') + && (!exists $global{innodb_file_per_table} + || $global{innodb_file_per_table} eq 'ON') + && (!exists $global{innodb_large_prefix} + || $global{innodb_large_prefix} eq 'ON'); die install_string('mysql_innodb_settings') unless $utf8mb4_supported; @@ -359,7 +366,11 @@ sub bz_setup_database { 'mysql_row_format_conversion', {table => $table, format => $new_row_format} ), "\n"; - $self->do(sprintf 'ALTER TABLE %s ROW_FORMAT=%s', $table, $new_row_format); + $self->do( + sprintf 'ALTER TABLE %s ROW_FORMAT=%s', + $self->quote_identifier($table), + $new_row_format + ); } } } @@ -402,7 +413,7 @@ sub bz_setup_database { " most tables.\nConverting tables to InnoDB:\n"; foreach my $table (@$myisam_tables) { print "Converting table $table... "; - $self->do("ALTER TABLE $table ENGINE = InnoDB"); + $self->do('ALTER TABLE ' . $self->quote_identifier($table) . ' ENGINE = InnoDB'); print "done.\n"; } } @@ -770,8 +781,13 @@ sub bz_setup_database { } print "Converting the $table table to UTF-8...\n"; - my $bin = "ALTER TABLE $table " . join(', ', @binary_sql); - my $utf = "ALTER TABLE $table " + my $bin + = 'ALTER TABLE ' + . $self->quote_identifier($table) . ' ' + . join(', ', @binary_sql); + my $utf + = 'ALTER TABLE ' + . $self->quote_identifier($table) . ' ' . join(', ', @utf8_sql, "DEFAULT CHARACTER SET $charset COLLATE $collate"); $self->do($bin); $self->do($utf); @@ -782,7 +798,9 @@ sub bz_setup_database { } } else { - $self->do("ALTER TABLE $table DEFAULT CHARACTER SET $charset COLLATE $collate"); + $self->do('ALTER TABLE ' + . $self->quote_identifier($table) + . " DEFAULT CHARACTER SET $charset COLLATE $collate"); } } # foreach my $table (@tables) @@ -870,7 +888,8 @@ sub _fix_defaults { print "Fixing defaults...\n"; foreach my $table (reverse sort keys %fix_columns) { my @alters = map("ALTER COLUMN $_ DROP DEFAULT", @{$fix_columns{$table}}); - my $sql = "ALTER TABLE $table " . join(',', @alters); + my $sql + = 'ALTER TABLE ' . $self->quote_identifier($table) . ' ' . join(',', @alters); $self->do($sql); } } diff --git a/Bugzilla/DB/Schema.pm b/Bugzilla/DB/Schema.pm index 6245bb166c..a88793d94b 100644 --- a/Bugzilla/DB/Schema.pm +++ b/Bugzilla/DB/Schema.pm @@ -2014,7 +2014,9 @@ is undefined. return "\n CONSTRAINT $fk_name FOREIGN KEY ($column)\n" - . " REFERENCES $to_table($to_column)\n" + . " REFERENCES " + . Bugzilla->dbh->quote_identifier($to_table) + . "($to_column)\n" . " ON UPDATE $update ON DELETE $delete"; } @@ -2049,13 +2051,18 @@ sub get_add_fks_sql { my @add = $self->_column_fks_to_ddl($table, $column_fks); my @sql; + my $dbh = Bugzilla->dbh; if ($self->MULTIPLE_FKS_IN_ALTER) { - my $alter = "ALTER TABLE $table ADD " . join(', ADD ', @add); + my $alter + = "ALTER TABLE " + . $dbh->quote_identifier($table) . " ADD " + . join(', ADD ', @add); push(@sql, $alter); } else { foreach my $fk_string (@add) { - push(@sql, "ALTER TABLE $table ADD $fk_string"); + push(@sql, + "ALTER TABLE " . $dbh->quote_identifier($table) . " ADD $fk_string"); } } return @sql; @@ -2076,7 +2083,8 @@ sub get_drop_fk_sql { my ($self, $table, $column, $references) = @_; my $fk_name = $self->_get_fk_name($table, $column, $references); - return ("ALTER TABLE $table DROP CONSTRAINT $fk_name"); + return ( + "ALTER TABLE " . Bugzilla->dbh->quote_identifier($table) . " DROP CONSTRAINT $fk_name"); } sub convert_type { @@ -2241,7 +2249,9 @@ sub _get_create_table_ddl { } my $sql - = "CREATE TABLE $table (\n" . join(",\n", @col_lines, @fk_lines) . "\n)"; + = "CREATE TABLE " + . Bugzilla->dbh->quote_identifier($table) . " (\n" + . join(",\n", @col_lines, @fk_lines) . "\n)"; return $sql; } @@ -2265,7 +2275,9 @@ sub _get_create_index_ddl { my $sql = "CREATE "; $sql .= "$index_type " if ($index_type && $index_type eq 'UNIQUE'); $sql - .= "INDEX $index_name ON $table_name \(" . join(", ", @$index_fields) . "\)"; + .= "INDEX $index_name ON " + . Bugzilla->dbh->quote_identifier($table_name) . ' (' + . join(', ', @$index_fields) . ')'; return ($sql); @@ -2291,16 +2303,20 @@ sub get_add_column_ddl { my ($self, $table, $column, $definition, $init_value) = @_; my @statements; + my $dbh = Bugzilla->dbh; push(@statements, - "ALTER TABLE $table " + 'ALTER TABLE ' + . $dbh->quote_identifier($table) . ' ' . $self->ADD_COLUMN . " $column " . $self->get_type_ddl($definition)); # XXX - Note that although this works for MySQL, most databases will fail # before this point, if we haven't set a default. - (push(@statements, "UPDATE $table SET $column = $init_value")) - if defined $init_value; + ( + push(@statements, + 'UPDATE ' . $dbh->quote_identifier($table) . " SET $column = $init_value") + ) if defined $init_value; if (defined $definition->{REFERENCES}) { push(@statements, @@ -2367,6 +2383,7 @@ sub get_alter_column_ddl { my $self = shift; my ($table, $column, $new_def, $set_nulls_to) = @_; + my $dbh = Bugzilla->dbh; my @statements; my $old_def = $self->get_column_abstract($table, $column); @@ -2393,7 +2410,10 @@ sub get_alter_column_ddl { # If we went from having a default to not having one elsif (!defined $default && defined $default_old) { - push(@statements, "ALTER TABLE $table ALTER COLUMN $column" . " DROP DEFAULT"); + push(@statements, + "ALTER TABLE " + . $dbh->quote_identifier($table) + . " ALTER COLUMN $column DROP DEFAULT"); } # If we went from no default to a default, or we changed the default. @@ -2401,28 +2421,40 @@ sub get_alter_column_ddl { || ($default ne $default_old)) { push(@statements, - "ALTER TABLE $table ALTER COLUMN $column " . " SET DEFAULT $default"); + "ALTER TABLE " + . $dbh->quote_identifier($table) + . " ALTER COLUMN $column SET DEFAULT $default"); } # If we went from NULL to NOT NULL. if (!$old_def->{NOTNULL} && $new_def->{NOTNULL}) { push(@statements, $self->_set_nulls_sql(@_)); - push(@statements, "ALTER TABLE $table ALTER COLUMN $column" . " SET NOT NULL"); + push(@statements, + "ALTER TABLE " + . $dbh->quote_identifier($table) + . " ALTER COLUMN $column SET NOT NULL"); } # If we went from NOT NULL to NULL elsif ($old_def->{NOTNULL} && !$new_def->{NOTNULL}) { - push(@statements, "ALTER TABLE $table ALTER COLUMN $column" . " DROP NOT NULL"); + push(@statements, + "ALTER TABLE " + . $dbh->quote_identifier($table) + . " ALTER COLUMN $column DROP NOT NULL"); } # If we went from not being a PRIMARY KEY to being a PRIMARY KEY. if (!$old_def->{PRIMARYKEY} && $new_def->{PRIMARYKEY}) { - push(@statements, "ALTER TABLE $table ADD PRIMARY KEY ($column)"); + push(@statements, + "ALTER TABLE " + . $dbh->quote_identifier($table) + . " ADD PRIMARY KEY ($column)"); } # If we went from being a PK to not being a PK elsif ($old_def->{PRIMARYKEY} && !$new_def->{PRIMARYKEY}) { - push(@statements, "ALTER TABLE $table DROP PRIMARY KEY"); + push(@statements, + "ALTER TABLE " . $dbh->quote_identifier($table) . " DROP PRIMARY KEY"); } return @statements; @@ -2444,7 +2476,10 @@ sub _set_nulls_sql { } my @sql; if (defined $default) { - push(@sql, "UPDATE $table SET $column = $default" . " WHERE $column IS NULL"); + push(@sql, + "UPDATE " + . Bugzilla->dbh->quote_identifier($table) + . " SET $column = $default WHERE $column IS NULL"); } return @sql; } @@ -2479,7 +2514,9 @@ sub get_drop_column_ddl { =cut my ($self, $table, $column) = @_; - return ("ALTER TABLE $table DROP COLUMN $column"); + return ("ALTER TABLE " + . Bugzilla->dbh->quote_identifier($table) + . " DROP COLUMN $column"); } =item C @@ -2492,7 +2529,7 @@ sub get_drop_column_ddl { sub get_drop_table_ddl { my ($self, $table) = @_; - return ("DROP TABLE $table"); + return ('DROP TABLE ' . Bugzilla->dbh->quote_identifier($table)); } sub get_rename_column_ddl { @@ -2542,7 +2579,11 @@ Gets SQL to rename a table in the database. =cut my ($self, $old_name, $new_name) = @_; - return ("ALTER TABLE $old_name RENAME TO $new_name"); + my $dbh = Bugzilla->dbh; + return ('ALTER TABLE ' + . $dbh->quote_identifier($old_name) + . ' RENAME TO ' + . $dbh->quote_identifier($new_name)); } =item C diff --git a/Bugzilla/DB/Schema/Mysql.pm b/Bugzilla/DB/Schema/Mysql.pm index cc555f64c8..76a648eb6a 100644 --- a/Bugzilla/DB/Schema/Mysql.pm +++ b/Bugzilla/DB/Schema/Mysql.pm @@ -144,11 +144,15 @@ sub _get_create_index_ddl { # Returns a "create index" SQL statement. my ($self, $table_name, $index_name, $index_fields, $index_type) = @_; + my $dbh = Bugzilla->dbh; my $sql = "CREATE "; $sql .= "$index_type " if ($index_type eq 'UNIQUE' || $index_type eq 'FULLTEXT'); - $sql .= "INDEX \`$index_name\` ON $table_name \(" + $sql + .= "INDEX " + . $dbh->quote_identifier($index_name) . " ON " + . $dbh->quote_identifier($table_name) . " \(" . join(", ", @$index_fields) . "\)"; return ($sql); @@ -182,10 +186,12 @@ sub get_alter_column_ddl { my @statements; - push( - @statements, "UPDATE $table SET $column = $set_nulls_to - WHERE $column IS NULL" - ) if defined $set_nulls_to; + my $dbh = Bugzilla->dbh; + push(@statements, + "UPDATE " + . $dbh->quote_identifier($table) + . " SET $column = $set_nulls_to WHERE $column IS NULL") + if defined $set_nulls_to; # Calling SET DEFAULT or DROP DEFAULT is *way* faster than calling # CHANGE COLUMN, so just do that if we're just changing the default. @@ -197,27 +203,32 @@ sub get_alter_column_ddl { && $self->columns_equal(\%new_defaultless, \%old_defaultless)) { if (!defined $new_def->{DEFAULT}) { - push(@statements, "ALTER TABLE $table ALTER COLUMN $column DROP DEFAULT"); + push(@statements, + "ALTER TABLE " + . $dbh->quote_identifier($table) + . " ALTER COLUMN $column DROP DEFAULT"); } else { - push( - @statements, "ALTER TABLE $table ALTER COLUMN $column - SET DEFAULT " . $new_def->{DEFAULT} - ); + push(@statements, + "ALTER TABLE " + . $dbh->quote_identifier($table) + . " ALTER COLUMN $column SET DEFAULT " + . $new_def->{DEFAULT}); } } else { my $new_ddl = $self->get_type_ddl(\%new_def_copy); - push( - @statements, "ALTER TABLE $table CHANGE COLUMN - $column $column $new_ddl" - ); + push(@statements, + "ALTER TABLE " + . $dbh->quote_identifier($table) + . " CHANGE COLUMN $column $column $new_ddl"); } if ($old_def->{PRIMARYKEY} && !$new_def->{PRIMARYKEY}) { # Dropping a PRIMARY KEY needs an explicit DROP PRIMARY KEY - push(@statements, "ALTER TABLE $table DROP PRIMARY KEY"); + push(@statements, + 'ALTER TABLE ' . $dbh->quote_identifier($table) . ' DROP PRIMARY KEY'); } return @statements; @@ -226,8 +237,9 @@ sub get_alter_column_ddl { sub get_drop_fk_sql { my ($self, $table, $column, $references) = @_; my $fk_name = $self->_get_fk_name($table, $column, $references); - my @sql = ("ALTER TABLE $table DROP FOREIGN KEY $fk_name"); my $dbh = Bugzilla->dbh; + my @sql = ( + "ALTER TABLE " . $dbh->quote_identifier($table) . " DROP FOREIGN KEY $fk_name"); # MySQL requires, and will create, an index on any column with # an FK. It will name it after the fk, which we never do. @@ -254,7 +266,7 @@ sub get_rename_indexes_ddl { my ($self, $table, %indexes) = @_; my @keys = keys %indexes or return (); - my $sql = "ALTER TABLE $table "; + my $sql = 'ALTER TABLE' . Bugzilla->dbh->quote_identifier($table) . ' '; foreach my $old_name (@keys) { my $name = $indexes{$old_name}->{NAME}; @@ -275,7 +287,9 @@ sub get_rename_indexes_ddl { sub get_set_serial_sql { my ($self, $table, $column, $value) = @_; - return ("ALTER TABLE $table AUTO_INCREMENT = $value"); + return ("ALTER TABLE " + . Bugzilla->dbh->quote_identifier($table) + . " AUTO_INCREMENT = $value"); } # Converts a DBI column_info output to an abstract column definition. @@ -413,7 +427,9 @@ sub get_rename_column_ddl { # MySQL doesn't like having the PRIMARY KEY statement in a rename. $def =~ s/PRIMARY KEY//i; - return ("ALTER TABLE $table CHANGE COLUMN $old_name $new_name $def"); + return ("ALTER TABLE " + . Bugzilla->dbh->quote_identifier($table) + . " CHANGE COLUMN $old_name $new_name $def"); } 1; diff --git a/Bugzilla/DB/Schema/Sqlite.pm b/Bugzilla/DB/Schema/Sqlite.pm index 131b51088b..5814cf0aaf 100644 --- a/Bugzilla/DB/Schema/Sqlite.pm +++ b/Bugzilla/DB/Schema/Sqlite.pm @@ -109,7 +109,7 @@ sub _sqlite_alter_schema { my $insert_str = join(',', @insert_cols); my $select_str = join(',', @select_cols); my $copy_sql - = "INSERT INTO $table ($insert_str)" . " SELECT $select_str FROM $rename_to"; + = "INSERT INTO " . $dbh->quote_identifier($table) . " ($insert_str)" . " SELECT $select_str FROM " . $dbh->quote_identifier($rename_to); # We have to turn FKs off before doing this. Otherwise, when we rename # the table, all of the FKs in the other tables will be automatically @@ -122,7 +122,10 @@ sub _sqlite_alter_schema { 'PRAGMA foreign_keys = OFF', 'BEGIN EXCLUSIVE TRANSACTION', @{$options->{pre_sql} || []}, - "ALTER TABLE $table RENAME TO $rename_to", + 'ALTER TABLE ' + . Bugzilla->dbh->quote_identifier($table) + . ' RENAME TO ' + . Bugzilla->dbh->quote_identifier($rename_to), $create_table, $copy_sql, "DROP TABLE $rename_to", diff --git a/Bugzilla/Group.pm b/Bugzilla/Group.pm index cf10ff0a29..2d7c72e85c 100644 --- a/Bugzilla/Group.pm +++ b/Bugzilla/Group.pm @@ -440,7 +440,8 @@ sub create { sub ValidateGroupName { my ($name, @users) = (@_); my $dbh = Bugzilla->dbh; - my $query = "SELECT id FROM groups " . "WHERE name = ?"; + my $query + = 'SELECT id FROM ' . $dbh->quote_identifier('groups') . ' WHERE name = ?'; if (Bugzilla->params->{'usevisibilitygroups'}) { my @visible = (-1); foreach my $user (@users) { diff --git a/Bugzilla/Install/DB.pm b/Bugzilla/Install/DB.pm index 85a751e599..5697e10cda 100644 --- a/Bugzilla/Install/DB.pm +++ b/Bugzilla/Install/DB.pm @@ -1640,7 +1640,8 @@ sub _convert_groups_system_from_groupset { $dbh->bz_drop_index('groups', 'groups_name_idx'); my @primary_key = $dbh->primary_key(undef, undef, 'groups'); if (@primary_key) { - $dbh->do("ALTER TABLE groups DROP PRIMARY KEY"); + $dbh->do( + 'ALTER TABLE ' . $dbh->quote_identifier('groups') . ' DROP PRIMARY KEY'); } $dbh->bz_add_column('groups', 'id', @@ -1651,7 +1652,8 @@ sub _convert_groups_system_from_groupset { # Convert all existing groupset records to map entries before removing # groupset fields or removing "bit" from groups. - my $sth = $dbh->prepare("SELECT bit, id FROM groups WHERE bit > 0"); + my $sth = $dbh->prepare( + 'SELECT bit, id FROM ' . $dbh->quote_identifier('groups') . ' WHERE bit > 0'); $sth->execute(); while (my ($bit, $gid) = $sth->fetchrow_array) { @@ -1746,7 +1748,7 @@ sub _convert_groups_system_from_groupset { # Get names of groups added. my $sth2 = $dbh->prepare( - "SELECT name FROM groups + "SELECT name FROM " . $dbh->quote_identifier('groups') . " WHERE (bit & $added) != 0 AND (bit & $removed) = 0" ); @@ -1758,7 +1760,7 @@ sub _convert_groups_system_from_groupset { # Get names of groups removed. $sth2 = $dbh->prepare( - "SELECT name FROM groups + "SELECT name FROM " . $dbh->quote_identifier('groups') . " WHERE (bit & $removed) != 0 AND (bit & $added) = 0" ); @@ -1771,9 +1773,7 @@ sub _convert_groups_system_from_groupset { # Get list of group bits added that correspond to # missing groups. $sth2 = $dbh->prepare( - "SELECT ($added & ~BIT_OR(bit)) - FROM groups" - ); + "SELECT ($added & ~BIT_OR(bit)) FROM " . $dbh->quote_identifier('groups')); $sth2->execute(); my ($miss) = $sth2->fetchrow_array; if ($miss) { @@ -1785,9 +1785,7 @@ sub _convert_groups_system_from_groupset { # Get list of group bits deleted that correspond to # missing groups. $sth2 = $dbh->prepare( - "SELECT ($removed & ~BIT_OR(bit)) - FROM groups" - ); + "SELECT ($removed & ~BIT_OR(bit)) FROM " . $dbh->quote_identifier('groups')); $sth2->execute(); ($miss) = $sth2->fetchrow_array; if ($miss) { @@ -1823,7 +1821,7 @@ sub _convert_groups_system_from_groupset { # Get names of groups added. my $sth2 = $dbh->prepare( - "SELECT name FROM groups + "SELECT name FROM " . $dbh->quote_identifier('groups') . " WHERE (bit & $added) != 0 AND (bit & $removed) = 0" ); @@ -1835,7 +1833,7 @@ sub _convert_groups_system_from_groupset { # Get names of groups removed. $sth2 = $dbh->prepare( - "SELECT name FROM groups + "SELECT name FROM " . $dbh->quote_identifier('groups') . " WHERE (bit & $removed) != 0 AND (bit & $added) = 0" ); @@ -1864,11 +1862,12 @@ sub _convert_groups_system_from_groupset { # Identify admin group. my ($admin_gid) - = $dbh->selectrow_array("SELECT id FROM groups WHERE name = 'admin'"); + = $dbh->selectrow_array( + "SELECT id FROM " . $dbh->quote_identifier('groups') . " WHERE name = 'admin'"); if (!$admin_gid) { - $dbh->do(q{INSERT INTO groups (name, description) - VALUES ('admin', 'Administrators')} - ); + $dbh->do("INSERT INTO " + . $dbh->quote_identifier('groups') + . " (name, description) VALUES ('admin', 'Administrators')"); $admin_gid = $dbh->bz_last_key('groups', 'id'); } @@ -2495,7 +2494,8 @@ sub _fix_group_with_empty_name { # Note that there can be at most one such group (because of # the SQL index on the name column). my ($emptygroupid) - = $dbh->selectrow_array("SELECT id FROM groups where name = ''"); + = $dbh->selectrow_array( + "SELECT id FROM " . $dbh->quote_identifier('groups') . " where name = ''"); if ($emptygroupid) { # There is a group with an empty name; find a name to rename it @@ -2503,7 +2503,8 @@ sub _fix_group_with_empty_name { # group_$gid and add _ if necessary. my $trycount = 0; my $trygroupname; - my $sth = $dbh->prepare("SELECT 1 FROM groups where name = ?"); + my $sth = $dbh->prepare( + 'SELECT 1 FROM ' . $dbh->quote_identifier('groups') . ' where name = ?'); my $name_exists = 1; while ($name_exists) { @@ -2514,7 +2515,8 @@ sub _fix_group_with_empty_name { $name_exists = $dbh->selectrow_array($sth, undef, $trygroupname); $trycount++; } - $dbh->do("UPDATE groups SET name = ? WHERE id = ?", + $dbh->do( + 'UPDATE ' . $dbh->quote_identifier('groups') . ' SET name = ? WHERE id = ?', undef, $trygroupname, $emptygroupid); print "Group $emptygroupid had an empty name; renamed as", " '$trygroupname'.\n"; @@ -2685,7 +2687,8 @@ sub _change_all_mysql_booleans_to_tinyint { my $quip_info_sth = $dbh->column_info(undef, undef, 'quips', '%'); my $quips_cols = $quip_info_sth->fetchall_hashref("COLUMN_NAME"); my $approved_col = $quips_cols->{'approved'}; - if ( $approved_col->{TYPE_NAME} eq 'TINYINT' + if ($approved_col->{TYPE_NAME} eq 'TINYINT' + and defined $approved_col->{COLUMN_SIZE} and $approved_col->{COLUMN_SIZE} == 1) { # series.public could have been renamed to series.is_public, @@ -2947,8 +2950,11 @@ EOT sub _rederive_regex_groups { my $dbh = Bugzilla->dbh; - my $regex_groups_exist = $dbh->selectrow_array( - "SELECT 1 FROM groups WHERE userregexp = '' " . $dbh->sql_limit(1)); + my $regex_groups_exist + = $dbh->selectrow_array("SELECT 1 FROM " + . $dbh->quote_identifier('groups') + . " WHERE userregexp = '' " + . $dbh->sql_limit(1)); return if !$regex_groups_exist; my $regex_derivations @@ -2963,7 +2969,7 @@ sub _rederive_regex_groups { my $sth = $dbh->prepare( "SELECT profiles.userid, profiles.login_name, groups.id, groups.userregexp, user_group_map.group_id - FROM (profiles CROSS JOIN groups) + FROM (profiles CROSS JOIN " . $dbh->quote_identifier('groups') . ") LEFT JOIN user_group_map ON user_group_map.user_id = profiles.userid AND user_group_map.group_id = groups.id diff --git a/Bugzilla/Migrate.pm b/Bugzilla/Migrate.pm index 75ec18ebc6..0d601ffcf3 100644 --- a/Bugzilla/Migrate.pm +++ b/Bugzilla/Migrate.pm @@ -894,8 +894,13 @@ sub _do_table_insert { my @values = map { $hash->{$_} } @fields; my $field_sql = join(',', @fields); my $question_sql = join(',', @questions); - Bugzilla->dbh->do("INSERT INTO $table ($field_sql) VALUES ($question_sql)", - undef, @values); + my $dbh = Bugzilla->dbh; + $dbh->do( + "INSERT INTO " + . $dbh->quote_identifier($table) + . " ($field_sql) VALUES ($question_sql)", + undef, @values + ); } ###################### diff --git a/Bugzilla/Object.pm b/Bugzilla/Object.pm index 67335b8606..ee49de84a4 100644 --- a/Bugzilla/Object.pm +++ b/Bugzilla/Object.pm @@ -108,6 +108,7 @@ sub _load_from_db { my $table = $class->DB_TABLE; my $name_field = $class->NAME_FIELD; my $id_field = $class->ID_FIELD; + my $sql_table = $dbh->quote_identifier($table); my $id = $param; if (ref $param eq 'HASH') { @@ -126,11 +127,10 @@ sub _load_from_db { # Too large integers make PostgreSQL crash. return if $id > MAX_INT_32; - $object_data = $dbh->selectrow_hashref( - qq{ - SELECT $columns FROM $table - WHERE $id_field = ?}, undef, $id - ); + $object_data + = $dbh->selectrow_hashref( + "SELECT $columns FROM $sql_table WHERE $id_field = ?", + undef, $id); } else { unless (defined $param->{name} @@ -159,7 +159,7 @@ sub _load_from_db { map { trick_taint($_) } @values; $object_data - = $dbh->selectrow_hashref("SELECT $columns FROM $table WHERE $condition", + = $dbh->selectrow_hashref("SELECT $columns FROM $sql_table WHERE $condition", undef, @values); } return $object_data; @@ -386,15 +386,16 @@ sub _do_list_select { } if (!$objects) { - my $sql = "SELECT $cols FROM $table"; + my $dbh = Bugzilla->dbh; + + my $sql_table = $dbh->quote_identifier($table); + my $sql = "SELECT $cols FROM $sql_table"; if (defined $where) { $sql .= " WHERE $where "; } $sql .= " ORDER BY $order"; $sql .= " $postamble" if $postamble; - my $dbh = Bugzilla->dbh; - # Sometimes the values are tainted, but we don't want to untaint them # for the caller. So we copy the array. It's safe to untaint because # they're only used in placeholders here. @@ -533,7 +534,8 @@ sub update { my $columns = join(', ', map {"$_ = ?"} @update_columns); - $dbh->do("UPDATE $table SET $columns WHERE $id_field = ?", + my $sql_table = $dbh->quote_identifier($table); + $dbh->do("UPDATE $sql_table SET $columns WHERE $id_field = ?", undef, @values, $self->id) if @values; @@ -565,7 +567,8 @@ sub remove_from_db { my $dbh = Bugzilla->dbh; $dbh->bz_start_transaction(); $self->audit_log(AUDIT_REMOVE) if $self->AUDIT_REMOVES; - $dbh->do("DELETE FROM $table WHERE $id_field = ?", undef, $self->id); + my $sql_table = $dbh->quote_identifier($table); + $dbh->do("DELETE FROM $sql_table WHERE $id_field = ?", undef, $self->id); $dbh->bz_commit_transaction(); if ($self->USE_MEMCACHED) { @@ -644,8 +647,9 @@ sub any_exist { my $class = shift; my $table = $class->DB_TABLE; my $dbh = Bugzilla->dbh; - my $any_exist - = $dbh->selectrow_array("SELECT 1 FROM $table " . $dbh->sql_limit(1)); + my $sql_table = $dbh->quote_identifier($table); + my $any_exist = $dbh->selectrow_array( + "SELECT 1 FROM $sql_table " . $dbh->sql_limit(1)); return $any_exist ? 1 : 0; } @@ -743,9 +747,13 @@ sub insert_create_data { my $qmarks = '?,' x @field_names; chop($qmarks); my $table = $class->DB_TABLE; + my $sql_table = $dbh->quote_identifier($table); $dbh->do( - "INSERT INTO $table (" . join(', ', @field_names) . ") VALUES ($qmarks)", - undef, @values); + "INSERT INTO $sql_table (" + . join(', ', @field_names) + . ") VALUES ($qmarks)", + undef, @values + ); my $id = $dbh->bz_last_key($table, $class->ID_FIELD); my $object = $class->new($id); diff --git a/Bugzilla/Product.pm b/Bugzilla/Product.pm index 1a4524033d..d4cb7da6c8 100644 --- a/Bugzilla/Product.pm +++ b/Bugzilla/Product.pm @@ -623,13 +623,13 @@ sub group_controls { if (!defined $self->{group_controls} || $full_data) { # Include name to the list, to allow us sorting data more easily. - my $query = qq{SELECT id, name, entry, membercontrol, othercontrol, + my $query = "SELECT id, name, entry, membercontrol, othercontrol, canedit, editcomponents, editbugs, canconfirm - FROM groups + FROM " . $dbh->quote_identifier('groups') . " LEFT JOIN group_control_map ON id = group_id $where_or_and product_id = ? - $and_or_where isbuggroup = 1}; + $and_or_where isbuggroup = 1"; $self->{group_controls} = $dbh->selectall_hashref($query, 'id', undef, $self->id); @@ -666,7 +666,9 @@ sub groups_available { $dbh->selectcol_arrayref( "SELECT group_id, membercontrol FROM group_control_map - INNER JOIN groups ON group_control_map.group_id = groups.id + INNER JOIN " + . $dbh->quote_identifier('groups') + . " ON group_control_map.group_id = groups.id WHERE isbuggroup = 1 AND isactive = 1 AND product_id = ? AND (membercontrol = $shown OR membercontrol = $default) AND " . Bugzilla->user->groups_in_sql(), {Columns => [1, 2]}, @@ -681,7 +683,9 @@ sub groups_available { $dbh->selectcol_arrayref( "SELECT group_id, othercontrol FROM group_control_map - INNER JOIN groups ON group_control_map.group_id = groups.id + INNER JOIN " + . $dbh->quote_identifier('groups') + . " ON group_control_map.group_id = groups.id WHERE isbuggroup = 1 AND isactive = 1 AND product_id = ? AND (othercontrol = $shown OR othercontrol = $default)", {Columns => [1, 2]}, $self->id @@ -715,10 +719,13 @@ sub groups_mandatory { # For membercontrol we don't check group_id IN, because if membercontrol # is Mandatory, the group is Mandatory for everybody, regardless of their # group membership. + my $dbh = Bugzilla->dbh; my $ids = Bugzilla->dbh->selectcol_arrayref( "SELECT group_id FROM group_control_map - INNER JOIN groups ON group_control_map.group_id = groups.id + INNER JOIN " + . $dbh->quote_identifier('groups') + . " ON group_control_map.group_id = groups.id WHERE product_id = ? AND isactive = 1 AND (membercontrol = $mandatory OR (othercontrol = $mandatory @@ -751,10 +758,13 @@ sub groups_valid { # Note that we don't check OtherControl below, because there is no # valid NA/* combination. - my $ids = Bugzilla->dbh->selectcol_arrayref( + my $dbh = Bugzilla->dbh; + my $ids = $dbh->selectcol_arrayref( "SELECT DISTINCT group_id FROM group_control_map AS gcm - INNER JOIN groups ON gcm.group_id = groups.id + INNER JOIN " + . $dbh->quote_identifier('groups') + . " ON gcm.group_id = groups.id WHERE product_id = ? AND isbuggroup = 1 AND membercontrol != " . CONTROLMAPNA, undef, $self->id ); diff --git a/Bugzilla/Search.pm b/Bugzilla/Search.pm index 52071ec0b3..fcd6273abb 100644 --- a/Bugzilla/Search.pm +++ b/Bugzilla/Search.pm @@ -1263,8 +1263,9 @@ sub _translate_join { if ($extra_condition) { $extra_condition = " AND $extra_condition"; } - - my @join_sql = "$join JOIN $table AS $name" + my $sql_table + = $table eq 'groups' ? Bugzilla->dbh->quote_identifier($table) : $table; + my @join_sql = "$join JOIN $sql_table AS $name " . " ON $from_table.$from = $name.$to$extra_condition"; return @join_sql; } @@ -2937,7 +2938,7 @@ sub _multiselect_table { } elsif ($field eq 'bug_group') { $args->{full_field} = 'groups.name'; - return "bug_group_map INNER JOIN groups + return "bug_group_map INNER JOIN " . $dbh->quote_identifier('groups') . " ON bug_group_map.group_id = groups.id"; } elsif ($field eq 'blocked' or $field eq 'dependson') { diff --git a/Bugzilla/User.pm b/Bugzilla/User.pm index 847d13c300..b33c397484 100644 --- a/Bugzilla/User.pm +++ b/Bugzilla/User.pm @@ -1630,7 +1630,7 @@ sub visible_groups_direct { } else { # All groups are visible if usevisibilitygroups is off. - $sth = $dbh->prepare('SELECT id FROM groups'); + $sth = $dbh->prepare('SELECT id FROM ' . $dbh->quote_identifier('groups')); } $sth->execute(); @@ -1694,7 +1694,7 @@ sub derive_regexp_groups { $sth = $dbh->prepare( "SELECT id, userregexp, user_group_map.group_id - FROM groups + FROM " . $dbh->quote_identifier('groups') . " LEFT JOIN user_group_map ON groups.id = user_group_map.group_id AND user_group_map.user_id = ? diff --git a/editgroups.cgi b/editgroups.cgi index f52f548f0c..8197180b8c 100755 --- a/editgroups.cgi +++ b/editgroups.cgi @@ -45,10 +45,11 @@ my $token = $cgi->param('token'); sub CheckGroupID { my ($group_id) = @_; $group_id = trim($group_id || 0); + my $dbh = Bugzilla->dbh; ThrowUserError("group_not_specified") unless $group_id; ( - detaint_natural($group_id) && Bugzilla->dbh->selectrow_array( - "SELECT id FROM groups WHERE id = ?", + detaint_natural($group_id) && $dbh->selectrow_array( + 'SELECT id FROM ' . $dbh->quote_identifier('groups') . ' WHERE id = ?', undef, $group_id ) ) || ThrowUserError("invalid_group_ID"); diff --git a/editproducts.cgi b/editproducts.cgi index 53f07d6515..1b1b9b6e5f 100755 --- a/editproducts.cgi +++ b/editproducts.cgi @@ -368,7 +368,7 @@ if ($action eq 'updategroupcontrols') { FROM bugs INNER JOIN bug_group_map ON bug_group_map.bug_id = bugs.bug_id - INNER JOIN groups + INNER JOIN ' . $dbh->quote_identifier('groups') . ' ON bug_group_map.group_id = groups.id WHERE groups.id IN (' . join(', ', @now_na) . ') AND bugs.product_id = ? ' . $dbh->sql_group_by('groups.name'), @@ -390,7 +390,7 @@ if ($action eq 'updategroupcontrols') { (SELECT bug_group_map.bug_id FROM bug_group_map WHERE bug_group_map.group_id = groups.id)) AS count - FROM groups + FROM ' . $dbh->quote_identifier('groups') . ' WHERE groups.id IN (' . join(', ', @now_mandatory) . ') ORDER BY groups.name', {'Slice' => {}}, $product->id ); diff --git a/editusers.cgi b/editusers.cgi index 3d3734a55a..21e0780589 100755 --- a/editusers.cgi +++ b/editusers.cgi @@ -708,16 +708,16 @@ sub userDataToVars { $vars->{'groups'} = $user->bless_groups(); $vars->{'permissions'} = $dbh->selectall_hashref( - qq{SELECT id, + 'SELECT id, COUNT(directmember.group_id) AS directmember, COUNT(regexpmember.group_id) AS regexpmember, - (CASE WHEN (groups.id IN ($grouplist) + (CASE WHEN (groups.id IN (' . $grouplist . ') AND COUNT(directmember.group_id) = 0 AND COUNT(regexpmember.group_id) = 0 ) THEN 1 ELSE 0 END) AS derivedmember, COUNT(directbless.group_id) AS directbless - FROM groups + FROM ' . $dbh->quote_identifier('groups') . ' LEFT JOIN user_group_map AS directmember ON directmember.group_id = id AND directmember.user_id = ? @@ -733,7 +733,7 @@ sub userDataToVars { AND directbless.user_id = ? AND directbless.isbless = 1 AND directbless.grant_type = ? - } . $dbh->sql_group_by('id'), + ' . $dbh->sql_group_by('id'), 'id', undef, ( $otheruserid, GRANT_DIRECT, $otheruserid, GRANT_REGEXP, @@ -742,12 +742,14 @@ sub userDataToVars { ); # Find indirect bless permission. - $query = qq{SELECT groups.id - FROM groups, group_group_map AS ggm + $query = 'SELECT groups.id + FROM ' + . $dbh->quote_identifier('groups') + . ', group_group_map AS ggm WHERE groups.id = ggm.grantor_id - AND ggm.member_id IN ($grouplist) + AND ggm.member_id IN (' . $grouplist . ') AND ggm.grant_type = ? - } . $dbh->sql_group_by('id'); + ' . $dbh->sql_group_by('id'); foreach (@{$dbh->selectall_arrayref($query, undef, (GROUP_BLESS))}) { # Merge indirect bless permissions into permission variable. diff --git a/sanitycheck.cgi b/sanitycheck.cgi index b78add3d22..a04fd9be2d 100755 --- a/sanitycheck.cgi +++ b/sanitycheck.cgi @@ -119,7 +119,7 @@ if ($cgi->param('createmissinggroupcontrolmapentries')) { # Find all group/product combinations used for bugs but not set up # correctly in group_control_map my $invalid_combinations = $dbh->selectall_arrayref( - qq{ SELECT bugs.product_id, + "SELECT bugs.product_id, bgm.group_id, gcm.membercontrol, groups.name, @@ -127,15 +127,14 @@ if ($cgi->param('createmissinggroupcontrolmapentries')) { FROM bugs INNER JOIN bug_group_map AS bgm ON bugs.bug_id = bgm.bug_id - INNER JOIN groups + INNER JOIN " . $dbh->quote_identifier('groups') . " ON bgm.group_id = groups.id INNER JOIN products ON bugs.product_id = products.id LEFT JOIN group_control_map AS gcm ON bugs.product_id = gcm.product_id AND bgm.group_id = gcm.group_id - WHERE COALESCE(gcm.membercontrol, $na) = $na - } + WHERE COALESCE(gcm.membercontrol, $na) = $na " . $dbh->sql_group_by( 'bugs.product_id, bgm.group_id', 'gcm.membercontrol, groups.name, products.name' @@ -366,7 +365,7 @@ if ($cgi->param('remove_old_whine_targets')) { my $old_ids = $dbh->selectcol_arrayref( "SELECT DISTINCT mailto FROM whine_schedules - LEFT JOIN $table + LEFT JOIN " . $dbh->quote_identifier($table) . " ON $table.$col = whine_schedules.mailto WHERE mailto_type = $type AND $table.$col IS NULL" ); @@ -438,13 +437,14 @@ sub CrossCheck { Status('cross_check_from', {table => $refertable, field => $referfield}); my $query - = qq{SELECT DISTINCT $refertable.$referfield} - . ($keyname ? qq{, $refertable.$keyname } : q{}) - . qq{ FROM $refertable - LEFT JOIN $table + = "SELECT DISTINCT $refertable.$referfield" + . ($keyname ? ", $refertable.$keyname " : "") + . " FROM " + . $dbh->quote_identifier($refertable) . " + LEFT JOIN " . $dbh->quote_identifier($table) . " ON $refertable.$referfield = $table.$field WHERE $table.$field IS NULL - AND $refertable.$referfield IS NOT NULL}; + AND $refertable.$referfield IS NOT NULL"; my $sth = $dbh->prepare($query); $sth->execute; @@ -661,14 +661,14 @@ sub DoubleCrossCheck { qq{ SELECT DISTINCT $refertable.$referfield1, $refertable.$referfield2 } - . ($keyname ? qq{, $refertable.$keyname } : q{}) . qq{ FROM $refertable - LEFT JOIN $table + . ($keyname ? qq{, $refertable.$keyname } : q{}) . " FROM $refertable + LEFT JOIN " . $dbh->quote_identifier($table) . " ON $refertable.$referfield1 = $table.$field1 AND $refertable.$referfield2 = $table.$field2 WHERE $table.$field1 IS NULL AND $table.$field2 IS NULL AND $refertable.$referfield1 IS NOT NULL - AND $refertable.$referfield2 IS NOT NULL} + AND $refertable.$referfield2 IS NOT NULL" ); foreach my $check (@$d_cross_check) { @@ -973,7 +973,7 @@ BugCheck( "bugs INNER JOIN group_control_map ON bugs.product_id = group_control_map.product_id - INNER JOIN groups + INNER JOIN " . $dbh->quote_identifier('groups') . " ON group_control_map.group_id = groups.id LEFT JOIN bug_group_map ON bugs.bug_id = bug_group_map.bug_id @@ -1018,7 +1018,7 @@ foreach my $target (['groups', 'id', MAILTO_GROUP], my $old = $dbh->selectall_arrayref( "SELECT whine_schedules.id, mailto FROM whine_schedules - LEFT JOIN $table + LEFT JOIN " . $dbh->quote_identifier($table) . " ON $table.$col = whine_schedules.mailto WHERE mailto_type = $type AND $table.$col IS NULL" ); diff --git a/userprefs.cgi b/userprefs.cgi index c96e7670fd..9faada1c20 100755 --- a/userprefs.cgi +++ b/userprefs.cgi @@ -390,18 +390,19 @@ sub DoPermissions { my (@has_bits, @set_bits); my $groups - = $dbh->selectall_arrayref( - "SELECT DISTINCT name, description FROM groups WHERE id IN (" + = $dbh->selectall_arrayref('SELECT DISTINCT name, description FROM ' + . $dbh->quote_identifier('groups') + . ' WHERE id IN (' . $user->groups_as_string - . ") ORDER BY name"); + . ') ORDER BY name'); foreach my $group (@$groups) { my ($nam, $desc) = @$group; push(@has_bits, {"desc" => $desc, "name" => $nam}); } $groups = $dbh->selectall_arrayref( 'SELECT DISTINCT id, name, description - FROM groups - ORDER BY name' + FROM ' . $dbh->quote_identifier('groups') . ' + ORDER BY name' ); foreach my $group (@$groups) { my ($group_id, $nam, $desc) = @$group; diff --git a/whine.pl b/whine.pl index cb10ea9a5f..b28397c86b 100755 --- a/whine.pl +++ b/whine.pl @@ -250,7 +250,8 @@ sub get_next_event { } } elsif ($mailto_type == MAILTO_GROUP) { - my $sth = $dbh->prepare("SELECT name FROM groups " . "WHERE id=?"); + my $sth = $dbh->prepare( + 'SELECT name FROM ' . $dbh->quote_identifier('groups') . ' WHERE id = ?'); $sth->execute($mailto); my $groupname = $sth->fetch->[0]; my $group_id = Bugzilla::Group::ValidateGroupName($groupname, $owner); -- 2.47.3