Encrypt values from Excel

Hi,

Im doing some API testing with sensitive data coming from excel.

Is there a way to encrypt all the variables (i.e. all the data from excel that i maped in the test suite). ?

I did try to do a TestListener but the Object TestCaseContext only has .getTestCaseVariables(). Is there a way that i can use something like “setTextCaseVariables”?

The only think that i know is working is todo:

  1. create GlobalVariables for each variable that i need
  2. in TestListener using an custom keyword that read the data from excel and then instanciate the GlobalVariables. In this case i will use them in HTTPBody. But doing this is not practical because we do not take advantage from Katalon.

We dont want to save any report in the local computers like the values that are maped into each variables. I did try to reduce the Katalon report. But even doing this the folder “Reports” always save a file “testCaseBinding” that contains the values from excel.

Steps i did to reduce the katalon report:

  1. Project Settings->Execution->Disable log
  2. File log.properties as “ERROR” or “FATAL”
  3. Removed “Katalon Basic Report plugin”.

Other solution is do a script where we delete all the files from …/Reports/report1etc… when a test case ended.

Thank you.

hi,

better would be have secret keys from your company or customer side
public_key.pem and private_key.pem

with this piece of java code you are able to encrypt/decrypt your data

import static com.kms.katalon.core.checkpoint.CheckpointFactory.findCheckpoint
import static com.kms.katalon.core.testcase.TestCaseFactory.findTestCase
import static com.kms.katalon.core.testdata.TestDataFactory.findTestData
import static com.kms.katalon.core.testobject.ObjectRepository.findTestObject
import static com.kms.katalon.core.testobject.ObjectRepository.findWindowsObject
import com.kms.katalon.core.checkpoint.Checkpoint as Checkpoint
import com.kms.katalon.core.cucumber.keyword.CucumberBuiltinKeywords as CucumberKW
import com.kms.katalon.core.mobile.keyword.MobileBuiltInKeywords as Mobile
import com.kms.katalon.core.model.FailureHandling as FailureHandling
import com.kms.katalon.core.testcase.TestCase as TestCase
import com.kms.katalon.core.testdata.TestData as TestData
import com.kms.katalon.core.testobject.TestObject as TestObject
import com.kms.katalon.core.webservice.keyword.WSBuiltInKeywords as WS
import com.kms.katalon.core.webui.keyword.WebUiBuiltInKeywords as WebUI
import com.kms.katalon.core.windows.keyword.WindowsBuiltinKeywords as Windows
import internal.GlobalVariable as GlobalVariable

import org.apache.commons.codec.binary.Base64;

import javax.crypto.Cipher;
import javax.crypto.spec.IvParameterSpec;
import javax.crypto.spec.SecretKeySpec;


String key = "JavasEncryptDemo"; // 128 bit key
String randomVector = "RandomJavaVector"; // 16 bytes IV

String enCrypted = EncryptorDemo.encrypt(key, randomVector, "Anything you want to encrypt!")
println enCrypted

String deCrypted = EncryptorDemo.decrypt(key, randomVector, enCrypted);
println deCrypted


public class EncryptorDemo {
	
		public static String encrypt(String key, String randomVector, String value) {
			try {
				IvParameterSpec iv = new IvParameterSpec(randomVector.getBytes("UTF-8"));
				SecretKeySpec skeySpec = new SecretKeySpec(key.getBytes("UTF-8"), "AES");
				Cipher cipher = Cipher.getInstance("AES/CBC/PKCS5PADDING");
				cipher.init(Cipher.ENCRYPT_MODE, skeySpec, iv);
				byte[] encrypted = cipher.doFinal(value.getBytes());
				//System.out.println("encrypted text: "  + Base64.encodeBase64String(encrypted));
				return Base64.encodeBase64String(encrypted);
			} catch (Exception e) {
				e.printStackTrace();
			}
			return null;
		}
	
		public static String decrypt(String key, String randomVector, String encrypted) {
			try {
				IvParameterSpec iv = new IvParameterSpec(randomVector.getBytes("UTF-8"));
				SecretKeySpec skeySpec = new SecretKeySpec(key.getBytes("UTF-8"), "AES");
				Cipher cipher = Cipher.getInstance("AES/CBC/PKCS5PADDING");
				cipher.init(Cipher.DECRYPT_MODE, skeySpec, iv);
				byte[] originalText = cipher.doFinal(Base64.decodeBase64(encrypted));
				//System.out.println("decrypted text: "  + new String(originalText));
				return new String(originalText);
			} catch (Exception e) {
				e.printStackTrace();
			}
			return null;
		}
	}

NRUPJIJSgXU7Un0WmooAdqU2KM6TpO+dA/QMubVD8a8=
Anything you want to encrypt!

Hi @Timo_Kuisma1,

