class Inspec::Resources::MssqlSession

STABILITY: Experimental This resource needs further testing and refinement

This requires the `sqlcmd` tool available on platform @see docs.microsoft.com/en-us/sql/relational-databases/scripting/sqlcmd-use-the-utility @see docs.microsoft.com/en-us/sql/linux/sql-server-linux-connect-and-query-sqlcmd

Attributes

db_name[R]
host[R]
instance[R]
local_mode[R]
password[R]
port[R]
user[R]

Public Class Methods

new(opts = {}) click to toggle source
# File lib/inspec/resources/mssql_session.rb, line 36
def initialize(opts = {})
  @user = opts[:user]
  @password = opts[:password] || opts[:pass]
  if opts[:pass]
    Inspec.deprecate(:mssql_session_pass_option, "The mssql_session `pass` option is deprecated. Please use `password`.")
  end
  @local_mode = opts[:local_mode]
  unless local_mode?
    @host = opts[:host] || "localhost"
    @port = opts[:port]
  end
  @instance = opts[:instance]
  @db_name = opts[:db_name]

  # check if sqlcmd is available
  raise Inspec::Exceptions::ResourceSkipped, "sqlcmd is missing" unless inspec.command("sqlcmd").exist?
  # check that database is reachable
  raise Inspec::Exceptions::ResourceSkipped, "Can't connect to the MS SQL Server." unless test_connection
end

Public Instance Methods

query(q) click to toggle source
# File lib/inspec/resources/mssql_session.rb, line 56
def query(q) # rubocop:disable Metrics/PerceivedComplexity
  escaped_query = q.gsub(/\\/, "\\\\").gsub(/"/, '""').gsub(/\$/, '\\$')
  # surpress 'x rows affected' in SQLCMD with 'set nocount on;'
  cmd_string = "sqlcmd -Q \"set nocount on; #{escaped_query}\" -W -w 1024 -s ','"
  cmd_string += " -U '#{@user}' -P '#{@password}'" unless @user.nil? || @password.nil?
  cmd_string += " -d '#{@db_name}'" unless @db_name.nil?
  unless local_mode?
    if @port.nil?
      cmd_string += " -S '#{@host}"
    else
      cmd_string += " -S '#{@host},#{@port}"
    end
    if @instance.nil?
      cmd_string += "'"
    else
      cmd_string += "\\#{@instance}'"
    end
  end
  cmd = inspec.command(cmd_string)
  out = cmd.stdout + "\n" + cmd.stderr
  if cmd.exit_status != 0 || out =~ /Sqlcmd: Error/
    raise Inspec::Exceptions::ResourceFailed, "Could not execute the sql query #{out}"
  else
    DatabaseHelper::SQLQueryResult.new(cmd, parse_csv_result(cmd))
  end
end
to_s() click to toggle source
# File lib/inspec/resources/mssql_session.rb, line 83
def to_s
  "MSSQL session"
end

Private Instance Methods

local_mode?() click to toggle source
# File lib/inspec/resources/mssql_session.rb, line 89
def local_mode?
  !!@local_mode # rubocop:disable Style/DoubleNegation
end
parse_csv_result(cmd) click to toggle source
# File lib/inspec/resources/mssql_session.rb, line 97
def parse_csv_result(cmd)
  require "csv" unless defined?(CSV)
  table = CSV.parse(cmd.stdout, headers: true)

  # remove first row, since it will be a seperator line
  table.delete(0)

  # convert to hash
  headers = table.headers

  results = table.map do |row|
    res = {}
    headers.each do |header|
      res[header.downcase] = row[header] if header
    end
    Hashie::Mash.new(res)
  end
  results
end
test_connection() click to toggle source
# File lib/inspec/resources/mssql_session.rb, line 93
def test_connection
  !query("select getdate()").empty?
end