Validate SQL (Feature Service/Layer)

Description

NoteNote:

The validateSQL operation is only supported in ArcGIS Online hosted feature services.

The validateSQL operation validates an SQL-92 expression or WHERE clause.

The validateSQL operation ensures that an SQL-92 expression, such as one written by a user through a user interface, is correct before performing another operation that uses the expression. For example, validateSQL can be used to validate information that is subsequently passed in as part of the where parameter of the calculate operation.

validateSQL also prevents SQL injection. In addition, all table and field names used in the SQL expression or WHERE clause are validated to ensure they are valid tables and fields.

NoteNote:

To support the validateSQL operation across all feature service implementations, a new support property, supportsValidateSql, is added to the layer metadata and is set to true.

Request parameters

Parameter

Details

f

Description: The response format. The default response format is html.

Values: html | json

sql

Description: The SQL expression or WHERE clause to validate.

Syntax:

"sql": "sqlExpression"

Example:

{"sql" : "Population > 300000"}

sqlType

Description: Three SQL types are supported in validateSQL:

  • where (default)—Represents the custom WHERE clause the user can compose when querying a layer or using calculate.
  • expression—Represents an SQL-92 expression. Currently, expression is used as a default value expression when adding a new field or using the calculate API.
  • statement—Represents the full SQL-92 statement that can be passed directly to the database. No current ArcGIS REST API resource or operation supports using the full SQL-92 SELECT statement directly. It has been added to the validateSQL for completeness.

Values: where | expression | statement

Example:

"sqlType" : "where"

Validation error codes

When the SQL-92 expression is valid, isValidSQL : true is returned. However, the following error codes and descriptions are returned when an invalid SQL-92 expression is submitted:

Error

Error code

Error description

Success

3000

Success

NotSupported

3001

Sql expression is not supported.

SyntaxError

3002

Sql expression syntax error.

SemanticError

3004

Sql expression semantic error.

InvalidTableName

3007

Invalid table name.

InvalidFieldName

3008

Invalid field name.

UnsafeSQL

3009

Unsafe sql expression is not allowed.

Example usage

Example 1: Validate SQL-92 (date SQL-92 WHERE clause)

sql = " some_date < CURRENT_DATE "
sqlType = "where"

http://services.myserver.com/<orgid>/ArcGIS/rest/services/stdQuery_SQLSrvr/FeatureServer/0/validateSQL?sql=some_date < CURRENT_DATE&sqlType=where&f=html&token

Example 2: Validate SQL-92 (using the where sqlType)

Sql = CNTRY_NAME > 'L' and pop_cntry > 20000000
sqlType = where

http://services.myserver.com/<orgid>/ArcGIS/rest/services/stdQuery_SQLSrvr/FeatureServer/0/validateSQL?sql=CNTRY_NAME > 'L' and pop_cntry > 20000000&sqlType=where&f=html&token

Example 3: Validate sql 92 expression

sql = pop_cntry + 20000000
sqlType = expression

http://services.myserver.com/<orgid>/ArcGIS/rest/services/stdQuery_SQLSrvr/FeatureServer/0/validateSQL?sql=pop_cntry+%2B+20000000&sqlType=expression&f=html&token=

JSON response example (validate is successful)

{
  "isValidSQL" : true
}

JSON response example (validation error if some_date is not a valid field)

{
  "isValidSQL" : false,
  "validationErrors" : [
    {
      "errorCode" : 3008, 
      "description" : "Invalid field name [some_date]"
    } 
  ]
}

9/13/2017