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 :
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.
