Validate SQL (Feature Service/Layer)

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

Description

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.

Request parameters

Parameter Details

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"`

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)

Use dark colors for code blocksCopy
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 )

Use dark colors for code blocksCopy
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

Use dark colors for code blocksCopy
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)

Use dark colors for code blocksCopy
{
  "isValidSQL" : true
}

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

Use dark colors for code blocksCopy
{
  "isValidSQL" : false,
  "validationErrors" : [
    {
      "errorCode" : 3008,
      "description" : "Invalid field name [some_date]"
    }
  ]
}