Using T-SQL functions in <fieldlist> element of web service XML

Posted By Synety over 9 years ago

How can I use the T-SQL function for example ISNULL() in a <fieldlist> tag of a web service XML (using Sage CRM 7.1)?

When I execute the following request in a fiddler:

<?xml version="1.0" encoding="UTF-8"?>

<soap:Envelope xmlns:soap="">schemas.xmlsoap.org/.../" xmlns:xsd="">www.w3.org/.../XMLSchema" xmlns:xsi="">www.w3.org/.../XMLSchema-instance">
<soap:Header>
<SessionHeader xmlns="">http://tempuri.org/type">
<sessionId>14945547879211</sessionId>
</SessionHeader>
</soap:Header>
<soap:Body>
<queryrecord xmlns="">http://tempuri.org/type">
<fieldlist>lead_leadid, lead_description, isnull(lead_personphonenumber,'') as ppno</fieldlist>
<queryString>lead_leadid=400</queryString>
<entityname>lead</entityname>
<orderby />
</queryrecord>
</soap:Body>
</soap:Envelope>

It returns:

<SOAP-ENV:Fault>
         <faultcode>SOAP-ENV:Server</faultcode>
         <faultstring>Query failed to run successfully.</faultstring>
</SOAP-ENV:Fault>

However when I remove the ISNULL() function the call executes and returns the result successfully.

0 Robert Hurson over 9 years ago

Hiya,

Works for me in a vanilla v7.2ehf3 with demo data. Here's the response:

HTTP/1.1 200 OK

Content-Type: text/xml; charset=utf-8

Expires: Tue, 23 Sep 2014 10:16:00 GMT

Server: Microsoft-IIS/7.5

X-Powered-By: ASP.NET

Date: Wed, 24 Sep 2014 09:16:00 GMT

Content-Length: 1133

" href="http://schemas.xmlsoap.org/soap/encoding/"">schemas.xmlsoap.org/.../" xmlns:SOAP-ENV="">schemas.xmlsoap.org/.../" xmlns:xsi="">www.w3.org/.../XMLSchema-instance"> xmlns="">tempuri.org/type"> xsi:type="typens:crmrecord" xmlns:typens="lead">tempuri.org/.../typens:entityname> xsi:type="typens:recordfield" xmlns:typens="leadid400integer">tempuri.org/.../records> xsi:type="typens:recordfield" xmlns:typens="descriptionWebsite">tempuri.org/.../typens:name>Website Offer #2 - Leadstring" href="http://tempuri.org/type">ppno104">tempuri.org/.../typens:name>104 9234stringfalse

Is there anything in the ewaresql.log?

Thanks,

Rob
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Robert Hurson over 9 years ago

Sorry, just saw the 7.1 tag (I'd looked at your profile to check the version). I can reproduce the issue there. Looks like you're running afoul of a SQL injection check. This is in my ewaresystem.log:

Sep 24 2014 10:28:23.005 15692 5520 1 Invalid SQL sent to webservice, possible attempted injection. - select lead_leadid, lead_description, isnull(lead_personphonenumber,'') as ppno from lead where lead_deleted is null and lead_leadid=400

One workaround might be to create a custom view, then query that instead of the entity?

Thanks,

Rob
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Synety over 9 years ago

Well that is a possible workaround I agree.

BTW, I didn't get you at "Looks like you're running afoul of a SQL injection check". Can you please explain what makes you feel that?
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Synety over 9 years ago

Hi Rob,

When I tried to execute web service XML for any view, the fiddler returned "Request Failed. Entity '' is not Web Service enabled."

How can I enable any view for web service?
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Robert Hurson over 9 years ago

Hi,

The error in my logs for v7.1 mentions a SQL injection check:

Sep 24 2014 10:28:23.005 15692 5520 1 Invalid SQL sent to webservice, possible attempted injection. - select lead_leadid, lead_description, isnull(lead_personphonenumber,'') as ppno from lead where lead_deleted is null and lead_leadid=400

The check isn't doing a great job, by the looks of it. If you need to query a custom view, then you'll need to add entries for Custom_Edits to CRM so that it's picked up as a custom entity instead. Not great either, but it works.

https://community.sagecrm.com/partner_community/b/hints_tips_and_tricks/archive/2010/11/04/a-trick-to-expose-an-external-table-through-sage-crm-s-soap-web-services.aspx

Thanks,

Rob
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Synety over 9 years ago

Hi Rob,

1. Now I got it. So basically Sage CRM 7.1 web service is considering the ISNULL() as an injection attempt. However this has been resolved in 7.2 web service.

2. I had found out the article describing the linking of view to the Tables and Databases (Administration -> Advanced Customization -> Tables and Databases). As also described in the link you provided above.

Thank you very much.

Regards,
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Robert Hurson over 9 years ago

Hi Kal,

No problem at all. If you need it fixed in v7.1, log a case with your local support team - we'll get it assigned to a patch. Otherwise, if adding the view as a table works for you, then that's fine as well.

Glad to be of help,

Rob
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Synety over 9 years ago

Thanks Rob.

Adding the view as a table worked for me.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel

Sage CRM

Using T-SQL functions in <fieldlist> element of web service XML

COMMUNITY

COMPANY

PARTNERS

SUPPORT & TRAINING