Creating a Filtering API with Dynamic SQL Query in WSO2 EI

In a service oriented architecture, organizations often have to allow other services or departments to read the data in the database in a controlled manner. Exposing the Data as a Service or REST API is recommended rather than giving direct access to the database. When Data as a Service / API is exposed, access to services can be easily controlled. WSO2 Enterprise Integrator (EI) has out-of-the-box support to expose Data as a Service for many data stores.

When exposing the Data as a Service / API, there might be a requirement to provide a Filter / Search API with filtering options to avoid performance issues. This blog post explains how to expose the data stored in a MySQL database as a search API with filtering options.

What’s the use case?

Exposing data stored in a database as a REST API and the REST API should provide the option to filter the data based on columns of the table. Here only “AND” operation for filtering is considered. For example, the following search / filter API call should do the SQL query in the corresponding database table and return the result:

REST API call: /context/resourcepath?query1=”value”& query 2”=value”…. ”& query-n”=value”

SQL Query: SELECT * FROM TABLE WHERE COLUMN1=”VALUE” AND COLUMN2=”VALUE”….AND COLUMNn=”VALUE”

What’s the solution?

WSO2 Enterprise Integrator (EI) has a Data Service feature which can be used to expose the data stored in the database as a service. It’s possible to create a Data Service which accepts Dynamic SQL query as input. This feature can be used to filter the data based on different column attribute values.

But exposing this Data Service directly to outsiders is dangerous because it’s subject to SQL injection. Therefore, Data Service should be exposed to outsiders in a controlling manner, validating the input to the Data Service should be validated before calling the service. This can be achieved by creating a REST API in WSO2 EI which act as the proxy for Data Service. Request coming to the REST API will be validated first, then from the valid request parameters dynamic query for the Data Service will be constructed and Data Service will be invoked.

Further exposing the Data Service via local transport and accessing from REST API via local transport would mitigate the Data Service from any attacks. This blog post will only explain the functional requirement and its implementation. More details on using local transport can be found in WSO2 documentation.

To run the example in this WSO2 Tutorial you must have the following prerequisites in place:

• WSO2 EI
• MySQL Database
• MySQL Driver
• WSO2 Integration Studio

Setting up WSO2 Enterprise Integrator & Database

• Download and Copy MySQL driver to <EI_HOME>/lib
• Let’s create a database to store the Office Branch details of a company. Insert some sample data.

Create API / service to Query Data

1. Create the following projects in Integration Studio:

• ESB Project (FilterAPI-ESB) – Holds ESB Artifacts
• Data Service Project ( FilterAPI-DS) – Holds Data Service Artifact
• Data Source Project ( FilterAPI-DataSource) – Holds Data Source Artifact
• Composite Carbon Application (FilterAPI-CAPP) – Holds the artifacts to be deployed

2. Create a Data Source to connect the database:

• Right-click on FilterAPI-DataSource > New > Data Source

• Switch to the source view and configure the data source

3. Create a Data Service with dynamic query as input

• Right-click FilterAPI-DS > New > Data Service

• Select the data source Type “Carbon Data Source” and select the data source name created in the previous step.

• Once the Service file is created, switch to source view and create the service to query the table using dynamic query.The service below contains following steps.

1. Define Data source
2. Define Query to get Office Details

2.1. Define SQL to get Office Details

2.2. Define Input parameter mapping

2.3. Define Out Put parameter mapping

3. Define operation

3.1 Select the queryID to map the operation

3.2 Define the operation input parameters

Note: In input mapping set sqlType. Value as QUERY_STRING for filterQuery. Here filterQuery is SQL WHERE clause statement. E.g WHERE OfficeCode=1

4. Test Data Services with Dynamic filter SQL query

• Select the Data Source and Data Service crated in the Composite Carbon Application (FilterAPI-CAPP) and generate the car application.
• Deploy the car application to the WSO2 EI Server
• Find the deployed service under the services section and select the Try this Service option

• Now provide a WHERE clause statement to the filterQuery parameter. For example, “WHERE officecode=1’. Service will filter the data based on the condition in the WHERE clause and returns the response.

Now we have a service which accepts SQL where clause condition and filter the data based on that.

5. Create REST API
   The service created in the previous step is subject to SQL injection, since the caller can provide any SQL statement to the input parameter. Therefore, we must ensure this Service is not exposed to outsiders or untrusted clients.To expose the service to outsiders, this service should be exposed via a secured API. The API should accept column values as query parameters and parameters should be validated for SQL injection. The API will construct the Dynamic WHERE clause based on the valid parameters it receives.

• Right-click on FilterAPI-ESB -> New -> Rest API
• Give a name and context to the API
• Create the logic to validate the query parameters, construct dynamic where clause, create data service payload with the constructed input parameter, call the data service, transform the message to JSON and return to the caller.

Note: In the above API only two table columns are considered for filtering and they are mapped to query parameters. This can be extended to any number of columns.

Table Column	Query Param
OfficeCode	code
City	city

The above API doesn’t have any validation logic added. Validation can be added before constructing the dynamic query. If validation fails, error message can be returned to the caller.

6. Test the REST API

• Select all artifacts to composite application and create the .car file.
• Deploy the .car file in WSO2 EI
• Invoke the API with different dynamic query parameters to filter the data. For example:
  • Get offices by city name: GET http://localhost:8280/office?city=Norwich
  • Get offices by office code: GET http://localhost:8280/office?code=1
  • Get offices by city name and office code: GET http://localhost:8280/office?code=1&city=Norwich

Conclusion

The data service feature in WSO2 Enterprise Integrator is a powerful feature to expose the data from different data sources. Along with the WSO2 EI mediation feature, data can be exposed in a control manager for consumer applications.

Yenlo is the leading, global, multi-technology integration specialist in the field of API-management, Integration technology and Identity Management. Known for our strong focus on best-of-breed hybrid and cloud-based iPaaS technologies. Yenlo is the product leader and multi-award winner in WSO2, Boomi, MuleSoft and Microsoft Azure technologies and offers best-of-breed solutions from multiple leading integration vendors.