Docs‎ > ‎API Creator‎ > ‎Create your API Project‎ > ‎API Properties‎ > ‎

Structured Filters

When you need maximum flexibility in filtering your data, you can use regular filters, for example:
.../rest/acme/myproj/v1/Customer?filter=balance<1000

Regular filters are not safe. Regular filters are pieces of SQL code that are sent directly to the database. Malicious persons can access data they're not supposed to have access to using a SQL injection.

Regular filters are a frequent cause of unintentional data leaks. Think of regular filters as a prototyping tool: they're quite nice when you want to move fast, but not appropriate for production systems. To protect the security of your data, turn off regular filters and use structured filters.

Turn Off Regular Filters

In API Creator, select Create, API Properties, Settings tab, and then select the Disallow free-form filters checkbox. Requests that specified a filter in the URL fail.

Named Filters

The use of named filters allows creation of named parameter values to be used in place of column attributes. However, each database has its own rules on how to handle mixed case and quotes around attribute names. The column attribute names may need to be in double quotes ("name"), or back-ticks (`name`) depending on the specific SQL database conventions.

There are two kinds of named filters: system filters and user filters.




Use System Filters

System filters provide an easy way to do secure filtering, without having to pre-define your own filters.

The following system filters are available:
sysfilter=[sysfiltername[modifier](expression[,expression]) - multiple expressions AND by default
modifier can be _or for multiple expressions. Uses OR instead of AND.
modifier can be _uc which UPPERs a text column
modifier can be _uc_or which applies both UPPER and OR between multiple expressions
expression syntax is (columnName: value)

 System Filter Name Examples
equal(colNamevalue)
Multiple parameters OK -by default AND is applied to each expression
 .../Customer?sysfilter=equal(name: 'Jones')
 .../Customer?sysfilter=equal(name: 'Jones', zipCode: '90210')
equal_or(colName: value)
Multiple parameters OK - OR is applied 
 .../Customer?sysfilter=equal_or(name: 'Jones', name: 'Smith')
equal_uc_or(colName: value)
The text value is converted to upper case
 .../Customer?sysfilter=equal_uc_or(name: 'Jones', name: 'Smith')
 notequal(colNamevalue) .../Customer?sysfilter=notequal(zipCode: '94501')
 .../Customer?sysfilter=notequal(zipCode: null)
 less(colNamevalue) .../Customer?sysfilter=less(balance: 1000)
 lessequal(colNamevalue) .../Customer?sysfilter=lessequal(balance: 1000)
 greater(colNamevalue) .../Customer?sysfilter=greater(balance: 1000)
 greaterequal(colNamevalue) .../Customer?sysfilter=greaterequal(order_date: timestamp(2015-10-28T13:00:00.000-0800))
 like(colNamevalue)
Multiple parameters OK
 .../Customer?sysfilter=like(name: 'Jo_n Sm%th')
 like_or(colNamevalue)
Multiple parameters OK
 .../Customer?sysfilter=like_or(name: 'Sm%th', name: 'Jo%es')
 notlike(colNamevalue) .../Customer?sysfilter=notlike(name: '%Smith%')

You can form an AND condition by combining system filters:
.../Customer?sysfilter=equal(name: 'Jones')&sysfilter=less(balance: 1000)

Filters that end with _or perform an OR between their parameters. Filters that end with _uc perform an upper case on the parameters. If you combine the two, you can have both the _uc_or to give you upper case and an or between the parameters:
 System Filter OR Examples
 equal_or(colName: value)
Multiple parameters OK
 .../Customer?sysfilter=equal_or(name: 'Jones', name: 'Smith')
 notequal_or(colNamevalue) .../Customer?sysfilter=notequal_or(zipCode: '94501')
 .../Customer?sysfilter=notequal_or(zipCode: null)
 less_or(colNamevalue) .../Customer?sysfilter=less_or(balance: 1000)
 lessequal_or(colNamevalue) .../Customer?sysfilter=lessequal_or(balance: 1000)
 greater_or(colNamevalue) .../Customer?sysfilter=greater_or(balance: 1000)
 greaterequal_or(colNamevalue) .../Customer?sysfilter=greaterequal_or(order_date: timestamp(2015-10-28T13:00:00.000-0800))
 like_or(colNamevalue)
Multiple parameters OK
 .../Customer?sysfilter=like_or(name: 'Sm%th', name: 'Jo%es')
 notlike_or(colNamevalue) .../Customer?sysfilter=notlike_or(name: '%Smith%')


The valid values for parameters are:
 Column type Valid values Example
 All columns null
Only valid for equal and notequal
 sysfilter=equal(name: null)
 String 'yadda yadda' sysfilter=like(comments: '%yadda%')
 Boolean true, false
Only valid for equal and notequal
 sysfilter=equal(disabled: true, offline:true)
 Number 123.456
  0
 1000
 sysfilter=greater(balance: 123.456)
 Date date(2015-11-07) sysfilter=lessequal(day:date(2015-11-07))
 Time time(13:15:00)
 time(13:15:00.000Z)
 sysfilter=greater(when: time(14:00:00))
 Timestamp timestamp(2015-11-07T13:15:00)
 timestamp(2015-11-07T13:15:00.000000-0800)
Date, time and timestamp use ISO-8601 format
 sysfilter=less(ts: timestamp(2015-11-07T13:15:00))

If you need more flexibility, then you'll need to define a user filter.

Define User Filters

Sometimes, you need more power than what system filters afford you. That's when you define user filters, which give you complete, unfettered access to all the power of the underlying database. Use the API Properties navigation and click on filter/sorts (below).
  • filter Name: the name used by ?userfilter=
  • Description: free form text
  • Resources: [optional] a list of full qualified resources, tables, views 
  • Filter : This is the list of column names and {attribute values}  e.g. paid = {paid_flag} and amount_total >= {amount_total} -- or, if your database requires quoting mixed-case column names: "Paid" = {paid_flag} and "Amount_Total" >= {amount_total}
The filter must be valid SQL for your database (other than the parameters in curly braces, of course). In particular, certain databases may require that you quote certain names, such as table names and column names that contain spaces, special characters, and use mixed case. Consult your database documentation for details. A typical symptom of an unquoted name would be a SQL error complaining about a non-existent table or column.

The following image shows a user filter:

Try it yourself in REST Lab using the Demo table PurchaseOrder:

http://localhost:8080/rest/el-local/demo/v1/demo%3APurchaseOrder?userfilter=CurrentPaidOrders(paid_flag:1,amount_total:1000)

Access Sub-Resources

Using a named filter places the attribute column names with the selected resource base table. If you need to access a sub-resource (a nested table inside a resource), then you can use the name of the nested resource, as shown in the following snippet:
filter.CustomerBusObject.Orders=paid=false
filter..Orders=paid=false – exactly the same as above (note the two dots)

sysfilter.CustomerBusObject.Orders=equal(paid:true) -- attribute must be the same case as the schema
sysfilter..Orders=equal(paid:true)

userfilter.CustomerBusObject.Orders=paidOrders()
userfilter..Orders=paidOrders()