Sql.Query API and quotes inline SELECT statement

Hi @emre (and scripting guru’s)

I have some simple scripting which breaks the sql.Query API helper function. Basically if the string below called productname has any embedded single quotes or double quotes the Helper breaks.

I have tried using the double single quote as advised in SQL work around but it seems it in the parser within sql.Query.
I have also used double quotes to encompass the qry command.
I have also used escape() and unescape().

If you have an embedded quote or double quote there is no workaround I think?

 var qry = "SELECT [CustomTags] FROM [MenuItems] WHERE [Name]='"+productname+"'";
 var r = sql.Query(qry).First;

Some advice please.

I am fairly sure this was discussed with QMcKay once. Would have to dig it up. I am not sure this is an issue as in something is broken… but will leave the topic for now just in case.

Why the quotes like that?[quote=“pauln, post:1, topic:10091”]

Yea I tried searching but little comes up - see what @QMcKay or Emre might say. The issue here is product names are always going to have a single quote in them like M and M's :pensive:

EDIT: Sorry kendash I have pasted the correct syntax now! The original was from accidental testing.

The result should read:
SELECT [CustomTags] FROM [MenuItems] WHERE [Name]='Product Code'

SELECT [CustomTags] FROM [MenuItems] WHERE [Name]='M and M's' - breaks right.

Here is an example of a working implementation:

function getusers(name){

var qry = "SELECT e.[Name] FROM [Entities] e JOIN [EntityTypes] et on et.[Id]=e.[EntityTypeId] WHERE et.[Name] = '"+name+"' ORDER BY e.[Name]";

var r = sql.Query(qry).First;

return r;


Have a crack at changing your name to Jessie’s and see if it works?

hmm ok one second let me test that.

Works fine.

So something is wrong with yours…

Ah what? Crap let me change mine to invert the Quotes <-> Double Quotes and see if that works. That will mean Double Quotes will be taboo.

Your selecting Custom Tags but they are in JSON format in that column…


[{"TN":"Recipe","TV":"No Info"},{"TN":" Recipe","TV":"hjj"},{"TN":"Cost Price","TV":"1.00"},{"TN":"Trade Price","TV":"1.50"}]```

Correct. What is the value of name sorry as that is where the issue is - if the variable I am using to search contains a quote? From testing all works fine UNTIL I place a Quote in the Variable - productname…

From your code:

Does this or can this contain a quote? I suggest if you look as qry before it is sent to the sql.Query you will see what I mean.

So do you really want it to return the entire JSON?

Check my reply above kendash - I handle the JSON stuff by parsing it later but it does not make it there.

Here s the full SCRIPT:

function customtag(productname, tagname)
  var qry = "SELECT [CustomTags] FROM [MenuItems] WHERE [Name]='"+productname+"'";
  var r = sql.Query(qry).First;

  var rtag = JSON.parse(r);
  for(var i = 0; i < rtag.length; i++)
    if (rtag[i].TN == tagname) return rtag[i].TV;
  return 0 ;

Just pass the Product name to get the Custom Tag - as I said it works every time except if there is a single quote.
If you put a return qry before the sql.Query it is easy to see the issue. You wind up with a = ‘M and M’s’ - notice 3 quotes!

Ok I see your delima so lets get it working in MSSMS first. Have you done that?

No but I GOOGLE every thing and the proper way to handle it is 2x single quotes i.e. 'Hamburger''s'. This will not help us but as it in the API from sambaPos I feel.

Yes I have used script to add the extra quote and also used escape / but all fail in the parser.

You can use script to insert that. So our issue is not the SQL Helper its a basic SQL issue.

Use a REPLACE for that.

show that script then.

Here was a test:

var qry = "SELECT [CustomTags] FROM [MenuItems] WHERE [Name]='"+productname.replace(/'/g, "\\'")+"'";

I also used the 2x quotes.

var qry = "SELECT [CustomTags] FROM [MenuItems] WHERE [Name]='"+productname.replace(/'/g, "\''")+"'";

They all worked but failed in the parser.

Whatever we do you still need to view (just for checking purposes only) the SELECT Statement before it goes to the sql.Query API and all times it looks good but comes out bad.

I have just removed the products for now until we get a possible solution.

No what I meant was use a CALL script…

We can use functions from other scripts so create one to replace that and then return desired input for query.