NWEA SQL Report - Cross site scripting warning

[Jared Grieve]

Has anyone else using an SQL report to generate their NWEA roster files encountered a Cross-Site Scripting Detected warning?

The SQL itself doesn't have any < symbols, the headers do so I just tried removing the headers entirely to see if that was it but no such luck. I called PowerSchool support to see if they would know but was told they allow SQL reports but don't support them directly. But would escalate the ticket to tier 2. While I wait on that I thought I'd hit the group up incase anyone else has already resolved the same issue.

While a < sign immediately followed by another character may trigger this error, I wonder what other symbols may as well.

Jared Grieve

Network Technician

Craig City School District

Phone: 907-826-2274

Fax: 907-826-3016

jgr...@craigschools.com

[sup...@trceducationalservices.com]

You will need to upgrade your plugin to support version 20. 

 

Shannon Cochran
TRC Educational Services

Email: sup...@trceducationalservices.com
Website: http://trceducationalservices.com
Helpdesk System: https://trceducationalservices.aceproject.com/
Phone: 440-549-0872

This message is confidential. This message may also be privileged or protected by work product immunity or other laws and regulations. If you have received it by mistake, please re-send this communication to the sender and delete it from your system without copying it or disclosing its contents to anyone.

--
You received this message because you are subscribed to the Google Groups "AK PSUG" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ak-psug+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/ak-psug/CAJWs%2By11CHL%2BDNjuCKsrGMWoGQqminK1uC0saPqA0OYEOw5c%2BA%40mail.gmail.com.

[Reese, Melissa]

Shannon,

Which plugin needs to be upgraded?

Thank you.

Melissa Reese

Director of Technology and Innovation
Valdez City Schools
PO Box 398
Valdez, AK 99686

(907) 834-4716 - office
(308) 214-0831 - cell

mre...@valdezcityschools.org

To view this discussion on the web visit https://groups.google.com/d/msgid/ak-psug/1a382001d68af0%24c27fae20%24477f0a60%24%40trceducationalservices.com.

[Kyle Schneider]

Jared,

It’s a bit tough to say without actually seeing your code. I’ve worked with a few schools and put together MAP exports for them with no problem. Typically I write it so it can be downloaded as a CSV.

If you want to send me what you’ve got, I can take a quick look.

--
Kyle Schneider
GSD Educational Services
Phone: 907-917-4149

Fax: 802-281-3272
Ky...@gsded.com

[Jared Grieve]

I hadn't thought about the plugin version but that wouldn't surprise me as its still on sqlReports 4, I checked https://www.freundresources.net/resources/downloads formerly sqlreports.net and it looks like you need to have an account to download the plugin version that works with SIS 20+... it looks like there is still a free classic version but I'm waiting on my account to be approved before I can download it.

In the meantime, here is the SQL in the NWEA Roster report. Its the same as it was when you helped me get it going at ASTE probably 4 years ago. Aside from commenting out the course number parameter since new courses were being made I was unaware of and they wanted to test in, might change this to an exclude courses parameter instead.

SELECT 

SCH.ABBREVIATION,

'',

PS_CUSTOMFIELDS.GETTEACHERSCF(T.ID,'AK_ATI'),

T. LAST_NAME,

T.FIRST_NAME,

T.MIDDLE_NAME,

T.EMAIL_ADDR,

T.EMAIL_ADDR,

CR.COURSE_NAME || '-' ||CC.EXPRESSION || '-' || CC.SECTION_NUMBER,

'',

S.STUDENT_NUMBER,

S.LAST_NAME,

S.FIRST_NAME,

SUBSTR(S.MIDDLE_NAME,1,1),

TO_CHAR(S.DOB, 'MM/DD/YY'),

S.GENDER,

TO_CHAR(S.GRADE_LEVEL),

CASE S.ETHNICITY

WHEN '1' THEN 'White'

WHEN '2' THEN 'African American'

WHEN '3' THEN 'Hispanic or Latino'

