JS API Query URL Parameters unable to handle apostrophes

luckachi

Using this example: https://community.esri.com/t5/arcgis-api-for-javascript/passing-url-parameters-to-query-feature-layers/td-p/284882

I pass the species name via the parameter 'cname' (ie: cname=Alewife) It seems to work just fine with names that have parentheses (cname=Phragmites+%28Invasive%29) and dashes (cname=Woodland+forget-me-not) but it doesn't seem to query correctly when an apostrophe appear in the name. I've tried a few different string replaces, splitting etc but nothing seems to be working.

For example, in the console log I have the expr variable being written out and that reads:

 

Q_NAME = 'Baby's breath'

 

and in my ArcGIS Server Manager I am receiving the following error message:

 

An invalid where clause or definition expression has been requested: "Q_NAME = 'Baby's breath'"

 

but when I change the expression to: 

 

var expr = 'Q_NAME  = "' + species + '"';

 

If receive the following error message: 

 

Geodatabase error: Underlying DBMS error [GIS.Layer][STATE_ID = 0].

 

I don't quite know what I am doing wrong as the species variable is printing out correctly in the console.log but is not querying the feature layer correctly.

jcarlson

In a query, 'string' is a string, but "string" is a column named string. For that reason, single and double-quotes are not interchangeable when constructing a query statement.

To escape a single quote in SQL, you simply give it twice, i.e., 'Baby''s breath'. That's two single-quote characters, not a double-quote. I put it in monospace font to make it clearer.

Which poses a problem when the table stores it with one, but a simple replace of ' → '' should do the trick.

To demonstrate that the query functions properly with two single-quotes, here's a query of my county's parcels, with the query statement owner_name like '%''%'.

You'll see that the query successfully evaluates and returns all parcels with a single-quote somewhere in the name.

{
 "exceededTransferLimit": false,
 "features": [
  {
   "attributes": {
    "owner_name": "PATRICK J & NANCY M O'CONNELL"
   }
  },
  {
   "attributes": {
    "owner_name": "RANDALL ROBINSON OBRA '93 TRUST NORTHERN TRUST CO GRDN & TR"
   }
  },
...
]}