Exit
  • Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
  • 한국 커뮤니티
0

Add cfqueryparam in cfscript block

Community Beginner ,
Feb 10, 2022 Feb 10, 2022

Hello,

I am trying to fix SQL injection inside an <CFSCRIPT> block.  Please see the below screen snap shot

 

pham_mn_2-1644509031498.pngexpand image

/* 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

 

 

 

129
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Feb 11, 2022 Feb 11, 2022
LATEST

 

...

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"}

 

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources