Coldfusion to insert a structure dynamically in SQL throws error

I am trying dynamically to insert a structure into MS SQL using the below code.

When I run the code I get the  message.
[Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near '<'.

However, when running the resolved SQL no error is shown.

Any help is greatly appreciated

=========================
To create table
=========================
USE [tfdata]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[mortgEdit1](
	[meId] [int] IDENTITY(1,1) NOT NULL,
	[meBatchNo] [int] NULL,
	[meModule] [nvarchar](64) NULL,
	[meActive] [nvarchar](16) NULL,
	[meAction] [int] NULL,
	[meComments] [nvarchar](256) NULL,
	[meDateCreated] [datetime] NULL,
	[meDateUpdated] [datetime] NULL,
	[meCreatedBy] [nvarchar](64) NULL,
	[meUpdatedBy] [nvarchar](64) NULL,
 CONSTRAINT [mortgEdit1_pk] PRIMARY KEY CLUSTERED 
(
	[meId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Open in new window




=========================
CF Code
=========================

<cfset strucmortgEdit1 = structnew()>
<cfset strucmortgEdit1.meComments = "This is a test">
<cfset strucmortgEdit1.meActive = "Activie">
<cfset strucmortgEdit1.meBatchNo = 250>

<cfset keyList = structKeyList(strucmortgEdit1)>	
<cfset keyValueList = "">
<cfset counter = 1>	

<cfloop List="#keyList#" index="listItem">
    <cfset keyValueList = keyValueList & '<cfqueryparam cfsqltype="cf_sql_varchar" value="#strucmortgEdit1[listItem]#">'>
    <cfif counter LT listLen(keyList)>
        <cfset keyValueList = keyValueList & ",">
        <cfset counter = counter + 1>
    </cfif>
</cfloop>


<!--- This resolved SQL runs without a problem --->
<cfquery name="x1">
insert into 
	mortgEdit1(MEACTIVE,MECOMMENTS,MEBATCHNO) 
values 
	(<cfqueryparam cfsqltype="cf_sql_varchar" value="Activie">,<cfqueryparam cfsqltype="cf_sql_varchar" value="This is a test">,<cfqueryparam cfsqltype="cf_sql_varchar" value="250">)
</cfquery>


 
<!--- insert row to table. This one throws the error --->
<cfquery name="yourQueryName" >
	insert into 
		mortgEdit(#keyList#)
	values 
	(#keyValueList#)
</cfquery>

Open in new window

Errol FarroAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

_agx_Commented:
Yeah, unfortunately you can't use cfqueryparam *within* dynamic strings.  That tag can't be evaluated at runtime that way. It must be be nested within cfquery tags, like in your example that "does" work.

If you want to use a dynamic number of bind variables, use the cfscript version of cfquery/cfqueryparam to both build the string and insert the query parameters.https://cfdocs.org/cfqueryparam

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
_agx_Commented:
insert into  mortgEdit(#keyList#)

In addition using dynamic column names poses a sql injection risk, because they can't be protected with cfqueryparam. If the structure data is re user supplied, be sure to validate the column names against a white list first, at the very least.

CFScript Example:

<cfscript>
    allowedColumns = "meActive";
	
	strucmortgEdit1 = structnew();
	strucmortgEdit1.meComments = "This is a test";
	strucmortgEdit1.meActive = "Activie";
	strucmortgEdit1.meBatchNo = 250;

	values = [];
	params = [];
	
	// Build arrays of parameters and values
	for (key in strucmortgEdit1) {
	     // WARNING: Using dynamic column NAMES is sql injection risk.
	    // MUST validate first, if values are user supplied
	    // Abort if invalid column names are detected
		if (!listFindNoCase(allowedColumns, key)) {
			throw("ERROR: Invalid column name detected");
		}
		arrayAppend(values, "?");
		arrayAppend(params, {value=strucmortgEdit1[key], cfsqltype="cf_sql_varchar"});
	}

	columnNames = structKeyList(strucmortgEdit1);
	sqlString = "INSERT INTO mortgEdit1( "& columnNames &" )"
			   & " VALUES ( "& arrayToList(values, ",") &" )";

    // Execute SQL 
	queryExecute(sqlString, params, {datasource="SQLServerCore"});
</cfscript>

Open in new window

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ColdFusion Language

From novice to tech pro — start learning today.