Query issue with Oracle have not tried with Microsoft. Not a ColdBox issue but found while coding in ColdBox.

function getAccounts(string pDsn,string pFirstname,string pLastname,string pEmailAddress,string pSortby) returntype=“Query” output=“false” {
// Setup a variable for the Query Result
var qResult = ‘’;
// Setup the Query variable
var q= new query();
// Add Parameter
q.addParam(name=“firstName”, value="#ucase(arguments.pFirstName)#%", cfsqltype=“CF_SQL_VARCHAR”);
q.addParam(name=“lastName”, value="#ucase(arguments.pLastName)#%", cfsqltype=“CF_SQL_VARCHAR”);
q.addParam(name=“emailAddress”, value="#ucase(arguments.pEmailAddress)#%", cfsqltype=“CF_SQL_VARCHAR”);
q.addParam(name=“sortBy”, value="#arguments.pSortBy#", cfsqltype=“CF_SQL_VARCHAR”);
// Create the SQL String
var sqlString="
SELECT FIRSTNAME,
LASTNAME,
RANK,
EMAILADDRESS,
STATUS,
REASON,
USERID,
PERSONNELID,
LOCKED
FROM MyPERSONNEL
WHERE
UPPER(FIRSTNAME) LIKE :firstName
AND UPPER(LASTNAME) LIKE :lastName
AND UPPER(EMAILADDRESS) LIKE :emailAddress
ORDER BY :sortBy
";

q.setdatasource(arguments.pDsn);
q.setsql(sqlString);
qResult=q.execute().getresult();
return qResult;

}

Calling the object function above

test = new myapp.model.manageAccounts.manageAccount();
// Setup a structure of the variables to pass in.
params = {};
params.pDsn = datasource;
params.pFirstName = ‘j%’;
params.pLastName = ‘s%’;
params.pEmailAddress = ‘%’;
params.pSortby = “Upper(firstname)”;
// Get the accounts
qManageAccount = test.getAccounts(argumentCollection=params);

params.pSortby = “firstname”;

I believe I have discovered the issue but I need help figuring out how to fix the issue.

I am wanting to pass in the SortBy but when I do and I use the query param and it is a string it will not work in Oracle.

When I go to SQL Developer and put in the query with the sort by ‘firstname’ I get the results not sorted. if I take away the quotes it works fine. I think this is the issue. So how can I pass in the sortby which will be in the URL so that it is safe from injection and will work in oracle.

I hope this makes sense if not ask and I will try to explain in more detail.

I think the params have to be set after the SQL is set.

I will try this but in the coldfusion debug it shows the query param and the where statement works just not the sortby… I think it is putting single quotes around the varialbe and there by causing the issue. example order by ‘firstname’ In sql developer this turns out to be true.

I mean in sql developer it gives the same bad results with the single quotes is why I think this might be the issue.

If you have your code as you type… I am surprised that you aren’t having this issue.

http://www.andyscott.id.au/2011/8/11/Tip-using-ColdFusion-query-in-cfscript-and-something-to-watch-out-for

If you move the params before the query it works fine. The only issue is that I think it is putting single quotes around the order by ‘Upper(Firstname)’ which in the SQL developer does not work… I am still trying to get it to work.