Invoking APIs‎ > ‎GET‎ > ‎

Structured Sorts

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

Regular sorts 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 sorts as a prototyping tool: they are nice when you want to move fast, but not appropriate for production systems.

By default, when you issue a GET request, you can specify how the returned data should be sorted by specifying an order argument, for example:

.../Customer?order=name desc

For more information about SQL injections, see the SQL injection wikipedia page.

Turn Off Regular Sorts

In API Creator, on the Create, API Properties, Settings tab, select the Disallow free-form filters and sorts checkbox and save your changes. By default, this checkbox is cleared.

When free-form filters and sorts are disallowed (regular sorts are turned off) and a request specifies an order argument in the URL, the request fails.

Named Sorts

The following kinds of named sorts are available:
  • System sorts
  • User sorts

Define System Sorts

For the majority of sorts, you can use system sorts.

The following system sorts are available:

sysorder=(expression[,expression])

espression is columnName[:modifier]

modifier is asc, desc, asc_uc, desc_uc, null_first, null_last 

Example:

.../Customer?sysorder=(name:null_first,balance:desc )

To specify the sort order, add a sysorder parameter, with a list of attributes, optionally followed with a colon and either asc or desc (if unspecified, asc is assumed).

For String (TEXT) columns, you can also specify asc_uc, or desc_uc to sort as if all values were upper-case. You can control specific handling of upper/lower case mixed values by combining this with the same column a second time.

For example, given a table with the six rows:

MAX,Max,max,DAVID,David,david

You receive the following results:

 Sort Result
 sysorder=(name:asc_uc,name:desc) david,David,DAVID,max,Max,MAX
 sysorder=(name:asc_uc,name:desc) DAVID,David,david,MAX,Max,max
 sysorder=(name:desc_uc,name:desc) MAX,Max,max,DAVID,David,david
 sysorder=(name:desc_uc,name:desc) max,Max,MAX,david,David,David

Note: You can place multiple columns in a single sysorder, or multiple sysorders (each a separate URL parameter) each with one or more columns.

Define User Sorts

If you need a more complex sort than what the system sorts allow, define a user sort. On the Create, API Properties, Sorts tab, click API Properties, Sorts.

If you define a user sort named MySort with the following value:

dayofweek(order_date) desc

Then you can use that sort in a query with:

.../PurchaseOrder?userorder=MySort