Docs‎ > ‎REST APIs‎ > ‎Filters‎ > ‎

Structured filters

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

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

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 Creator, select API Properties, and make sure the Allow free-form filters option is not checked.

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:

 Filter Examples
 equal(colName: value) .../Customer?sysfilter=equal(name: 'Jones')
 .../Customer?sysfilter=equal(name: 'Jones', zipCode: '90210')
Note: this is the only system filter that accepts more than one column/value pair.
 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)
 more(colNamevalue) .../Customer?sysfilter=more(balance: 1000)
 moreorequal(colNamevalue) .../Customer?sysfilter=moreequal(order_date: timestamp(2015-10-28T13:00:00.000-0800))
 like(colNamevalue) .../Customer?sysfilter=like(name: 'Jo_n Sm%th')
 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)

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

TODO: time(), date, timestamp(), data types

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.

order_type = {order_type} and order_date >= {start_date} and order_date <= {end_date}

?userfilter=newsForPeriod(news_type:'U',start_date:timestamp(2015-10-27T12:00:00.000-0800),end_date:timestamp(2015-10-28T23:59:59.999-0800))



Comments