Error when SQL is specified in "where" attribute in AML
Hi Team,
There is AML that seems to have never been an error before, but somehow it becomes an error.
* Aras version is 11.0SP15.
For example, I remember that SQL could be written in the where attribute like the following AML and IOM code.
At the very least, I was able to execute AML / IOM code written in this way in my previous development (V11.0SP8 environment).
===<AML>========================================
<Item type='CAD Structure' action='get' where="innovator.[CAD_Structure].source_id in (select id from innovator.[CAD] where name = 'TestName')">
</Item>
================================================
===<IOM code>=====================================
Item applyItm = inn.newItem("CAD Structure", "get");
applyItm.setAttribute("where", "[CAD_Structure].source_id in (select id from innovator.[CAD] where name = 'TestName')");
Item resultItm = applyItm.apply();
================================================
However, when I run this on my current development project, I get the following error:
##############################################################
<SOAP-ENV:Envelope xmlns:SOAP-ENV='schemas.xmlsoap.org/.../'>
<SOAP-ENV:Body>
<SOAP-ENV:Fault xmlns:af='www.aras.com/InnovatorFault'>
<faultcode>SOAP-ENV:Server.ItemAnalysisException</faultcode>
<faultstring><![CDATA[Item Analysis Error. Some Items have incorrect attribute/property values syntax. See details for more information.
Details:
Incorrect value: "where="innovator.[CAD_Structure].source_id in (select id from innovator.[CAD] where name = 'TestName')"". Incorrect value explanation: "select" is forbidden in where attribute.
Incorrect value: "where="innovator.[CAD_Structure].source_id in (select id from innovator.[CAD] where name = 'TestName')"". Incorrect value explanation: Item hasn't property referred to column with name "name"
]]></faultstring>
<detail>
<af:legacy_detail><![CDATA[Item Analysis Error. Some Items have incorrect attribute/property values syntax. See details for more information.
Details:
Incorrect value: "where="innovator.[CAD_Structure].source_id in (select id from innovator.[CAD] where name = 'TestName')"". Incorrect value explanation: "select" is forbidden in where attribute.
Incorrect value: "where="innovator.[CAD_Structure].source_id in (select id from innovator.[CAD] where name = 'TestName')"". Incorrect value explanation: Item hasn't property referred to column with name "name"
]]></af:legacy_detail>
<af:exception message='Item Analysis Error. Some Items have incorrect attribute/property values syntax. See details for more information.
Details:
Incorrect value: "where="innovator.[CAD_Structure].source_id in (select id from innovator.[CAD] where name = 'TestName')"". Incorrect value explanation: "select" is forbidden in where attribute.
Incorrect value: "where="innovator.[CAD_Structure].source_id in (select id from innovator.[CAD] where name = 'TestName')"". Incorrect value explanation: Item hasn't property referred to column with name "name"
' type='Aras.Server.Core.ItemAnalysisException' />
</detail>
</SOAP-ENV:Fault>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>
##############################################################
I get an error message saying that "select" is forbidden in where attribute.
I've seen errors like this for the first time.
What should I do?
Is there something wrong with the syntax ? Is the Aras environment bad ? Has Aras AML / IOM rules changed ?
Regards
Hi NiNi,
a security update in one of the version 11 Service Packs made it so that you cannot simply use all sorts of SQL in where-clauses any more. You will need to work with whitelists to re-enable this. See this community post for further details. I have never used it myself, I simply remembered the linked community post.
Cheers,
C