WHEN '4' THEN 'Asian'

WHEN '5' THEN 'American Native'

WHEN '6' THEN 'Alaska Native'

WHEN '7' THEN 'Multi Ethnicity'

WHEN '8' THEN 'Native Hawaiian or Other Pacific Islander'

ELSE 'Missing' END

FROM CC

JOIN STUDENTS S ON S.ID = CC.STUDENTID

JOIN TEACHERS T ON T.ID = CC.TEACHERID

JOIN COURSES CR ON CR.COURSE_NUMBER = CC.COURSE_NUMBER

JOIN SCHOOLS SCH ON SCH.SCHOOL_NUMBER = S.SCHOOLID

WHERE  CC.TERMID in (%param1%)

AND CC.SCHOOLID in (%param2%)

-- AND CC.COURSE_NUMBER in (%param3%)

AND S.ENROLL_STATUS = 0

AND S.GRADE_LEVEL IN (2,3,4,5,6,7,8,9,10,11,12)

Headers:

<th>School Name</th><th>Previous Instructor ID</th><th>Instructor ID</th><th>Instructor Last Name</th><th>Instructor First Name</th><th>Instructor Middle Initial</th><th>User Name</th><th>Email Address</th><th>Class Name</th><th>Previous Student ID</th><th>Student ID</th><th>Student Last Name</th><th>Student First Name</th><th>Student Middle Initial</th><th>Student Date of Birth</th><th>Student Gender</th><th>Student Grade</th><th>Student Ethnic Group Name</th>

Jared Grieve

Network Technician

Craig City School District

Phone: 907-826-2274

Fax: 907-826-3016

jgr...@craigschools.com

To view this discussion on the web visit https://groups.google.com/d/msgid/ak-psug/9497F5EB-5789-4F00-8539-80669EBF083C%40gsded.com.

[sup...@trceducationalservices.com]

So with version 20 you won’t have any luck using version 4 of SQL reports.  Matt who manages the site does pretty good job at activating accounts.  Basically with version 20 + they added more code for cross scripting and with the latest version when you click submit it changes the <TD>’s to Divs and that gets you past the error you are experiencing. 

 

I would just hold off on your sql report since this is probably the issue. 

 

Shannon Cochran
TRC Educational Services

Email: sup...@trceducationalservices.com
Website: http://trceducationalservices.com
Helpdesk System: https://trceducationalservices.aceproject.com/
Phone: 440-549-0872

This message is confidential. This message may also be privileged or protected by work product immunity or other laws and regulations. If you have received it by mistake, please re-send this communication to the sender and delete it from your system without copying it or disclosing its contents to anyone.

 

From: ak-...@googlegroups.com <ak-...@googlegroups.com> On Behalf Of Jared Grieve

To view this discussion on the web visit https://groups.google.com/d/msgid/ak-psug/CAJWs%2By3ri2Lt_xrZCqsK0Y8VwQ-EvU4EJ_%3Dgj-Qm_r2JxgmsxA%40mail.gmail.com.

[Kyle Schneider]

Shannon is right. The issue is going to be SQL Reports and cross site scripting. Either grab the new version, or we can chat about setting this up as a downloadable CSV straight from a link in PowerSchool.

Either way, using ps_customfields.getteacherscf is frowned on nowadays. I rewrote it to pull directly from the correct table.

SELECT 

 

SCH.ABBREVIATION,

'',

u.ATI,

JOIN s_ak_usr_x u ON u.usersdcid=t.users_dcid

JOIN COURSES CR ON CR.COURSE_NUMBER = CC.COURSE_NUMBER

JOIN SCHOOLS SCH ON SCH.SCHOOL_NUMBER = S.SCHOOLID

 

WHERE  CC.TERMID in (%param1%)

AND CC.SCHOOLID in (%param2%)

-- AND CC.COURSE_NUMBER in (%param3%)

AND S.ENROLL_STATUS = 0