Yes i did something similar in Java but used RSA type.

But the problem is that in order to use a Test Suite you need to mapping excel values with the Test Case variables. Doing this programmatically in Katalon is not a good practice. Im I wrong?

I as wondering if there’s is a fast way in Katalon to do this type of approach.

hi,

“Doing this programmatically in Katalon is not a good practice”
why not? I guess it’s good choice, read excel values to List Array and encrypt there in a loop

“I as wondering if there’s is a fast way in Katalon to do this type of approach”
not sure, maybe some support guys could be better to answer that

why not? I guess it’s good choice, read excel values to List Array and encrypt there in a loop

Because when you are using an Test Suite, you can change the test case variables unless they are Global Variables. You only have access to them from the object TestCaseContext with the method .getTestCaseVariables(). If there was an method that could do something like testcaseContext.setTestCaseVariables(map).

Other thing is that you will need to create method do read an excel file, the decrypt encrypt methods etc. Once you are using Katalon the thing is to take advantage from the application and not “recreate” the excel reading, the mapping values, your private Test Suite methods etc…

I think i find a way by:

  1. Create custom keyword: TestCase.read(testCaseID)
    Here we have access to the specific test case by its id. So now we can change the defaultValue for each variable that exist this test case.

  2. Create another custome keyword that will simulate an Test Suite.
    Like TestSuite.startTestCase(testCaseID). In this keyword we can call the upper method “testCase read”

  3. Finally we create a Test Case Main.
    In this test case we call TestSuite.startTestCase(testCaseID).

But I hope that someone from Katalon’s support team proposes a more elegant solution.

Hi @DummyAutomation

I don’t precisely know if this could help solve your problem. There exists an annotation @BeforeTestDataBindToTestCase for test listeners that allow you to change the value of Test Case Bindings.

Test Listener

class NewTestListener {
	@BeforeTestDataBindToTestCase
	def variableBindingConverter(TestSuiteContext tsContext, TestCaseContext tcContext, Map variableBinding) {
		println tcContext.getTestCaseId()
		variableBinding['aVariable'] = encryptedText(variableBinding['aVariable']);
	}
	
	private String encryptedText(String input) {
		return input + " is encrypted";
	}
}

It is assumed that Test Case A is in Test Suite A and has a test case variable aVariable which is binded to a Test Data variable.

When you execute Test Suite A:

2020-03-31 19:05:15.579 INFO  c.k.katalon.core.main.TestSuiteExecutor  - os = Mac OS X 64bit
2020-03-31 19:05:15.581 INFO  c.k.katalon.core.main.TestSuiteExecutor  - hostAddress = 192.168.0.104
2020-03-31 19:05:15.582 INFO  c.k.katalon.core.main.TestSuiteExecutor  - katalonVersion = 7.3.0.${buildNumber}
Test Cases/Test Case A
2020-03-31 19:05:16.151 INFO  c.k.katalon.core.main.TestCaseExecutor   - --------------------
2020-03-31 19:05:16.151 INFO  c.k.katalon.core.main.TestCaseExecutor   - START Test Cases/Test Case A
2020-03-31 19:05:16.191 INFO  c.k.katalon.core.main.TestCaseExecutor   - aVariable = Value from Test data is encrypted
2020-03-31 19:05:16.732 DEBUG testcase.Test Case A                     - 1: println(aVariable)
Value from Test data is encrypted
2020-03-31 19:05:16.742 INFO  c.k.katalon.core.main.TestCaseExecutor   - END Test Cases/Test Case A

Let me know if this helps.

1 Like

Hi @ThanhTo,

This really help me a lot.

I did not know that this anotation exsists because when we create a new Litetener there is no option for “BeforeTestDataBindToTestCase”.

Now i found all the anotation that are possible by doing writing @Before then click "control button + space button.

Now with this solution we can make a more elegant approach to this problem.

Thank you :smiley:

1 Like

Hi @DummyAutomation

I am curious about your use case, may I ask:

  • What type of information do you consider sensitive when testing API ?
  • In your request, do you pass the encrypted data into the body and send it to the server, or is there a decryption step before-hand.
  • An alternative solution would be to prepare a separate excel sheet with encrypted data, is that right ?

Feel free to describe your use case, it would help us develop understanding of our users’ use cases.

Hi, @ThanhTo

  1. The information is from a Bank entity. In dev environment, there is no problem. But in a pre-production and a production environment the information must be as less visible as possible.

  2. In request the info goes decrypted, but before the binding the data is encrypted and in the end of each test case the variable binding goes to empty string.

  3. Yes the idea is to have encrypted values into an excel file.

One of the reasons of encrypted values is because the Katalon Report log and another is that the bank do not want that the data is visible. Even if the testing is in a bank network.

I hope that this explanation was useful.

2 Likes