Add cfqueryparam in cfscript block
Copy link to clipboard
Copied
Hello,
I am trying to fix SQL injection inside an <CFSCRIPT> block. Please see the below screen snap shot
/* Determine if user has account */
qryAuthEmp = queryExecute("
SELECT a.*, facility_key, org_cd
FROM staffing.auth_user_name a,
(SELECT ndc_emp_id,
(CASE
WHEN (INSTR (email_id, '@') - 1) > 20
THEN
UPPER (SUBSTR (email_id, 1, 20)) || '@FAA.GOV'
ELSE
UPPER (email_id)
END)
user_email
FROM staffing.auth_user_name) b,
(SELECT DISTINCT facility_key, y.org_cd, email_id
FROM #session.userProfile.emailAddress contains "CTR." ? "staffing.contractor x" : "staffing.employee x"#
JOIN staffing.master_org_cc_lookup Y
ON x.org_cd = y.org_cd
WHERE active = 'Y'
AND end_date > SYSDATE
#session.userProfile.emailAddress contains "CTR." ? "" : "AND emp_stat_cd IN ('A','C','F','L','M','P')"#) d
WHERE a.ndc_emp_id = b.ndc_emp_id
AND a.email_id = d.email_id
AND a.is_active = :ActiveStatus
AND a.end_date > SYSDATE
AND UPPER(b.user_email) = :email_id
",
{
ActiveStatus={value='Y', cfsqltype='cf_sql_varchar'},//Fix SQL Injection
email_id={value=uCase(session.userProfile.emailAddress), cfsqltype='cf_sql_varchar'}
},
{
datasource='#application.dsn#'
});
I added line 46, 47 for ":ActiveStatus" (line 41), and ":email_id" at line 43, but I am not sure how do I do the the same thing to line 36 and 38 (the syntax wise).
Anyone has any idea please?
Thanks in advance
Copy link to clipboard
Copied
...
WHERE active = :ActiveStatus
...
#session.userProfile.emailAddress contains "CTR." ? "" : "AND emp_stat_cd IN (:emp_stat_cd_list)"#) d
...
ActiveStatus={value='Y', cfsqltype='cf_sql_varchar'},
email_id={value=uCase(session.userProfile.emailAddress), cfsqltype='cf_sql_varchar'},
emp_stat_cd_list={value="A,C,F,L,M,P" cfsqltype="cf_sql_char" list="true"}

