Defining a Dynamic SQL Query

Dynamic SQL query support allows you to change SQL queries (e.g., defining additional conditions in the SQL) in the runtime without changing the data service configuration. For this to work, you must specify required SQL query statements (e.g., with WHERE clause) as a QUERY_STRING data type. These statements will be directed to the final SQL query in the runtime.

Dynamic query support can lead to SQL injection attacks. Therefore, we recommend that the clients validate the values set to QUERY_STRING at runtime.

The QUERY_STRING data type is available as an SQL type when creating Input Mappings for queries. For example,

You can add the SQL query using the mapping name:

A sample configuration for the data service is shown below:

<data name="DynamicQuerySample" serviceNamespace="http://ws.wso2.org/dataservice/samples/rdbms_sample">
   <config id="default">
      <property name="driverClassName">org.h2.Driver</property>
      <property name="url">jdbc:h2:file:./samples/database/DATA_SERV_SAMP</property>
      <property name="username">wso2ds</property>
      <property name="password">wso2ds</property>
      <property name="minIdle">1</property>
      <property name="maxActive">10</property>
      <property name="autoCommit">false</property>
   </config>
   <query id="employeesSQL" useConfig="default">
      <sql>select * from Employees :filterQuery</sql>
      <result element="employees" rowName="employee">
         <element column="lastName" name="last-name" xsdType="string"/>
         <element column="firstName" name="first-name" xsdType="string"/>
         <element column="email" name="email" xsdType="string"/>
         <element column="salary" name="salary" xsdType="double"/>
      </result>
      <param name="filterQuery" sqlType="QUERY_STRING"/>
   </query>
   <query id="customerInCountrySQL" useConfig="default">
      <sql>select * from Customers where country = :country :filter</sql>
      <result element="customer-addresses" rowName="customer-address">
         <element column="customerNumber" name="customer-number" xsdType="integer"/>
         <element column="contactLastName" name="contact-last-name" xsdType="string"/>
         <element column="contactFirstName" name="contact-first-name" xsdType="string"/>
         <element column="addressLine1" name="address-line1" xsdType="string"/>
         <element column="addressLine2" name="address-line2" xsdType="string"/>
         <element column="city" name="city" xsdType="string"/>
         <element column="state" name="state" xsdType="string"/>
         <element column="postalCode" name="postal-code" xsdType="string"/>
         <element column="country" name="country" xsdType="string"/>
      </result>
      <param name="country" sqlType="STRING"/>
      <param name="filter" sqlType="QUERY_STRING"/>
   </query>
   <query id="insertUpdateQuery" useConfig="default">
      <sql>:query</sql>
      <param name="query" sqlType="QUERY_STRING"/>
   </query>
   <operation name="getEmployees">
      <call-query href="employeesSQL">
         <with-param name="filterQuery" query-param="filterQuery"/>
      </call-query>
   </operation>
   <operation name="getCustomersInCountry">
      <call-query href="customerInCountrySQL">
         <with-param name="country" query-param="country"/>
         <with-param name="filter" query-param="filter"/>
      </call-query>
   </operation>
   <operation name="insertUpdateOp">
      <call-query href="insertUpdateQuery">
         <with-param name="query" query-param="query"/>
      </call-query>
   </operation>
</data>