- URL:https://<featurelayer-url>/validateSQL
- Version Introduced:10.3
Description
Nota:
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.
Nota:
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:
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 expressionsql = 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]"
}
]
}