is it possible to dynamically create a query and escape the values too using cfscript+cfquery+cfqueryparam?

Question

I'm still new to ColdFusion. Basically I am dynamically creating a query for Oracle. I have used cfquery/cfparam in the past but I would really rather use cfscript to accomplish as that is more readable. It is intended to be a large 'INSERT ALL ... INTO.'

Here's a basic example of what I have so far:

<cfscript>
clinicNIL = structNew();
clinicNIL.ADDRESS1 = 'line 1';
clinicNIL.ADDRESS2 = 'line 2';

myFields = [
     'ADDRESS1'
    ,'ADDRESS2'
];

query = queryNew("");
sql = "INSERT ALL";

for (i=1; i LTE ArrayLen(myFields); i=i+1) {

    sql = sql & "INTO NOTINLIST (SOURCETABLE, SOURCECOLUMN, SOURCEPK, ENTEREDVALUE, INSERTDATE, UPDATEDDATE, INSERTEDBY, UPDATEDBY) VALUES(";
    // [..]

    // How to dynamically escape the value below?
    sql = sql & EscapeTheParameterHere( clinicNIL[ myFields[i] ]);

    // [..]
    sql = sql & ")
";

}

WriteOutput( query );
</cfscript>

Where I have 'EscapeTheParameterHere' I want to be able to have that value escaped somehow. how can I escape the value?

while I'm here, is there any good resources or references for CF?

Answer 1

here is the solution I came up with using cfquery/cfqueryparam. I didn't realize you could do a cfloop inside of a cfquery. By the way, I did find something called 'CF.Query' but apparently it only satisfies a subset of cfquery.

<cfscript>
clinicNIL = structNew();
clinicNIL.ADDRESS1 = 'line 1';
clinicNIL.ADDRESS2 = 'line 2';

myFields = [
     'ADDRESS1'
    ,'ADDRESS2'
];

totalFields = ArrayLen(myFields);

</cfscript>
<cfquery name="insert" datasource="somedatasource">
    INSERT ALL
    <cfloop from="1" to="#totalFields#" index="i">
            INTO NOTINLIST 
            (SOURCETABLE, SOURCEPK, SOURCECOLUMN, ENTEREDVALUE, INSERTDATE, UPDATEDATE, INSERTEDBY, UPDATEDBY) 
            VALUES(
             'FACULTYADDRESSES'
            , 123
            , <cfqueryparam value = "#myFields[i]#" cfsqltype='CF_SQL_VARCHAR'>
            , <cfqueryparam value = "#clinicNIL[ myFields[i] ]#" cfsqltype='CF_SQL_VARCHAR'>
            , SYSDATE
            , SYSDATE
            , '123'
            , '123'
            )
    </cfloop>
    SELECT * FROM DUAL
</cfquery>