tracker issue : CF-4202765

select a category, or use search below
(searches all categories and all time range)
Title:

Executing the SQL statement is not allowed Bug

| View in Tracker

Status/Resolution/Reason: To Track//PRNeedInfo

Reporter/Name(from Bugbase): Anthony Morse / ()

Created: 06/08/2018

Components: Database, General

Versions: 2016

Failure Type: Non Functioning

Found In Build/Fixed In Build: 2016.0.05.303689 /

Priority/Frequency: Normal / All users will encounter

Locale/System: English / Win 2012 Server x64

Vote Count: 0

1 - Use a data source with the Allowed SQL Drop unchecked
2 - Run a sql update or insert on a field with the value of  '/ drop a' 
3 - Returns the error message Executing the SQL statement is not allowed.  

- if you remove the / it runs successfully
- If you have no characters after drop it runs successfully
- other characters in front of drop  also cause the error (example = \ Drop a | drop a & drop a)

Exhibits the same behavior with any of the other Allowed SQL selections unchecked
SELECT	 CREATE	 GRANT
INSERT	 DROP	 REVOKE
UPDATE	 ALTER	 
DELETE

Attachments:

Comments:

Example code <!--- create table atest (id int, xml_data xml); ---> <cftry> <cfset variables.xmldata = EncodeForXML("\ drop a")> <cfquery name="test" datasource="cluster0"> declare @nodeValue nvarchar(max) declare @noNode integer select @nodeValue = ( select case when xml_data.exist('(/data/field)[1]') = 1 then xml_data.value('(/data/field)[1]', 'nvarchar(max)') else '' end from atest where id = 1 ) select @noNode = ( select case when xml_data.exist('(/data/field)[1]') = 1 then 0 else 1 end from atest where id = 1 ) if @noNode = 1 begin update atest set xml_data.modify(N'insert <field>#variables.xmldata#</field> into (/data[1])') where id = 1 end else if @nodeValue = '' begin update atest set xml_data.modify(N'insert text{"#variables.xmldata#"} into (/data/field[1])[1]') where id = 1 and xml_data.value('(/data/field)[1]', 'nvarchar(max)') = '' end else update atest set xml_data.modify(N'replace value of (/data/field/text())[1] with "#variables.xmldata#"') where id = 1 </cfquery> <cfcatch type="any"> <cfoutput>#cfcatch.message# #cfcatch.detail#</cfoutput> </cfcatch> </cftry>
Comment by Anthony M.
29021 | June 12, 2018 03:01:03 AM GMT
Hi Anthony, Can you please share the valid use case for this kind of query? Why do you want to prepend "/" with DDL queries? Are you getting these DDL queries from UI? -Nimit
Comment by Nimit S.
29946 | November 09, 2018 06:09:07 AM GMT
Hi Anthony, Can you please respond to my previous query? -Nimit
Comment by Nimit S.
29947 | November 20, 2018 11:05:31 AM GMT