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

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 开发者_StackOverflow社区references for CF?

You can bind parameters using the addParam function of a cfscript query object just like cfqueryparam works. Had to convert your example a bit to work on my MSSQL box and a smaller version of your table but it should give you the general idea.

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

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

query = new query();
//you may need to use the query methods setDatasource, setUsername and setPassword to configure the query

//sql = "INSERT ALL" & chr(13) & chr(10);
sql = "";

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

    query.addParam(name="address"&i,value=clinicNIL[ myFields[i] ],cfsqltype="VARCHAR");

    sql = sql & "INSERT INTO NOTINLIST (ADDRESS) VALUES(";

    sql = sql & ":address" & i;

    sql = sql & ")" & chr(13) & chr(10);

}

queryResult = query.execute(sql=sql);
</cfscript>

The magic is the :paramName in the sql string will have it's associated parameter replaced during the execute call with a properly escaped parameter.

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>