Escaping single quotes inside functions calls

[manmorjim]

Context

When we define a query which contains single quotes like:

q = '''
SELECT cartodb_id, the_geom, classification,
case 
  when classification = 2 then 'Peligroso'
  when classification = 1 then 'Grave'
  when classification = 0 then 'Sin síntomas'
  else 'N/A'
end as classification_str 
FROM {} 
WHERE classification > 0 AND classification < 3
'''.format(table)

It generates a syntax error in the SQL API when is passed by parameter to a SQL function. For example if we use the query above to publish a new map:

viz = Map(
  Layer(q, style=s),
  basemap=basemaps.darkmatter,
  viewport={'zoom': 10.5, 'lat': 41.3947, 'lng': 2.078}
)
viz.publish(name='cartotest', if_exists='replace', password='secret')

This code does a request to the SQL API with a function call CDB_QueryTablesText:

{
"q": "SELECT CDB_QueryTablesText('\nSELECT cartodb_id, the_geom, classification,\ncase \n  when classification = 2 then 'Peligroso'\n  when classification = 1 then 'Grave'\n  when classification = 0 then 'Sin síntomas'\n  else 'N/A'\nend as classification_str \nFROM gencat_covid19_selfdiagnosis_points \nWHERE classification > 0 AND classification < 3\n') as tables"
}

The SQL API returns a 400 error due to unbalanced quotes:

{
    "error": [
        "syntax error at or near \"Peligroso\""
    ]
}

A workaround would be using Dollar-Quoted String Constants $q$ in the query:

q = '''
SELECT cartodb_id, the_geom, classification,
case 
  when classification = 2 then $q$Peligroso$q$
  when classification = 1 then $q$Grave$q$
  when classification = 0 then $q$Sin síntomas$q$
  else $q$N/A$q$
end as classification_str 
FROM {} 
WHERE classification > 0 AND classification < 3
'''.format(table)

Suggestion

The Dollar-Quoted solution could be a little tricky for general users. By this reason a suggestion would be escaping queries passed by parameter instead.

For example, in the context_manager implement something like:

  query = 'SELECT CDB_QueryTablesText($q${}$q$) as tables'.format(query)

With this, the SQL API call would be:

{
"q": "SELECT CDB_QueryTablesText($q$\nSELECT cartodb_id, the_geom, classification,\ncase \n  when classification = 2 then 'Peligroso'\n  when classification = 1 then 'Grave'\n  when classification = 0 then 'Sin síntomas'\n  else 'N/A'\nend as classification_str \nFROM gencat_covid19_selfdiagnosis_points \nWHERE classification > 0 AND classification < 3\n$q$) as tables"
}

which has a correct syntax.

[cmongut]

Hi @manmorjim,

thank you very much for giving enough detail and propose a nice solution, we will consider it for the next release :)

[ztephm]

@FerGolden when this is implemented, it will solve this Support issue: https://github.com/CartoDB/support/issues/2476

[Jesus89]

Fixed in #1619. Released in CF 1.0.3