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;
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”]
“+productname+”
[/quote]
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
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'
Therefore: SELECT [CustomTags] FROM [MenuItems] WHERE [Name]='M and M's' - breaks right.
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;
}
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.
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!
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.
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.
