Defects

Unable to read selected item. SQL Statement has possible SQL Injection when using "--" in a query



ID:    D27733
Published:    13 January 2021
Updated:    28 January 2021
 

Defect Id

DEF343756

Originally Reported Against

SBM 11.8

Description

 The fix for  DEF183856  has meant that valid queries may be rejected.

For example a query of this form, if run in a WS call (such as a GetItemsByQuery) 

   select TS_ID from USR_FLD_FLDNAME with(nolock) where TS_ID > 0 and (TS_ARG_NAME = 'george--E') 
   order by TS_ID offset 0 rows fetch next 1001 rows only.  

may result in the message :

  Unable to read selected item. SQL Statement has possible SQL Injection: 

This will be resolved in a future release, but there are some possible workarounds.

1. Change SQL to break up the “--“: TS_TITLE = 'george-' + '-' + 'E'
a. Benefit: Response remains the same, easy to consume.
b. Drawback: caller has to detect that they are sending a “--“ and replace it, may be difficult depending on the consuming platform.

2. Change web services call to RunReportXml.  Create a report with query “myField like (Query at Runtime)”.  Include the following in the option section of the XML (translate the f54v1 to the field id of your field, run the report and Copy URL to Clipboard, look in the URL for the &f… value):
<urn:extraOption>
               <urn:name>HasRuntimeParams</urn:name>
               <urn:value>1</urn:value>
            </urn:extraOption>
            <urn:extraOption>
               <urn:name>f54v1</urn:name>
               <urn:value>EASWE--E</urn:value>
            </urn:extraOption>
a. Benefit: Customer need not detect if the value contains “--“.
b. Drawback: caller must change the way they consume the response, may be challenging to consume report xml.

3. Change the web services call to  RunModScript.  Put the value in the inputs section.
a. Benefit: Customer need not detect if the value contains “--“.
b. Drawback: customer must write a ModScript to read the records with this value, send the response in the “Outputs” vector, customer must change calling code to consume new output format.

 

Find Answers

Type a question or describe what you are looking for below

My Recent Searches

Welcome kb sso

Additional Assistance

  • Submit a Case Online
  • FAQs