Docs‎ > ‎CA Live API Creator‎ > ‎Create‎ > ‎

Structured filters

When you need maximum flexibility in filtering your data, you can use regular filters, e.g.:


There is, however, a serious problem with regular filters: they aren't safe. This is because a regular filter is basically a piece of SQL code that gets sent directly to the database. In most cases, that's fine, but this mechanism can be used by malicious persons to access data they're not supposed to have access to. That's called a SQL injection, and it's a frequent cause of unintentional data leaks.

Regular filters should be thought of as a prototyping tool: they're quite nice when you want to move fast, but not appropriate for production systems.

So, if security is a concern, you need to turn off regular filters, and use structured filters. You can turn off regular filter, go to the API Properties/Settings tab, and make sure the Disallow free-form filters option is checked.

NOTE: 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.

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 always available:

sysfilter=[sysfiltername[modifier](expression[,expression]) - multiple expressions will AND by default
modifier can be _or for multiple expressions will use OR instead of AND
modifier can be _uc which will UPPER a text column
modifier can be _uc_or which will apply both UPPER and OR between multiple expressions       
expression syntax is (columnName: value) 

 System Filter Name Examples
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 will be 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))
Multiple parameters OK
 .../Customer?sysfilter=like(name: 'Jo_n Sm%th')
Multiple parameters OK
 .../Customer?sysfilter=like_or(name: 'Sm%th', name: 'Jo%es')
 notlike(colNamevalue) .../Customer?sysfilter=notlike(name: '%Smith%')

System filters can be combined, in which case they form an AND condition:

.../Customer?sysfilter=equal(name: 'Jones')&sysfilter=less(balance: 1000)

As the name implies, the filters that end with _or will perform an OR between their parameters. If the filters that end with _uc will perform an upper case on the parameters. If the two are combined, 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))
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
 sysfilter=greater(balance: 123.456)
 Date date(2015-11-07) sysfilter=lessequal(day:date(2015-11-07))
 Time time(13:15:00)
 sysfilter=greater(when: time(14:00:00))
 Timestamp timestamp(2015-11-07T13:15:00)
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.

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}
Note that, as mentioned before, 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, use mixed case, etc... 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.

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


Accessing Sub-resources

Using a named filter will generally place 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 below.

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