Create a dynamic/filtered query service in WSO2 DSS.

Sometimes we have requirement in which we have to call a database query at run time. For example, we have an employee table and need to get details from this table with some condition like “where emp_last_name=? and address=?”. In above scenario if we don’t know the how many condition cab be there at run time, DSS filtered query can be used.

DSS file:

<data name="DynamicDS" transports="http https local">
   <config enableOData="false" id="DynamicQueryEmp">
      <property name="driverClassName">com.mysql.jdbc.Driver</property>
      <property name="url">jdbc:mysql://localhost:3306/emp</property>
      <property name="username">root</property>
      <property name="password">root</property>
   </config>
   <query id="DynaminQuery" useConfig="DynamicQueryEmp">
      <sql>select id, name, address from emp :filterQuery</sql>
      <result defaultNamespace="http://shriwithjava.blogspot.co.nz/" element="employees" rowName="employee">
         <element column="id" name="id" xsdType="xs:string"/>
         <element column="name" name="name" xsdType="xs:string"/>
         <element column="address" name="address" xsdType="xs:string"/>
      </result>
      <param name="filterQuery" sqlType="QUERY_STRING"/>
   </query>
   <operation name="getEmployees">
      <call-query href="DynaminQuery">
         <with-param name="filterQuery" query-param="filterQuery"/>
      </call-query>
   </operation>
</data> 

In this DSS file we can use any database condition and pass it to DSS as parameter

How to implement NTLM security in WSO2 ESB

Unfortunately, WSO2 does not provide any plugins or mediator directly to implement NTLM security. However, this can be done by using the custom mediator. You can write a java class with this security handler and call it from WSO2 container.

In order to write a Class mediator, you can follow this blog and put the custom jar into the WSO2 lib folder.  

package poc.ntlm;

import java.io.IOException;

import org.apache.commons.httpclient.HttpClient;
import org.apache.commons.httpclient.HttpException;
import org.apache.commons.httpclient.NTCredentials;
import org.apache.commons.httpclient.auth.AuthScope;
import org.apache.commons.httpclient.methods.GetMethod;

public class NLTMSecurity {

 public static void main(String[] args) throws HttpException, IOException {
  System.out.println("started");
  String result = invokeService();
  System.out.println("output  : " + result);

 }

 public static String invokeService()
   throws HttpException, IOException {
  String responseString = null;
  try {
   HttpClient client = new HttpClient();

   String URL = "http://XXX.XXX.XXX/XX/XXX/2011/OrganizationData.svc/ListSet?$select=ListId,ListName,StateCode";
   GetMethod getMethod = new GetMethod(URL);
   NTCredentials credentials = new NTCredentials("USER_NAME", "PASSWORD", "HOST_NAME", "DOMAIN");
   client.getState().setCredentials(new AuthScope(null, -1, null),
     credentials);
   int status = client.executeMethod(getMethod);

   System.out.println("Status : " + status);

   responseString = getMethod.getResponseBodyAsString();

   System.out.println("responseString : " + responseString);

  } catch (Exception e) {
   System.out.println(e);
  }

  return responseString;

 }

}

You can set this response and the HTTP code in the WSO2 ESB container.

import org.apache.synapse.MessageContext;
import org.apache.synapse.mediators.AbstractMediator;


public class NTLMSecurityMediator extends AbstractMediator { 

 public boolean mediate(MessageContext context) { 

 context.setProperty("Response", responseString );
      
context.setProperty("HTTP_STATUS", status );      return true;
      
    
  
 }
}

How to apply Certificate in WSO2 ESB

In our previous blog, we have converted the .pfx file to .cer file. In this blog we will see how to apply certificate in WSO2 ESB.

Until certificate is added, we can't connect to the 3rd party services if this certificate is implemented there. This can be done by using very simple below steps.

You have to reach to the WSO2 ESB (wso2esb-4.9.0) security folder and execute the below steps.


C:\>cd C:\Work\WSO2\wso2esb-4.9.0\repository\resources\security

C:\Work\WSO2\wso2esb-4.9.0\repository\resources\security>keytool -importcert -file C:\Users\ShriK\Desktop\nzgpp\certificate\nzgpp.cer -keystore client-truststore.jks -alias nzgpp

• Use "wso2carbon" password if asked.

Enter keystore password:
Owner: CN=XXXXX.jqdev.local
Issuer: CN=XXXXX.jqdev.local
Serial number: 7c74XXXXXXXXX1b12ac2808bc
Valid from: Thu Aug 03 13:29:13 NZST 2017 until: Fri Aug 03 12:00:00 NZST 2018
Certificate fingerprints:
         MD5:  5C:4B:E3:AD:57:E9:6F:08:76:95:6D:60:A3:04:2A:EB
         SHA1: AE:58:76:XX:XX:9A:64:84:55:62:XX:17:XX:A7:9A:54:1B:E9:C4:14
         SHA256: F5:53:66:05:E3:41:CF:65:E7:9D:14:1F:1D:81:39:D7:96:XX:90:26:51:XX:A7:9A:54:1B:E9:27:CD:14:F9
         Signature algorithm name: SHA1withRSA
         Version: 3

Extensions:

#1: ObjectId: 2.5.29.37 Criticality=false
ExtendedKeyUsages [
  serverAuth
]

#2: ObjectId: 2.5.29.15 Criticality=false
KeyUsage [
  Key_Encipherment
  Data_Encipherment
]

• Type "yes" and enter

Trust this certificate? [no]:  yes
Certificate was added to keystore



Once above highlighted message is printed, means your certificate is successfully installed. you shouyld be getting expected results. 

Convert .PFX file to .cer file

In this logs I will show that how to convert .pfx file to .cer file. In order to do that you should have a .pfx file which I have taken it from Microsoft CRM where this certificate has been installed.

• Search “internet option” in start and open it.

• Click on the Content tab

• Click on the Certificate Button

• Click on Import button and click Next

• Select .pfx file

• Click Open and OK.

In above steps we have successfully imported the .pfx file and now need to covert it to .cer file.

• Follow the same steps above and reach to the certificate location.

• Select certificate and click on export
• Click Next and again click next in next screen

• Click Next

• Give the file name and the location and save it.

Now in next blog we will see how to install this certificate in WSO2 ESB in order to connect to CRM services.