AND S.GRADE_LEVEL IN (2,3,4,5,6,7,8,9,10,11,12)

--
Kyle Schneider
GSD Educational Services
Phone: 907-917-4149

Fax: 802-281-3272
Ky...@gsded.com

On Sep 14, 2020, at 8:18 PM, sup...@trceducationalservices.com wrote:

So with version 20 you won’t have any luck using version 4 of SQL reports.  Matt who manages the site does pretty good job at activating accounts.  Basically with version 20 + they added more code for cross scripting and with the latest version when you click submit it changes the <TD>’s to Divs and that gets you past the error you are experiencing.  

 

I would just hold off on your sql report since this is probably the issue.  

 

Shannon Cochran
TRC Educational Services

Email: sup...@trceducationalservices.com
Website: http://trceducationalservices.com
Helpdesk System: https://trceducationalservices.aceproject.com/
Phone: 440-549-0872

<image001.png>

To view this discussion on the web visit https://groups.google.com/d/msgid/ak-psug/1a835c01d68af5%24b6a86c40%2423f944c0%24%40trceducationalservices.com.

[Marsha Sandhofer]

This might not be related, but I have been having tons of trouble with my Daily Bulletin.  If I use both the Paragraphs and the bulleted items I get the Cross-Site Script Error.  I've gone through every bit of the script I am using and can not find a single error with my < placing.  I have had to just give up and modify how my bulletin looks. I am interested in what anyone finds out about this type of error.

Thank you

Marsha

Petersburg High School

--

You received this message because you are subscribed to the Google Groups "AK PSUG" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ak-psug+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/ak-psug/CAJWs%2By11CHL%2BDNjuCKsrGMWoGQqminK1uC0saPqA0OYEOw5c%2BA%40mail.gmail.com.

--

Marsha Sandhofer
High School & Athletic Information Clerk

Petersburg High School

Phone Number! 1-877-526-7656, ext 201

[Kyle Schneider]

Almost all HTML tags are blocked. There is a specific list of tags that are acceptable. You can find those here:

https://help.powerschool.com/t5/PowerSchool-SIS-Knowledge-Base/SIS-Cross-Site-Scripting-Security/ta-p/11629

One thing I’ve found when working with a lot of folks is that they’re using a <font> tag. Those aren’t going to work.

--
Kyle Schneider
GSD Educational Services
Phone: 907-917-4149

Fax: 802-281-3272
Ky...@gsded.com

To view this discussion on the web visit https://groups.google.com/d/msgid/ak-psug/CABkz-Y8mGJhH7bKMrDVd%3DYPp4o9meHiz7DY0MpVvNVaBot%2BnTg%40mail.gmail.com.

[Jared Grieve]

I'm replying to the group just incase its beneficial to anyone else, but a link in PS that spits out a NWEA Roster CSV sounds good to me. I have sqlReports for a few other things and always meant to use it more but it never made it to the top of my priority list so its pretty much used just for this. I'm more than happy to learn about alternate methods. We can move this to a private conversation if you prefer but it sounds like Melissa might be in the same boat.

Thanks for the replies.

Jared Grieve

Network Technician

Craig City School District

Phone: 907-826-2274

Fax: 907-826-3016

jgr...@craigschools.com

To view this discussion on the web visit https://groups.google.com/d/msgid/ak-psug/3F99A43C-E536-431F-ABE3-5F042136999A%40gsded.com.

[Adam Larsen]

Here is a pretty good general form query that would serve as a good starting point from SQL Developer, SQL Studio, sqlReports, or a custom PS page.

There is some fancy date math in here to help it run for the correct year and terms whenever you run it, but you could also use params or other methods to filter to the correct time period.  This is more or less what I run when I build my file each term.

