tracker issue : CF-4126398

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

SQL comments inside CFQuery are not ignored

| View in Tracker

Status/Resolution/Reason: Closed/Fixed/

Reporter/Name(from Bugbase): Harry Klein / Harry Klein (Harry Klein)

Created: 03/09/2016

Components: Database

Versions: 2016

Failure Type: Unspecified

Found In Build/Fixed In Build: RC1_v12 /

Priority/Frequency: Major / Unknown

Locale/System: English / Win All

Vote Count: 2

Listed in the version 2016.0.01.298513 Issues Fixed doc
Verification notes: verified_fixed on July 30, 2017 using build 2016.0.01.298513 
Problem Description:
SQL comments inside cfquery are not ignored anymore!
Tested with MSSQL 2008

Steps to Reproduce:
MSSQL Query

			WHERE
				(
					(
						p3.isvisibleinnavi IS NOT NULL					-- is online and...
						AND
						(	p3.isvisibleinnavi <> p2.isvisibleinnavi	-- isvisible in navi was changed ...
						 	OR qap.page_offline = 1						-- OR page goes offline
							OR p2.pagenavititle <> p3.pagenavititle
							OR p2.pagepath <> p3.pagepath
							OR p2.pagefile <> p3.pagefile
							OR p2.app_ID <> p3.app_ID 		-- or page path has changed
							OR p2.folder_ID <> p3.folder_ID	-- or sortorder below same parent folder has changed (same pagepath)
						)
					)

Actual Result:
Throws an error: ... due to underlying exception: Error Executing Database Query. / [Macromedia][SQLServer JDBC Driver][SQLServer]Falsche Syntax in der Nähe von 'NULL'."

Expected Result:
Query should execute fine

Any Workarounds:
Remove all SQL Comments

----------------------------- Additional Watson Details -----------------------------

Watson Bug ID:	4126398

External Customer Info:
External Company: CONTENS
External Customer Name: Harry Klein
External Customer Email: KLEIN@CONTENS.DE
External Test Config:  


Bug File Paths:
\\sjshare.corp.adobe.com\Prereleasebugfiles\ColdFusion\12.0\RC1_v12\4114953\testquery.cfm

Attachments:

Comments:

Adding BUG AUDIT TRAIL ********action: updated fieldName: Fixed By newValue: himar oldValue: Blank oprid: himar recordName: RQ_DEFECT timpestamp: 2016-02-12 08:22:56.0 action: updated fieldName: Date Fixed newValue: 2016-02-12 00:22:55.0 oldValue: Blank oprid: himar recordName: RQ_DEFECT timpestamp: 2016-02-12 08:22:56.0 action: updated fieldName: Changelist newValue: 298019 oldValue: Blank oprid: himar recordName: RQ_DEFECT timpestamp: 2016-02-12 08:22:56.0 action: updated fieldName: Status newValue: ToTest oldValue: ToFix oprid: himar recordName: RQ_DEFECT timpestamp: 2016-02-12 08:22:56.0 action: updated fieldName: Reason newValue: Fixed oldValue: BugVerified oprid: himar recordName: RQ_DEFECT timpestamp: 2016-02-12 08:22:56.0 action: updated fieldName: Owner newValue: nimsharm oldValue: himar oprid: himar recordName: RQ_DEFECT timpestamp: 2016-02-12 08:22:56.0 action: updated fieldName: Fix By Milestone newValue: Post Release oldValue: Alpha oprid: vmannebo recordName: RQ_DEFECT timpestamp: 2016-02-12 07:29:16.0 action: updated fieldName: Fix By Product Milestone newValue: HF1 oldValue: Alpha oprid: vmannebo recordName: RQ_DEFECT timpestamp: 2016-02-12 07:29:16.0 action: updated fieldName: Fix By Milestone newValue: Alpha oldValue: Blank oprid: nimsharm recordName: RQ_DEFECT timpestamp: 2016-02-10 14:14:13.0 action: updated fieldName: Priority newValue: 3 oldValue: 0 oprid: nimsharm recordName: RQ_DEFECT timpestamp: 2016-02-10 14:14:13.0 action: updated fieldName: Reason newValue: BugVerified oldValue: Blank oprid: nimsharm recordName: RQ_DEFECT timpestamp: 2016-02-10 14:14:13.0 action: updated fieldName: Found in Phase newValue: Gold Master oldValue: Blank oprid: nimsharm recordName: RQ_DEFECT timpestamp: 2016-02-10 14:14:13.0 action: updated fieldName: Status newValue: ToFix oldValue: Unverified oprid: nimsharm recordName: RQ_DEFECT timpestamp: 2016-02-10 14:14:13.0 action: updated fieldName: Fix By Product Milestone newValue: Alpha oldValue: Blank oprid: nimsharm recordName: RQ_DEFECT timpestamp: 2016-02-10 14:14:13.0 action: updated fieldName: Owner newValue: himar oldValue: nimsharm oprid: nimsharm recordName: RQ_DEFECT timpestamp: 2016-02-10 14:14:13.0
Comment by CFwatson U.
4254 | March 09, 2016 01:56:29 AM GMT
Added By: PreRelease User User Name:Harry Klein Note Added: This issue can be closed Date Added :2016-02-26 11:25:05.0 Added By: PreRelease User User Name:Harry Klein Note Added: Seems to be fixed, my tests very positive Date Added :2016-02-16 09:46:35.0 Added By: PreRelease User User Name:Harry Klein Note Added: I was able to create a testfile Date Added :2016-02-11 09:23:31.0 Added By: PreRelease User User Name:Harry Klein Note Added: Entered Bug. Date Added :2016-02-08 12:32:59.0
Comment by CFwatson U.
4255 | March 09, 2016 01:56:30 AM GMT
It breaks all of our code in CF 2016.
Vote by External U.
4261 | March 23, 2016 01:22:45 PM GMT
This issue is fixed and the fix will be available as part of upcoming update of ColdFusion.
Comment by Nimit S.
4256 | March 24, 2016 02:34:12 AM GMT
Is there an ETA for when the patch will be released? 0 out of the 6 apps I have checked so far are working in CF2016 as a result of this issue and we can't even download CF11 in the meantime.
Comment by External U.
4257 | May 06, 2016 12:06:32 PM GMT
This impacts all our prior code -
Vote by Kevin P.
4262 | April 18, 2017 10:17:36 PM GMT
I don't think the fix works well. The fix can't be backward compatibility. E.g., my code is - <CFQUERY NAME="spotlight" datasource=#application.dsn#> select s.spotlight_id,s.display_date,s.spotlight_image,s.spotlight_desc,l.e_site_name league_name,l.e_site_folder note,sp.sport_name from e_site l, spotlight s, sport sp , e_site_org eso where s.spotlight_sport_code=sp.sport_code and s.spotlight_e_site_id=l.e_site_id --and s.spotlight_sport_code=#sport_code# AND s.spotlight_display=1 AND s.spotlight_status=4 and eso.e_org_id=37 --lb and eso.e_site_Id=l.e_site_Id and eso.del_flag=0 order by s.display_date desc </CFQUERY> Before the fix, it doesn't work. After the fix, it's compiled to the sql below - select s.spotlight_id,s.display_date,s.spotlight_image,s.spotlight_desc,l.e_site_name league_name,l.e_site_folder note,sp.sport_name from e_site l, spotlight s, sport sp , e_site_org eso where s.spotlight_sport_code=sp.sport_code and s.spotlight_e_site_id=l.e_site_id --and s.spotlight_sport_code=138 AND s.spotlight_display=1 AND s.spotlight_status=4 and eso.e_org_id=37 --lb and eso.e_site_Id=l.e_site_Id and eso.del_flag=0 order by s.display_date desc Seems it comments all the statements after the first sql comment. It works well before CF2016. Also it could cause the performance issue of the application for it removes all the statement.
Comment by Jay G.
4258 | June 27, 2017 03:21:38 AM GMT
Does it change if sport_code is query param'd?
Comment by Kevin P.
4259 | June 28, 2017 06:25:46 PM GMT
Verified, on April 12, 2016, that this is fixed in CF2016 Update 1 (build 2016.0.01.298513). Thanks!, -Aaron
Comment by Aaron N.
4260 | July 30, 2017 06:17:00 PM GMT