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-laye...

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 · Accepted solution]

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"
   }
  },
...
]}

[luckachi]

You are awesome @jcarlson  - thank you! 

[MatthewDriscoll]

This is some really good information!  I am surprised this has not tripped me up too.