SQL to Caml parser is not happy about escaped quotes

[petero-dk]

Parent = "Support and deliver roll out of \"Sale Management\"" AND ID <> 400

When parsed becomes

<Eq><FieldRef Name="Parent" /><Value Type="Text">Support and deliver roll out of "Sale ManagementNull AND ID&lt;&gt;400</Value></Eq>

It seems to affect the second escaped "
If I switch around and use single ' they do have the same problem if the ' is the last item in the quotations.

Parent = 'transletsen af matrialeBy O\'Conner and O\''
<Eq><FieldRef Name="Parent" /><Value Type="Text">transletsen af matrialeBy O'Conner and O**Null**</Value></Eq>

Actually the problem is having the same escaped character as the quotation character in a where clause

[Aymkdn]

Can you show me the full query that you try to parse ?

[Aymkdn]

I'll assume it's :
"Parent = 'Support and deliver roll out of \"Sale Management\"' AND ID <> 400"

In that case you can proceed this way:

$SP().list("The List").get({
  whereCAML:true,
  where:$SP().parse("Parent = 'Support and deliver roll out of &quot;Sale Management&quot;' AND ID <> 400", false)
})

So you replace the quotes by ", and you use $SP().parse() with false for escapeChar parameter.

[petero-dk]

I cannot do that. These are dynamic generated queries so I do not know which other chars that needs to be escaped. The weird thing is that it is only an issue when the escaped ' or " are followed by a non-escaped ' or " respectively.

When it is the last item in this loop:

SharepointPlus/sharepointplus-5.0.js

Lines 818 to 823 in e64bb70

	var apos = letter;
	var word = "", other="";
	while ((letter = queryString.charAt(++i)) != apos && i < limitMax) {
	if (letter == "\\") letter = queryString.charAt(++i);
	word+=letter;
	}

If you notice in my samples it inserts a null instead of the escaped " or '

[Aymkdn]

What is your code exactly?

Because if I do this:

var parent = 'Support and deliver roll out of "Sale Management"';
var id = 400;
var where = $SP().parse("Parent = '"+parent+"' AND ID <> "+id);
console.log(where); // -> <And><Eq><FieldRef Name="Parent" /><Value Type="Text">Support and deliver roll out of "Sale Management"</Value></Eq><Neq><FieldRef Name="ID" /><Value Type="Number">400</Value></Neq></And>
$SP().list("The List").get({
  where:where
})
.then(function(data) {
  console.log(data.length); // -> return X
})

Then it does work...

[Aymkdn]

So, is it OK now?

[petero-dk]

Hi @Aymkdn

As I have tried to explain above, this is a narrow case, but not okay. The workaround you describe is also the one I have temporarily implemented already, however what I tried to describe is a bug in the code.

var parent = "Support and deliver roll out of \\'Sale Management\\'";
var id = 400;
var where = $SP().parse("Parent = '"+parent+"' AND ID <> "+id);
console.log(where); // -> <And><Eq><FieldRef Name="Parent" /><Value Type="Text">Support and deliver roll out of "Sale Management"</Value></Eq><Neq><FieldRef Name="ID" /><Value Type="Number">400</Value></Neq></And>
$SP().list("The List").get({
  where:where
})
.then(function(data) {
  console.log(data.length); // -> return X
})

and

var parent = 'Support and deliver roll out of \\"Sale Management\\"';
var id = 400;
var where = $SP().parse('Parent = "'+parent+'" AND ID <> '+id);
console.log(where); // -> <And><Eq><FieldRef Name="Parent" /><Value Type="Text">Support and deliver roll out of "Sale Management"</Value></Eq><Neq><FieldRef Name="ID" /><Value Type="Number">400</Value></Neq></And>
$SP().list("The List").get({
  where:where
})
.then(function(data) {
  console.log(data.length); // -> return X
})

will always fail, because of the escaped " or ' inside the " or ' in the query. Because the var parent is dynamic I can always end up in one of the edge cases, no matter which I choose.

Does that make sense? The output in my original post (after I have cleaned up a little) directly from my code. The Null in the output text is coming from the parse function

[petero-dk]

The output from the parse function will for both the var parent above be:

...<Value Type="Text">Support and deliver roll out of "Sale ManagementNull AND ID&lt;&gt;400</Value>...

Notice the Null and that the next part is joined into the Value tag

[Aymkdn]

Actually the issue is due to \''. Example:

"Title = 'James O\'Connor and O\'' AND ID <> 0
_________________________________ ^

The '' will be converted in Null. I'll look at this to provide a fix for next release (v5.1)

[Aymkdn]

If you want to test, replace ligne 694 by :

var queryString = q.replace(/(\s+)?(=|~=|<=|>=|<>|<|>| LIKE | IN )(\s+)?/g,"$2").replace(/([^\\?])""|([^\\?])''/g,"$1Null").replace(/==/g,"="); // remove unnecessary white space & replace '

And let me know please !

[Aymkdn]

Did you get a chance to test it @petero-dk ?

[petero-dk]

Not yet, been away since. On my todo when I get back

[Aymkdn]

OK. Please let me know when you get back :-)

[Aymkdn]

Sorry to hurry you, but it's been a while now and I'd like to close this issue.

If you don't reply soon, then I'll assume it works OK with this fix and it will be released for v5.1.

Thanks.

[petero-dk]

Ill check today

[Aymkdn]

No news, so I'm closing it. Feel free to comment back if you want to.

Thanks for reporting back this issue!