Invoking APIs‎ > ‎GET‎ > ‎

Structured Filters

To protect the security of your data and prevent malicious persons from accessing data they are not supposed to have access to using SQL injectionsturn off regular filters and use structured filters.

Regular filters are pieces of SQL code that are sent directly to the database. They are not safe and are a frequent cause of unintentional data leaks. Think of regular filters as a prototyping tool: they are nice when you want to move fast, but not appropriate for production systems. Use regular filters only when you need maximum flexibility in filtering your data, for example:

.../rest/acme/myproj/v1/Customer?filter=balance<1000

For more information:

Named Filters

You can use created named parameter values in place of column attributes by using named filters.

Note: Each database has its own rules on how to handle mixed case and quotes around attribute names. The specific SQL database conventions define whether to surround column attribute names with double quotes ("name") or back-ticks (`name`) in the filter field.

The attribute names are the names of table columns or (if used with a resource) the names of resource attributes. In the case of resources, you can specify the name of resource attributes (recommended), but you can also specify column names.

A named filter can be a system filter or a user filter.




Define System Filters

You can do secure filtering, without having to predefine your own filters, using system 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, for example:

.../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. Filters that end with _uc and _or perform an 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 following lists the valid values for parameters:

 Column type Valid values Example
 All columns null
Note: Only valid for equal and notequal.
 sysfilter=equal(name: null)
 String 'yadda yadda' sysfilter=like(comments: '%yadda%')
 Boolean true, false
Note: 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)
Note: Date, time, and timestamp use ISO-8601 format.
 sysfilter=less(ts: timestamp(2015-11-07T13:15:00))

If you need more flexibility, define a user filter.

Define User Filters

Define user filters when you need more power than what system filters afford you. User filters give you complete, unfettered access to all the power of the underlying database.

After you have defined the user filter, you can test it in the 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)

For more information about structured sorts, see Structured Sorts.

  1. Go to Create, API Properties, Filters tab.
    The following image shows a user filter on the Create, API Properties, Filters tab:
  2. Click Add.
  3. Complete the following fields and then save your changes:

Filter name

The name used by ?userfilter=

Resources

A list of full qualified resources, tables, and views.

Optional: Yes

Filter

The list of column names and attribute values (within curly braces). The filter (other than the attribute values within curly braces) must be valid SQL for your database. 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. A typical symptom of an unquoted name is a SQL error complaining about a non-existent table or column.
For example:

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}

For more information about your database requirements, see your database documentation.

Access Sub-Resources

Using a named filter places the attribute column names with the selected resource base table. Sub-resources are nested tables inside resources. Access the sub-resource using 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()