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.
Example Service is as follows.
Note : Since column names are also dynamic, in the output mapping all possible column names are defined with optional parameter.
There you can invoke the "DynamicQueryTest" operation as follows.
Rest operation can be invoked as follows.
https://172.17.0.1:9443/services/DynamicQueryTest.SecureHTTPEndpoint/DynamicTestRes?columns=LastName&table=Employees&whereclause=where EMPLOYEENUMBER > 1
Dynamic query support can lead to SQL injection attacks. Therefore, it is recommended that the clients validate the values set to QUERY_STRING at runtime.
References : WSO2 DSS - Dynamic SQL Query
Example Service is as follows.
<data name="DynamicQueryTest" transports="http https local"> <config enableOData="false" id="TestDB"> <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> </config> <query id="DynamicQuery" useConfig="TestDB"> <sql>Select :columns from :table :whereclause</sql> <result element="EmployeeData" rowName="Employee"> <element column="EMPLOYEENUMBER" name="EMPLOYEENUMBER" optional="true" xsdType="string"/> <element column="LASTNAME" name="LASTNAME" optional="true" xsdType="string"/> <element column="FIRSTNAME" name="FIRSTNAME" optional="true" xsdType="string"/> <element column="EXTENSION" name="EXTENSION" optional="true" xsdType="string"/> </result> <param name="columns" sqlType="QUERY_STRING"/> <param name="table" sqlType="QUERY_STRING"/> <param name="whereclause" sqlType="QUERY_STRING"/> </query> <operation name="DynamicQueryTest"> <call-query href="DynamicQuery"> <with-param name="columns" query-param="columns"/> <with-param name="table" query-param="table"/> <with-param name="whereclause" query-param="whereclause"/> </call-query> </operation> <resource method="GET" path="DynamicTestRes"> <call-query href="DynamicQuery"> <with-param name="columns" query-param="columns"/> <with-param name="table" query-param="table"/> <with-param name="whereclause" query-param="whereclause"/> </call-query> </resource> </data>
Note : Since column names are also dynamic, in the output mapping all possible column names are defined with optional parameter.
There you can invoke the "DynamicQueryTest" operation as follows.
<body> <p:DynamicQueryTest xmlns:p="http://ws.wso2.org/dataservice"> <!--Exactly 1 occurrence--> <p:columns>EMPLOYEENUMBER,LASTNAME</p:columns> <!--Exactly 1 occurrence--> <p:table>EMPLOYEES</p:table> <!--Exactly 1 occurrence--> <p:whereclause>where EMPLOYEENUMBER > 1</p:whereclause> </p:DynamicQueryTest> </body>
Rest operation can be invoked as follows.
https://172.17.0.1:9443/services/DynamicQueryTest.SecureHTTPEndpoint/DynamicTestRes?columns=LastName&table=Employees&whereclause=where EMPLOYEENUMBER > 1
Dynamic query support can lead to SQL injection attacks. Therefore, it is recommended that the clients validate the values set to QUERY_STRING at runtime.
References : WSO2 DSS - Dynamic SQL Query
No comments:
Post a Comment