#!/usr/bin/env ruby

# bugzilla_spammer.rb
#
# Copyright (C) 2007 Jonathan Cheyer
#
# Author: Jonathan Cheyer, http://cheyer.biz
# Initial Version: 2007-08-03
# License: GPLv2
#
# Remove all bugzilla data that was created by a spammer.
#
# Rationale: Bugzilla 2.22 and older has no way to do basic spam
#    cleanup, such as deleting a user, deleting bugs, deleting attachments,
#    and deleting comments. Spammers like to create attachments on existing
#    bugs. Those attachments contain HTML spam in them and can be reached
#    by direct URL, bypassing normal bugzilla use altogether. This allows
#    the spammer to include those URLs in email spam that they send to
#    people, thus using your bugzilla server as a spam file server.
#    Bugzilla 3.0 tries to add in some basic support for deleting attachments,
#    but you need to remove them one at a time (multiple clicks per removal)
#    and comments can't be removed. Users can't really be removed properly
#    because you get warnings about causing referential integrity problems
#    in your database.
#
# Description: This script can be used to specify an email address which 
# is a spammer. All bugs, attachments, comments, and bug activities that 
# were touched by the spammer will be removed, without causing any 
# referential integrity problems in your database.
#
# Read the directions before using. Understand what the program is doing.
# Backup your data before using.
#
# USE AT YOUR OWN RISK. You have been warned.
#
# WARNING: This program intentionally deletes data from your bugzilla
#    database. The purpose of this program is to remove any data that may
#    have been created by a spammer. Some care has been taken to ensure that
#    that the program is working as documented. However, no guarantee is
#    given that you won't lose data that you intended to keep, especially in the
#    case of user error (ex: you give the program an email address of a
#    user which you want to keep data for). As with any program licensed 
#    under GPLv2, this program comes without warranty. Under no circumstance
#    will Jonathan Cheyer be held liable for loss of data by this program, 
#    accidental or otherwise.
#
# Disclaimer of Warranty. THERE IS NO WARRANTY FOR THE PROGRAM, TO THE 
# EXTENT PERMITTED BY APPLICABLE LAW. EXCEPT WHEN OTHERWISE STATED IN WRITING 
# THE COPYRIGHT HOLDERS AND/OR OTHER PARTIES PROVIDE THE PROGRAM “AS IS” 
# WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING, BUT 
# NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR 
# A PARTICULAR PURPOSE. THE ENTIRE RISK AS TO THE QUALITY AND PERFORMANCE OF 
# THE PROGRAM IS WITH YOU. SHOULD THE PROGRAM PROVE DEFECTIVE, YOU ASSUME THE 
# COST OF ALL NECESSARY SERVICING, REPAIR OR CORRECTION.
#
# Limitation of Liability. IN NO EVENT UNLESS REQUIRED BY APPLICABLE LAW OR 
# AGREED TO IN WRITING WILL ANY COPYRIGHT HOLDER, OR ANY OTHER PARTY WHO 
# MODIFIES AND/OR CONVEYS THE PROGRAM AS PERMITTED ABOVE, BE LIABLE TO YOU 
# FOR DAMAGES, INCLUDING ANY GENERAL, SPECIAL, INCIDENTAL OR CONSEQUENTIAL 
# DAMAGES ARISING OUT OF THE USE OR INABILITY TO USE THE PROGRAM (INCLUDING 
# BUT NOT LIMITED TO LOSS OF DATA OR DATA BEING RENDERED INACCURATE OR LOSSES 
# SUSTAINED BY YOU OR THIRD PARTIES OR A FAILURE OF THE PROGRAM TO OPERATE 
# WITH ANY OTHER PROGRAMS), EVEN IF SUCH HOLDER OR OTHER PARTY HAS BEEN 
# ADVISED OF THE POSSIBILITY OF SUCH DAMAGES.

require 'mysql'
require 'optparse'

class Query
  attr_accessor :options 

  def initialize(options)
    @options = options
  end

  def query(sql)
    begin
      puts " connecting to database: {host=%s, port=%s, user=%s, database=%s, socket=%s}\n" % [@options['host'], @options['port'], @options['user'], @options['database'], @options['socket']] if @options['debug']
      dbh = Mysql.real_connect(@options['host'], @options['user'], @options['password'], @options['database'], @options['port'], @options['socket'])
      puts "  executing query: '%s'\n" % sql if @options['debug']
      res = dbh.query(sql)
      array = []
      if res != nil
        res.each do |row|
          array << row[0]
        end
      end
      return array 
    rescue Mysql::Error => e
      $stderr.puts "Error code: #{e.errno}"
      $stderr.puts "Error message: #{e.error}"
      $stderr.puts "Error SQLSTATE: #{e.sqlstate}" if e.respond_to?("sqlstate")
      raise "database problem, exiting."
    ensure
      dbh.close if dbh
    end
  end

  def display(s)
    puts s if not @options['quiet']
  end
