How to make a program that can open mysql

I'm developing an InSpec control that runs CIS compliance commands. While working on MySQL, I'm stuck here:

Execute the following SQL statement to determine the value of datadir:

show variables where variable_name = 'datadir';

I need to extract the output from the above command and reuse it in the next command:

ls -l <THE OUTPUT OF THE PREVIOUS COMMAND>/.. | egrep "^d[r|w|x]{3}------\s*.\s*mysql\s*mysql\s*\d*.*mysql"

The problem is that the first command is an SQL Request and the second command is a terminal command.

How can I put both of them (after getting the output of the first command and put it in the second one) in an InSpec control like the following:

control "mysql1" do
    impact 1.0
    title "Use dedicated Least Privileged Account for MySQL Daemon/Service"
    desc "May reduce the impact of a MySQL-born vulnerability"
    describe command ('ps -ef |e grep "^mysql.*$"') do
    its('stdout') { should match ''}
    end
end

Thank you for your help @Matt

I've read your answer and found it really helpful, except the last block of code : Does

egrep "^d[r|w|x]{3}------\s*.\s*mysql\s*mysql\s*\d*.*mysql"

mean

it { expect(subject).to_not be_owned_by 'mysql' }
it { expect(subject).to_not be_grouped_into 'mysql' }
it { expect(subject).to_not be_executable_by 'mysql' }

Plus I did try all of the blocks you wrote previously and none of them did work.. And yes, I'm using linux 16.04

Solution

You can extract the output of the SQL request with the following method:

command('mysql -u <user> -p -e "show variables where variable_name = \'datadir\'"').stdout.split(' ')

The mysql -u <user> -p -e part is necessary to execute the SQL query from a Linux command. If you are using Window, you will probably need to make use of sqlcmd instead. This allows the SQL query to execute successfully with the command method.

The reason the command method works here is because it is a custom RSpec type (implicitly therefore also a class constructor in the sense that Ruby has constructors) that will execute locally or remotely on the tested system. The .stdout method is a member of the class to capture the stdout of the command. .split will ensure the output variables are stored in a whitespace-delimited array.

Now we can use it in the next command like so:

# store array of variables
variables = command('mysql -u <user> -p -e "show variables where variable_name = \'datadir\'"').stdout.split(' ')
# use array in command
variables.each do |variable|
  describe command("ls -l #{variable}/.. | egrep \"^d[r|w|x]{3}------\s*.\s*mysql\s*mysql\s*\d*.*mysql\"") do
    its('stdout') { should match ''}
  end
end

Above we iterate through the array of variables captured in the SQL query and test it in the describe command() RSpec test. A better way to execute this test would be to test the stdout of the command in the matcher and not the egrep. Doing that and cleaning up the match method:

# store array of variables
variables = command('mysql -u <user> -p -e "show variables where variable_name = \'datadir\'"').stdout.split(' ')
# use array in command
variables.each do |variable|
  describe command("ls -l #{variable}/..") do
    its('stdout') { should_not match(/^d[r|w|x]{3}------\s*.\s*mysql\s*mysql\s*\d*.*mysql/)}
  end
end

Updating to non-deprecated RSpec matchers and fixing the invoking of the stdout method as a string instead of a symbol we arrive at:

# store array of variables
variables = command('mysql -u <user> -p -e "show variables where variable_name = \'datadir\'"').stdout.split(' ')
# use array in command
variables.each do |variable|
  describe command("ls -l #{variable}/..") do
    its(:stdout) { is_expected.to_not match(/^d[r|w|x]{3}------\s*.\s*mysql\s*mysql\s*\d*.*mysql/)}
  end
end

Another improvement we can make is to use a better suited file type and the permissions matchers instead of raw commands. This helps for platform-independent testing:

# store array of variables
variables = command('mysql -u <user> -p -e "show variables where variable_name = \'datadir\'"').stdout.split(' ')
# use array in file type
variables.each do |variable|
  describe file("#{variable}/..") do
    # check permissions
    it { expect(subject).to_not be_owned_by 'mysql' }
    it { expect(subject).to_not be_grouped_into 'mysql' }
    it { expect(subject).to_not be_executable_by 'mysql' }
  end
end

I understand there was a good bit here to implement the functionality you are looking for and many fixes and improvements as well, so be sure to examine the code and explanations closely to understand everything I did here.