ArcGIS Server includes a security option that forces developers to use standardized SQL queries when working with map, feature, image, and WFS services through REST or SOAP. This makes it easier for developers and applications to query ArcGIS Server services and helps prevent SQL injection attacks. Standardized queries are enforced by default but can be disabled by the server administrator.
Standardized queries
When standardized queries are enabled, ArcGIS Server checks for standard syntax and does not allow database-specific functions and syntax. If you're an application developer currently using database-specific functions and syntax, you must update the where clauses in your application's code to use common SQL syntax supported by ArcGIS. Alternatively, you can disable ArcGIS Server from checking for standardized queries.
Limitations of standardized queries
Standardized queries are applied to the entire ArcGIS Server site; they cannot be enabled for some services and disabled for others.
Standardized queries are not supported on joins between different workspaces. Additionally, database tables accessed through an OLE DB connection file are not supported. If your service data contains these sources, you must use alternative methods for referencing your data.
Subqueries as a where clause, for example, POP_2010 = (SELECT min(POP_2010)) FROM counties, are not supported.
Write a standardized query
The table at the end of this topic describes which SQL functions are supported in ArcGIS Server. You can use this table to help you understand which SQL functions can be used to generate standardized queries that can be used against map, feature, image, and WFS services in your applications.
Determine if standardized queries are being used by ArcGIS Server
As mentioned above, standardized queries are enabled by default and apply to all map, feature, image, and WFS services in the ArcGIS Server site. However, a server administrator can verify that standardized queries are being used by signing in to the ArcGIS Server Administrator Directory and browsing to system > properties > update. If no system property is displayed in the System Properties dialog box or {"standardizedQueries": "true"} is present, standardized queries are turned on.
Alternatively, administrators, application developers, and clients can verify that standardized queries are being used by accessing a service through the ArcGIS Server Services Directory and reviewing the Use Standardized Queries property. For map, feature, and WFS services, the property can be reviewed by accessing a specific layer or table in the service, for example, https://gisserver.domain.com:6443/arcgis/rest/services/folder/service/service type/0. For image services, the property is available at the service endpoint, for example, https://gisserver.domain.com:6443/arcgis/rest/services/folder/service/ImageServer.
For instructions on how to access the Administrator Directory or to learn how to disable standardized queries, see the next section of this topic.
Disable standardized queries
If you need to use database-specific where clause statements in your application, you can disable standardized queries by accessing the ArcGIS Server Administrator Directory. To do so, follow the instructions below.
Caution:
By disabling this security option, your site becomes more vulnerable to SQL injection attacks.
Note:
Disabling standardized queries is not supported for hosted feature services in ArcGIS Enterprise.
- Open the Administrator Directory and sign in with a user that has administrative permissions to your site.
The Administrator Directory is typically available at https://gisserver.domain.com:6443/arcgis/admin.
- Click system > properties > update.
- On the Operation - update page, enter the following string into the System Properties dialog box:
{"standardizedQueries": "false"} - Click Update.
- Restart ArcGIS Server.
Your site is now configured to allow users to send requests to map, feature, image, and WFS services using nonstandardized queries. To reenable standardized queries, repeat the above instructions but set the standardizedQueries property to true.
Supported SQL functions in ArcGIS Server
The following list shows which SQL functions are supported by ArcGIS Server and the syntax for each. When the following functions and syntax are used in your applications, ArcGIS Server converts them to conform to the specification of the database used by the service.
These functions apply to ArcGIS Server web services, not hosted web layers.
| Function type | Function | Description | Example |
|---|---|---|---|
Date | CURRENT_DATE | Returns the current date in the session time zone. Note:The hosted session time zone is always UTC. | Datefield < CURRENT_DATE |
CURRENT_TIME | Returns the current time in the session time one. Note:The hosted session time zone is always UTC. | Timestampfield < CURRENT_TIME | |
CURRENT_TIMESTAMP | Returns the current local time. Note:The hosted session time zone is always UTC. | Timestampfield < CURRENT_TIMESTAMP | |
EXTRACT(extract_field FROM extract_source) | Returns the extract_field portion of the extract_source. The extract_field argument can be one of the following keywords: YEAR, MONTH, DAY, HOUR, MINUTE, or SECOND. | Search all rows from the month of November: EXTRACT(MONTH FROM Datefield) = 11 | |
Math | ABS(numeric_exp) | Returns the absolute (positive) value of the specified numeric expression. | ABS(-99)=90 |
ACOS(numeric_exp) | Returns the arccosine of numeric_exp as an angle, expressed in radians. | ||
ASIN(numeric_exp) | Returns the arcsine of numeric_exp as an angle, expressed in radians. | ||
ATAN(numeric_exp) | Returns the arctangent of numeric_exp as an angle, expressed in radians. | ||
CEILING(numeric_exp) | Returns the smallest integer greater than, or equal to, the specified numeric expression. | Find all rows where the ceiling integer of numeric_exp is equal to a desired value: CEILING(ratings)=90 | |
COS(numeric_exp) | Returns the cosine of numeric_exp as an angle, expressed in radians. | ||
COSH(numeric_exp) | Returns the hyperbolic cosine of numeric_exp as an angle, expressed in radians. | ||
FLOOR(numeric_exp) | Returns the largest integer less than or equal to the specified numeric expression. | Find all rows where the floor integer of numeric_exp is equal to a desired value: FLOOR(ratings)=75 | |
LOG(float_exp) | Returns the natural logarithm of the specified float expression. | ||
LOG10(float_exp) | Returns the base-10 logarithm of the specified float expression. | ||
MOD(integer_exp1, integer_exp2) | Returns the remainder of integer_exp1 divided by integer_exp2. | Find all rows where integer_exp1 is an even number: MOD(userid,2)=0 | |
POWER(numeric_exp, integer_exp) | Returns the value of the specified expression to the specified power. | POWER(Numericfield, 2) = 16 | |
ROUND(numeric_exp, integer_exp) | Returns a numeric value, rounded to the specified length or precision. | ||
SIGN(numeric_exp) | Returns an indicator of the mathematical sign of numeric_exp. Examples: 0 for zero, 1 for positive, -1 for negative. | Search all rows where the numeric_exp is a negative number: SIGN(elevation)=-1 | |
SIN(numeric_exp) | Returns the sine of numeric_exp as an angle, expressed in radians. | ||
SINH(numeric_exp) | Returns the hyperbolic sine of numeric_exp as an angle, expressed in radians. | ||
TAN(numeric_exp) | Returns the tangent of numeric_exp as an angle, expressed in radians. | ||
TANH(numeric_exp) | Returns the hyperbolic tangent of numeric_exp as an angle, expressed in radians. | ||
TRUNCATE(numeric_exp, integer_exp) | Returns a numeric_exp truncated to integer_exp places to the right of the decimal point without rounding the value. | TRUNCATE(0.5463111445,3)=0.546 | |
Aggregate | AVG(numeric_exp, integer_exp) | Returns the calculated average of the values in numeric_exp. | outStatistics=[{"statisticType": "avg","onStatisticField": "pop1997","outStatisticFieldName": "avg_pop1997"}] |
COUNT(string_exp OR ASTERISK) | Returns the number of rows that have a string_exp. | outStatistics=[{"statisticType": "count","onStatisticField": "p_females","outStatisticFieldName": "count_p_females"}] | |
MAX(numeric_exp) MAX(string_exp) MAX(datetime_exp) | Returns the largest value in numeric_exp, string_exp, or datetime_exp. | Find the maximum value across all numeric_exp rows: outStatistics=[{"statisticType": "max","onStatisticField": "p_males","outStatisticFieldName": "max_p_males"}] | |
MIN(numeric_exp) MIN(numeric_exp) MIN(numeric_exp) | Returns the smallest value in numeric_exp, string_exp, or datetime_exp. | Find the minium value across all numeric_exp rows: outStatistics=[{"statisticType": "min","onStatisticField": "pop1990","outStatisticFieldName": "min_pop1990"}] | |
STDDEV(numeric_exp) | Returns the standard deviation of the values in numeric_exp. | Find the standard deviation value across all numeric_exp rows: outStatistics=[{"statisticType": "stddev","onStatisticField": "eval_score","outStatisticFieldName": "stddev_eval_score"}] | |
SUM(numeric_exp) | Returns the sum of values in numeric_exp. | Find the sum of values across all numeric_exp rows: outStatistics=[{"statisticType": "sum","onStatisticField": "p_females","outStatisticFieldName": "sum_p_females"}] | |
VAR(numeric_exp) | Returns the variance of values in numeric_exp. | Find the variance value across all numeric_exp rows: outStatistics=[{"statisticType": "var","onStatisticField": "pop1980","outStatisticFieldName": "var_pop1980"}] | |
String | CHAR_LENGTH(string_exp) | Returns the length in characters of the input string. | Search all rows where the length of string_exp is greater than 10: CHAR_LENGTH(string_exp) > 10 |
CONCAT(string_exp1, string_exp2) | Returns a string that is the result of concatenating two or more string values. | Output a concatenated version of string_exp1 and string_exp2 with a concatenation character: OutFields=CONCAT(id,CONCAT(I-,name)) | |
LOWER(string_exp) | Returns a string equal to string_exp with all upper case characters converted to lowercase. The default locale is the locale of the database. | Search all rows where the value is the desired string, regardless of case: LOWER(string_field) = "lowered_string" | |
POSITION(cahracter_exp1 IN character_exp2) | Returns the integer position of the first character_exp1 in character_exp2. | Search all rows where the first character is 'I': POSITION('I' IN state_name) = 1 | |
SUBSTRING(string_exp FROM start FOR length) SUBSTRING(string_exp, integer_exp_start, integer_exp_length) | Returns a character string that is derived from string_exp, beginning at the character position specified by start for length characters. | Search all rows where the first two characters from values in Stringfield are Ch: SUBSTRING(Stringfield FROM 1 FOR 2)='Ch' | |
TRIM(BOTH|LEADING|TRAILING trim_character FROM string_exp) | Returns the string_exp with the trim_character removed from the leading, trailing, or both ends of the string. | Output string_field without the undesired prefix and suffix: OutFields=TRIM(BOTH '__' FROM string_exp) | |
UPPER(string_exp) | Returns a string equal to that in string_exp with all lowercase characters converted to uppercase. The default locale is the locale of the database. | UPPER(name) = "TEXAS" | |
Other | CAST(expression AS target_data_type) | Returns an expression converted into target_data_type. | Search all rows where the expression converted into a specified data type is equal to the desired value: CAST(parcel_id AS INT)=38456 |
COALESCE(expression1,expression2,...) | Returns the first non-null value from a list of expressions. | Search all rows where the first non-null value is the specified value: COALESCE(preferred_name, nickname, first_name) = 'MyName' | |
CURRENT_USER | Returns the currently logged in user. Note:This does not return the current database user. | Search all rows where the currently logged in user is the sole worker. workerfield=CURRENT_USERSearch all rows where the currently logged in user is listed as a worker, regardless of if there are other workers. position(CURRENT_USER in workerfield) > 0 | |
NULLIF(expression1,expression2) | Compares two expressions. If they are equal, returns NULL. I they are not equal, returns expression1. | Outputs the result of NULLIF comparisons between expression1 and expression2: outFields=NULLIF(units_sold, unit_sell_goal) |