end

def remove_spammer(options, spammer)
  # get spammer's userid
  q = Query.new(options)
  userid = q.query("select userid from profiles where login_name = '%s'" % spammer)

  if userid.empty? then
    q.display "spammer doesn't exist with that email address or has already been removed"
    return
  else
    q.display "Cleaning up mess left by: %s\n" % spammer
  end

  # check if spammer created attachments
  attach_ids = q.query("select attach_id from attachments where submitter_id = %s" % userid)

  if attach_ids.empty? then
    q.display "spammer did not create any attachments\n"
  else
    # remove spammer's attachments
    attach_str = attach_ids.join(",")
    q.display "Attachments: %s\n" % attach_ids.length
    q.query("delete from attach_data where id in (%s)" % attach_str)
    q.display "  attachment data has been deleted\n"
    q.query("delete from attachments where attach_id in (%s)" % attach_str)
    q.display "  attachments have been deleted\n"
  end

  # remove spammer's comments
  count = q.query("select count(*) from longdescs where who = %s" % userid)
  q.display "Comments: %s\n" % count
  q.query("delete from longdescs where who = %s" % userid)
  q.display "  comments have been deleted\n"

  # check if spammer created bugs
  bugs = q.query("select bug_id from bugs, profiles where bugs.reporter = profiles.userid and profiles.userid = %s" % userid)
  q.display "Bugs: %s\n" % bugs.length
  if bugs.empty? then
    q.display "  spammer did not create any bugs\n"
  else
    q.display "\n"
    bugs.each do |bug|
      q.display "Bug #%s\n" % bug
      attach_ids = q.query("select attach_id from attachments where bug_id = %s" % bug)
      q.display "  attachments: %s\n" % attach_ids.length
      if not attach_ids.empty? then
        attach_str = attach_ids.join(",")
        q.query("delete from attachments where attach_ids in (%s)" %  attach_str)
        q.query("delete from attach_id where id in (%s)" % attach_str)
      end

      count = q.query("select count(*) from bugs_activity where bug_id = %s" % bug)
      q.display "  activity: %s\n" % count
      q.query("delete from bugs_activity where bug_id = %s" % bug)
      q.query("delete from bugs where bug_id = %s" % bug)
    end
    q.display "\nTotal bugs: %s\n" % bugs.length
  end

  # remove the spammer
  q.query("delete from profiles where userid = %s" % userid)
  q.display "\nRemoved spammer: %s\n" % spammer
end

def get_options(args)
  options = {}
  opts = OptionParser.new do |opts|
    opts.banner = "Usage: #{File.basename($0)} [options] <email_address>"
    opts.on("-?", "--help", "Show this usage statement") do |help|
      options['help'] = true
      puts opts
    end
    opts.on("-d", "--debug", "debug enabled (db queries are displayed)") do |d|
      options['debug'] = true
    end
    opts.on("-q", "--quiet", "only display errors") do |q|
      options['quiet'] = true
    end
    opts.on("-h", "--host=HOST", "host machine of mysql database") do |h|
      options['host'] = h
    end
    opts.on("-P", "--port=PORT", "database port") do |p|
      options['port'] = p
    end
    opts.on("-u", "--user=USER", "database user") do |u|
      options['user'] = u
    end
    opts.on("-p", "--password=PASSWORD", "database password") do |p|
      options['password'] = p
    end
    opts.on("-S", "--socket=SOCKET", "database socket") do |s|
      options['socket'] = s
    end
    opts.on("-D", "--database=DATABASE", "mysql logical database to use") do |d|
      options['database'] = d
    end
  end
  begin
    opts.parse!(args)
    raise "Missing Argument <email_address>" if args.empty? and not options['help']
    return options
  rescue Exception => e
    $stderr.puts e, "", opts
    exit 1
  end
end

def main
  options = get_options(ARGV)
  begin
    remove_spammer(options, ARGV[0]) unless options['help']
  rescue Exception => e
    $stderr.puts e
    exit 1
  end
end

main