select
    schools.name "School Name",
    null "Previous Instructor ID",
    teachers.users_dcid "Instructor ID",
    teachers.last_name "Instructor Last Name",
    teachers.first_name "Instructor First Name",
    substr(teachers.middle_name, 1, 1) as "Instructor Middle Initial",
    teachers.email_addr "User Name",
    teachers.email_addr "Email Address",
    terms.abbreviation || ' ' || courses.course_name || ' ' || sections.external_expression "Class Name",
    null "Previous Student ID",
    students.student_number "Student ID",
    students.last_name "Student Last Name",
    students.first_name "Student First Name",
    students.middle_name "Student Middle Initial",
    to_char(students.dob, 'FMmm/dd/yyyy') "Student Date of Birth",
    students.gender "Student Gender",
    students.grade_level "Student Grade",
    students.ethnicity "Student Ethnic Group Name"
from
    cc
    join sections on cc.sectionid = sections.id
    join teachers on cc.teacherid = teachers.id
    join courses on cc.course_number = courses.course_number
    join students on cc.studentid = students.id
    join schools on students.schoolid = schools.school_number
    join terms on sections.termid = terms.id and sections.schoolid = terms.schoolid    
where
    students.enroll_status <= 0
    and floor(sections.termid/100) + 1991 = extract(year from add_months(sysdate, 6)) /* Current year */
    and        
        case /* Check the month of when this query is being run */        
            when extract(month from sysdate) in (8,9,10,11,12) and mod(sections.termid, 100) in (0,1,3,4) then 1 /* If Aug-Dec, return Y1, S1, Q1, and Q2 classes */
            when extract(month from sysdate) in (1,2,3) and mod(sections.termid, 100) in (0,2,5,6) then 1 /* If Jan-Mar, return Y1, S2, Q3, and Q4 classes */
            when extract(month from sysdate) in (4,5) and mod(sections.termid, 100) in (0,2,6) then 1 /* If Apr-May, return Y1, S2, and Q4 classes */
            else 0
        end = 1
    and courses.sched_do_not_print != 1    
order by    
    students.grade_level,
    students.lastfirst,
    courses.course_name

--

Adam Larsen

Principal Developer

Aurora Educational Technology LLC

ad...@auroraedtech.com

📣 AMOS Early Warning System | ✅ Grade Verification | 🎥 SQL Studio

To view this discussion on the web visit https://groups.google.com/d/msgid/ak-psug/CAJWs%2By32tYm2b5C34-F_GFpCJ9DzN6xS_g9nYxvaHjitF_%3DjsQ%40mail.gmail.com.

[Jim Shreve]

This may be of help to some of you. I have gone the route of creating an export template in Data Export Manager and then just do some quick data altering in Excel to help teachers ID their classes, align ethnic group names to IDs from PowerSchool, and change Grade 0 to Grade K. About 20 minutes of work in the spreadsheet and I am able to copy / paste my info to the required / optional columns in NWEA's Roster File Template and complete the import. 

It takes about 15 minutes to build out and save the Template in Data Export Manager using Category: Student Information and Export From: Student : Course Data (screenshots of my Template fields attached). Then it is available under the My Templates tab for re-use whenever you need it.

Disclaimer - I am pulling info from one custom screen I created for Student email address (U_DEF_EXT_STUDENTS.student_email_address) for completing the Roster Template optional fields of Student User Name and Student Email. I was not able to access the StudentEmail Field from the Courses Data tables.

As always, start with the students selected that you want to export data on (example for MAP I select appropriate Grades (K-10) at each school for smaller exports, or all K-10 at District level for a mass export).

Jim Shreve

Director of Technology

Nome Public Schools

(907)443-6203

jims...@nomeschools.org

"In brightest day, in blackest night

No evil shall escape my sight

Let those who worship evil's might

Beware my power... Green Lantern's light!"

Hal Jordan's / Green Lantern mantra, DC Comics (1959)

To view this discussion on the web visit https://groups.google.com/d/msgid/ak-psug/CAJWs%2By32tYm2b5C34-F_GFpCJ9DzN6xS_g9nYxvaHjitF_%3DjsQ%40mail.gmail.com.