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.