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