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.