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

ParameterDetails

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:

ErrorError codeError 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
1
2
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
1
2
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
1
2
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
1
2
3
{
  "isValidSQL" : true
}

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

Use dark colors for code blocksCopy
1
2
3
4
5
6
7
8
9
{
  "isValidSQL" : false,
  "validationErrors" : [
    {
      "errorCode" : 3008,
      "description" : "Invalid field name [some_date]"
    }
  ]
}

Your browser is no longer supported. Please upgrade your browser for the best experience. See our browser deprecation post for more details.