Validate SQL (Feature Service/Layer)

• URL:https://<featurelayer-url>/validateSQL
  
• Version Introduced:10.3

Description

Note:

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.

Note:

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"

https://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

https://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

https://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]"
    } 
  ]
}