Docs‎ > ‎CA Live API Creator‎ > ‎Create‎ > ‎

Structured Sorts

By default, when you issue a GET request, you can specify an order argument to specify how the returned data should be sorted, e.g.:

.../Customer?order=name desc

There is, however, a serious problem with regular sorts: they aren't safe. This is because a regular sort 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 sorts 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 sorts, and use structured sorts. To turn off regular sorts, open the API Creator, select API Properties, and make sure the Disallow free-form filters option is checked.

There are two kinds of named sorts: system sorts and user sorts.


System sorts

For the vast majority of sorts, you can use the built-in mechanism:

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, you simply 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 may also specify asc_uc, or desc_uc to make the sort be done as if all values were upper-case.   This can be combined with the same column a second time to control specific handling of upper/lower case mixed values.

For example, given a table with the 6 rows
  MAX,Max,max,DAVID,David,david

You would get 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 that you may place multiple columns in a single sysorder, or multiple sysorders (each a separate URL parameter) each with one or more columns.

User sorts

If you need a more complex sort than what the system sorts allow, then you need to define a user sort.



This done in the API Creator, under API Properties, tab 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