Structured Filters

By default, requests with specified filters in the endpoint URL (regular filters) cannot access your data and require that requests define named filters.
lac53
By default, requests with specified filters in the endpoint URL (regular filters) cannot access your data and require that requests define named 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.
Named filters help to protect the security of your data. Named filters prevent malicious persons from using using SQL injections to access data that they should not have access.
The following image shows how 
Layer7 Live API Creator
 handles named filters:
 CA Technologies 
Allow regular filters only if you need maximum flexibility in filtering your data, for example:
.../rest/acme/myproj/v1/Customer?filter=balance<1000
For more information about how to turn on regular filters (allow regular filters), see API Properties.
In this article:
 
sysfilter
 uses the following syntax:
sysfilter=[sysfiltername[modifier](expression[,expression]) - multiple expressions AND by default
The syntax for 
expression
 is ([
columnName]: [value]
). You can handle spaces or special characters in 
colName
 and 
value
 using single or double quotes. You can use the JavaScript style, including 
\uNNNN
 encoding, 
\n
, and 
\r
.
The system filters have the following variations:
  • Form an 
    AND
     condition by combining system filters, for example:
    .../Customer?sysfilter=equal(name: 'Jones')&sysfilter=less(balance: 1000)
    By default, 
    Layer7 Live API Creator
     applies 
    AND
     to expressions, for example:
    .../Customer?sysfilter=equal(name: 'Jones', zipCode: '90210')
  • Form
     an uppercase on the parameters by adding 
    _uc
     to the end of the system filter name.
  • Form
     an 
    OR
     condition between the parameters by adding 
    _or
     to the end of the system filter name.
  • Form
     an uppercase on the parameters and an 
    OR
     condition between the parameters by adding 
    _uc
     
    _or
     to the end of the system filter name.
You can use the following system filters:
System filter name
Description
Example
 
equal(
colName
value
 
 
You can add one or more parameters.
.../Customer?sysfilter=equal(name: 'Jones')
.../Customer?sysfilter=equal(name: 'Jones', zipCode: '90210')
 
equal_uc(colName: value)
 
Converts the text value to uppercase.
.../Customer?sysfilter=equal_uc(name: 'Jones', name: 'Smith')
 
equal_uc_or(colName: value)
 
Converts the text value to uppercase and OR between multiple expressions.
.../Customer?sysfilter=equal_uc_or(name: 'Jones', name: 'Smith')
 
notequal
(
colName
value
 
 
 
.../Customer?sysfilter=notequal(zipCode: '94501')
.../Customer?sysfilter=notequal(zipCode: null)
 
less
(
colName
value
 )
 
.../Customer?sysfilter=less(balance: 1000)
 
lessequal
(
colName
value
 )
 
.../Customer?sysfilter=lessequal(balance: 1000)
 
greater
(
colName
value
 )
 
.../Customer?sysfilter=greater(balance: 1000)
 
greaterequal
(
colName
value
 )
 
.../Customer?sysfilter=greaterequal(order_date: timestamp(2015-10-28T13:00:00.000-0800))
 
like
(
colName
value
 )
 
You can add one or more parameters.
.../Customer?sysfilter=like(name: 'Jo_n Sm%th')
 
notlike
(
colName
value
 )
 
.../Customer?sysfilter=notlike(name: '%Smith%')
OR System Filters
You can use the following
OR
system filters:
OR System filter name
Description
Examples
equal_or(colName: value)
Applies OR to the expression. You can add one or more parameters.
.../Customer?sysfilter=equal_or(name: 'Jones', name: 'Smith')
 
notequal_or(
colName
value
 )
 
.../Customer?sysfilter=notequal_or(zipCode: '94501')
.../Customer?sysfilter=notequal_or(zipCode: null)
 
less_or(
colName
value
 )
 
.../Customer?sysfilter=less_or(balance: 1000)
 
lessequal_or(
colName
value
 )
 
.../Customer?sysfilter=lessequal_or(balance: 1000)
 
greater_or(
colName
value
 )
 
.../Customer?sysfilter=greater_or(balance: 1000)
 
greaterequal_or(
colName
value
 )
 
.../Customer?sysfilter=greaterequal_or(order_date: timestamp(2015-10-28T13:00:00.000-0800)
 
like_or(
colName
value
 )
 
You can add one or more parameters.
.../Customer?sysfilter=like_or(name: 'Sm%th', name: 'Jo%es')
 
notlike_or(
colName
value
 )
 
.../Customer?sysfilter=notlike_or(name: '%Smith%')
Parameter Values
You can enter the following values for parameters:
Column type
Valid values
Example
All columns
null
Only valid for the 
equal
 and 
notequal
 system filter names.
sysfilter=equal(name: null)
String
'yadda yadda'
sysfilter=like(comments: '%yadda%')
Boolean
true or false
Only valid for the 
equal
 and 
notequal
 system filter names
sysfilter=equal(disabled: true, offline:true)
Number
123.456
0
1000
sysfilter=greater(balance: 123.456)
Date
date(2015-11-07)
Date uses ISO-8601 format.
sysfilter=lessequal(day:date(2015-11-07))
Time
time(13:15:00)
time(13:15:00.000Z)
Time uses ISO-8601 format.
sysfilter=greater(when: time(14:00:00))
Timestamp
timestamp(2015-11-07T13:15:00)
timestamp(2015-11-07T13:15:00.000000-0800)
Timestamp uses ISO-8601 format.
sysfilter=less(ts: timestamp(2015-11-07T13:15:00))
Define User Filters
Mask or hide the internal column and SQL syntax and prevent users from accessing your data using regular filters (to prevent SQL injection). Get complete, unfettered access to all the power of the underlying database. Define user filters when you need a more complex filter than what system filters afford you.
User filters (
userfilter
) have the following syntax:
userfilter=[FilterName]([expression])
The syntax for 
expression
 is [
columnName][:modifier]
.
Example:
 
.../v1/[Resource]?userfilter=[FilterName]([expression])
Follow these steps:
 
  1. With your API open, in the Create section, click 
    API Properties
    .
    The API Properties page appears.
  2. Click the 
    Filters
     tab.
    A list of user filters display in the list.
  3. Above the list of user filters, click 
    Add
    .
  4. Complete the following fields, and then save your changes:
Filter name
 
The name that the 
?userfilter=
 uses.
 
Resources
 
A comma-separated list of resources, subresources, tables, and views.
Optional:
 Yes
Example:
 
demo:PurchaseOrder, AccountSummary, AccountSummary.Orders_List
Filter
 
The list of column names and attribute values (within curly braces) or, to avoid the exposure of your database columns to the client, named parameter values. The attribute names are the names of columns or, if used with a resource, the names of resource attributes. In the case of resources, you can specify resource attribute names (recommended) or you can specify column names.
The filter, other than the attribute values within curly braces, must be valid SQL for your database. Each database has its own requirements about how to handle mixed case, special characters, and spaces in column names and attribute values. For example, surrounding the names and values with double quotes ("column_name") or back-ticks (`column_name`). A typical symptom of unquoted column names and attribute values is a SQL error complaining about a non-existent table or column.
For more information about your database requirements, see your database documentation.
Example:
 
paid = {paid_flag} and amount_total >= {amount_total}
Example for databases that require quoting mixed-case column names:
"Paid" = {paid_flag} and "Amount_Total" >= {amount_total}
The user filter is defined.
Test User Filters in the REST Lab
The following example defines the 
CurrentPaidOrders
 user filter with the 
paid_flag
 and 
amount_total
 named parameters:
http://localhost:8080/rest/el-local/demo/v1/demo%3APurchaseOrder?userfilter=CurrentPaidOrders(paid_flag:1,amount_total:1000)
For more information about the REST Lab, see Test your API Using the REST Lab.
Apply Filters to Subresources
You can apply filters to subresources using the name of the nested resource using the following syntax:
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()
filter.CustomerBusObject.Orders=paid=false
filter..Orders=paid=false – exactly the same as above (note the two dots)
User filters must include the subresource name.
For more information about subresources, see Manage Subresources.