SQL 原有sql ,現顯示error message

本文由 tr-wong_sir 在 2020-01-08 發表於 "WebSAMS 討論區" 討論區

  1. 55019624

    tr-wong_sir
    Expand Collapse

    文章:
    60
    讚:
    0
    下列sql,是用來尋找學生聯絡資料,
    由2017-2019年度,運作正常,
    但當踏入2020年,就出現error message,

    'The request may contains malicious code.
    Please contact the system administrator.'

    希望給予修正



    select
    a.CLASSLVL,
    s.CLASSNAME '班別',
    a.CLASSNO '學號',
    a.strn,
    a.regno,

    a.ENNAME '英名姓名',
    a.CHNAME '中文姓名',
    a.SEX '性別',

    dateformat(a.DOB, 'DD/MM/YYYY') as '出生日期',
    a.STRN '學生編號',
    a.HOMETEL '家居電話',
    guard.CHNAME as '監護人',
    guard.EMERGENCYPHONE '緊急電話',

    f.chname '父親姓名',
    f.phone '父親電話',

    m.chname '母親姓名',
    m.phone '母親電話',

    (case when trim(a.CHDISTRICT)<>'' then trim(a.CHDISTRICT) else '' end) +
    (case when trim(a.CHSTREET)<>'' then trim(a.CHSTREET) else '' end) +
    (case when trim(a.CHVILLAGEESTATE)<>'' then trim(a.CHVILLAGEESTATE) else '' end) +(case when trim(a.CHBUILDING)<>'' then trim(a.CHBUILDING) else '' end) +(case when trim(a.CHBLKNO)<>'' then trim(a.CHBLKNO) + '座' else '' end) +(case when trim(a.CHFLOORNO)<>'' then trim(a.CHFLOORNO) + '樓' else '' end)+(case when trim(a.CHFLATNO)<>'' then trim(a.CHFLATNO) + '室' else '' end) '家居地址',
    a.schfrom '來自幼稚園',
    (select list(string(SIB.CHNAME, ' ', SIB.CLASSCODE), ', ')
    from VW_STU_LATESTSTUDENT SIB
    left outer join TB_SCH_SCHCLASS SIBC
    on SIBC.SUID=SIB.SUID and SIBC.SCHYEAR=SIB.SCHYEAR and
    SIBC.SCHLEVEL=SIB.SCHLVL and SIBC.SCHSESSION=SIB.SCHSESS and
    SIBC.CLASSLEVEL=SIB.CLASSLVL and SIBC.CLASSCODE=SIB.CLASSCODE
    where SIB.SUID=a.SUID and SIB.SCHYEAR=a.SCHYEAR and
    SIB.SIBGRP<>-1 and SIB.SIBGRP=a.SIBGRP and
    SIB.STUID<>a.STUID
    ) '兄弟姊妹'
    from wsadmin.VW_STU_LATESTSTUDENT a
    left outer join TB_SCH_SCHCLASS s
    on s.SUID=a.SUID and s.SCHYEAR=a.SCHYEAR and
    s.SCHLEVEL=a.SCHLVL and s.SCHSESSION=a.SCHSESS and
    s.CLASSLEVEL=a.CLASSLVL and s.CLASSCODE=a.CLASSCODE
    left outer join TB_STU_PARENT guard
    on guard.SUID = a.SUID and guard.STUID=a.STUID and
    guard.GUARDIANIND='1'
    left outer join TB_STU_PARENT m on
    a.SUID = m.SUID and a.STUID = m.STUID and m.RELATION = '02'
    left outer join TB_STU_PARENT f on
    a.SUID = f.SUID and a.STUID = f.STUID and f.RELATION = '01'
    left outer join wsadmin.TB_STU_STUSCHREC t on
    a.SUID=t.SUID and a.STUID=t.STUID and a.FIRSTATTDATE = t.FIRSTATTDATE and t.SCHYEAR=(SELECT MIN(SCHYEAR)FROM wsadmin.TB_STU_STUSCHREC U WHERE t.SUID = U.SUID AND t.STUID = U.STUID)
    left outer join TB_HSE_COMMON n on
    a.SUID=n.SUID and n.CODE_ID=a.Nationality and n.TB_ID='NATION'
    left outer join TB_HSE_COMMON h on
    a.SUID=h.SUID and h.CODE_ID=a.SCHHOUSE and
    h.TB_ID='SCHHUS'
    left outer join
    (select STUID, min(FIRSTATTDATE) 'FIRSTATTDATE' from wsadmin.TB_STU_STUSCHREC group by STUID) d
    on d.STUID = a.STUID
    where a.SCHYEAR=?
    order by a.SUID, a.SCHLVL, a.SCHSESS, a.CLASSLVL, a.CLASSCODE, a.CLASSNO
     
    #1 tr-wong_sir, 2020-01-08
  2. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    97
    讚:
    0
    你好,

    發現如果table 的alias 是 a 就出現問題, 可以嘗試把 a. 改為 z.
    而 wsadmin.VW_STU_LATESTSTUDENT a 改為 wsadmin.VW_STU_LATESTSTUDENT z 就可以了。

    我也改了, 你可以試試

    select
    z.CLASSLVL,
    s.CLASSNAME '班別',
    z.CLASSNO '學號',
    z.strn,
    z.regno,

    z.ENNAME '英名姓名',
    z.CHNAME '中文姓名',
    z.SEX '性別',

    dateformat(z.DOB, 'DD/MM/YYYY') as '出生日期',
    z.STRN '學生編號',
    z.HOMETEL '家居電話',
    guard.CHNAME as '監護人',
    guard.EMERGENCYPHONE '緊急電話',

    f.chname '父親姓名',
    f.phone '父親電話',

    m.chname '母親姓名',
    m.phone '母親電話',

    (case when trim(z.CHDISTRICT)<>'' then trim(z.CHDISTRICT) else '' end) +
    (case when trim(z.CHSTREET)<>'' then trim(z.CHSTREET) else '' end) +
    (case when trim(z.CHVILLAGEESTATE)<>'' then trim(z.CHVILLAGEESTATE) else '' end) +(case when trim(z.CHBUILDING)<>'' then trim(z.CHBUILDING) else '' end) +(case when trim(z.CHBLKNO)<>'' then trim(z.CHBLKNO) + '座' else '' end) +(case when trim(z.CHFLOORNO)<>'' then trim(z.CHFLOORNO) + '樓' else '' end)+(case when trim(z.CHFLATNO)<>'' then trim(z.CHFLATNO) + '室' else '' end) '家居地址',
    z.schfrom '來自幼稚園',
    (select list(string(SIB.CHNAME, ' ', SIB.CLASSCODE), ', ')
    from VW_STU_LATESTSTUDENT SIB
    left outer join TB_SCH_SCHCLASS SIBC
    on SIBC.SUID=SIB.SUID and SIBC.SCHYEAR=SIB.SCHYEAR and
    SIBC.SCHLEVEL=SIB.SCHLVL and SIBC.SCHSESSION=SIB.SCHSESS and
    SIBC.CLASSLEVEL=SIB.CLASSLVL and SIBC.CLASSCODE=SIB.CLASSCODE
    where SIB.SUID=z.SUID and SIB.SCHYEAR=z.SCHYEAR and
    SIB.SIBGRP<>-1 and SIB.SIBGRP=z.SIBGRP and
    SIB.STUID<>z.STUID
    ) '兄弟姊妹'
    from wsadmin.VW_STU_LATESTSTUDENT z
    left outer join TB_SCH_SCHCLASS s
    on s.SUID=z.SUID and s.SCHYEAR=z.SCHYEAR and
    s.SCHLEVEL=z.SCHLVL and s.SCHSESSION=z.SCHSESS and
    s.CLASSLEVEL=z.CLASSLVL and s.CLASSCODE=z.CLASSCODE
    left outer join TB_STU_PARENT guard
    on guard.SUID = z.SUID and guard.STUID=z.STUID and
    guard.GUARDIANIND='1'
    left outer join TB_STU_PARENT m on
    z.SUID = m.SUID and z.STUID = m.STUID and m.RELATION = '02'
    left outer join TB_STU_PARENT f on
    z.SUID = f.SUID and z.STUID = f.STUID and f.RELATION = '01'
    left outer join wsadmin.TB_STU_STUSCHREC t on
    z.SUID=t.SUID and z.STUID=t.STUID and z.FIRSTATTDATE = t.FIRSTATTDATE and t.SCHYEAR=(SELECT MIN(SCHYEAR)FROM wsadmin.TB_STU_STUSCHREC U WHERE t.SUID = U.SUID AND t.STUID = U.STUID)
    left outer join TB_HSE_COMMON n on
    z.SUID=n.SUID and n.CODE_ID=z.Nationality and n.TB_ID='NATION'
    left outer join TB_HSE_COMMON h on
    z.SUID=h.SUID and h.CODE_ID=z.SCHHOUSE and
    h.TB_ID='SCHHUS'
    left outer join
    (select STUID, min(FIRSTATTDATE) 'FIRSTATTDATE' from wsadmin.TB_STU_STUSCHREC group by STUID) d
    on d.STUID = z.STUID
    where z.SCHYEAR=?
    order by z.SUID, z.SCHLVL, z.SCHSESS, z.CLASSLVL, z.CLASSCODE, z.CLASSNO
     
    #2 edb-catherinewschan, 2020-01-08
  3. 55019624

    tr-wong_sir
    Expand Collapse

    文章:
    60
    讚:
    0
    上述報表的問題,已能解決,亦能重新獲得聯絡資料


    但本校仍有不少報表,
    出現相同error message,

    'The request may contains malicious code.
    Please contact the system administrator.'

    例如:
    select
    s.CLASSNAME 'Class',
    a.CLASSNO 'Class No',
    t.schyear 'Year entered School',
    t.classcode 'Class entered school',
    dateformat(d.FIRSTATTDATE, 'DD/MM/YYYY') as '入學日期',
    a.CHNAME 'Name (Chi)',
    a.ENNAME 'Name (Eng)',
    a.SEX,
    a.REGNO,
    a.STRN,
    dateformat(a.DOB, 'DD/MM/YYYY') as DOB,
    a.BIRTHCERT,
    a.HKID,
    a.POB,
    a.PLACEOFORIGIN as 'Origin',
    n.CH_DES as 'Nationality',
    h.CH_DES as 'SCHHOUSE',
    a.HOMETEL,
    guard.CHNAME as 'Guardian (Chi)',
    guard.EMERGENCYPHONE,
    guard.occupation,
    f.chname,
    f.phone,
    f.occupation,
    m.chname,
    m.phone,
    m.occupation,
    a.SCHFROM,
    (case when trim(a.CHDISTRICT)<>'' then trim(a.CHDISTRICT) else '' end) +
    (case when trim(a.CHSTREET)<>'' then trim(a.CHSTREET) else '' end) +
    (case when trim(a.CHVILLAGEESTATE)<>'' then trim(a.CHVILLAGEESTATE) else '' end) +(case when trim(a.CHBUILDING)<>'' then trim(a.CHBUILDING) else '' end) +(case when trim(a.CHBLKNO)<>'' then trim(a.CHBLKNO) + '座' else '' end) +(case when trim(a.CHFLOORNO)<>'' then trim(a.CHFLOORNO) + '樓' else '' end)+(case when trim(a.CHFLATNO)<>'' then trim(a.CHFLATNO) + '室' else '' end) '中文地址',
    (case when trim(a.ENFLATNO)<>'' then 'Room ' + trim(a.ENFLATNO) + ', ' else '' end) + (case when trim(a.ENFLOORNO)<>'' then trim(a.ENFLOORNO) + '/F, ' else '' end) +
    (case when trim(a.ENBLKNO)<>'' then 'Block ' + trim(a.ENBLKNO) + ', ' else '' end) + (case when trim(a.ENBUILDING)<>'' then trim(a.ENBUILDING) + ', ' else '' end) +
    (case when trim(a.ENVILLAGEESTATE)<>'' then trim(a.ENVILLAGEESTATE) + ', ' else '' end) + (case when trim(a.ENSTREET)<>'' then trim(a.ENSTREET) + ', ' else '' end) + (case when trim(a.ENDISTRICT)<>'' then trim(a.ENDISTRICT) + '.' else '' end)
    '英文地址'
    from VW_STU_LATESTSTUDENT a
    left outer join TB_SCH_SCHCLASS s
    on s.SUID=a.SUID and s.SCHYEAR=a.SCHYEAR and s.SCHLEVEL=a.SCHLVL and s.SCHSESSION=a.SCHSESS and s.CLASSLEVEL=a.CLASSLVL and s.CLASSCODE=a.CLASSCODE
    left outer join TB_STU_PARENT guard
    on guard.SUID = a.SUID and guard.STUID=a.STUID and guard.GUARDIANIND='1'
    left outer join TB_STU_PARENT m on
    a.SUID = m.SUID and a.STUID = m.STUID and m.RELATION = '02'
    left outer join TB_STU_PARENT f on
    a.SUID = f.SUID and a.STUID = f.STUID and f.RELATION = '01'
    left outer join TB_STU_STUSCHREC t on
    a.SUID=t.SUID and a.STUID=t.STUID and t.SCHYEAR=(SELECT MIN(SCHYEAR)FROM TB_STU_STUSCHREC U WHERE t.SUID = U.SUID AND t.STUID = U.STUID)
    left outer join TB_HSE_COMMON n on
    a.SUID=n.SUID and n.CODE_ID=a.Nationality and n.TB_ID='NATION'
    left outer join TB_HSE_COMMON h on
    a.SUID=h.SUID and h.CODE_ID=a.SCHHOUSE and
    h.TB_ID='SCHHUS'
    left outer join
    (select STUID, min(FIRSTATTDATE) 'FIRSTATTDATE' from TB_STU_STUSCHREC group by STUID) d
    on d.STUID = a.STUID
    where a.SCHYEAR=? and a.areacode=''
    order by a.SUID, a.SCHLVL, a.SCHSESS, a.CLASSLVL, a.CLASSCODE, a.CLASSNO


    用了上述方法,
    將alias a 轉為 z

    select
    s.CLASSNAME 'Class',
    z.CLASSNO 'Class No',
    t.schyear 'Year entered School',
    t.classcode 'Class entered school',
    dateformat(d.FIRSTATTDATE, 'DD/MM/YYYY') as '入學日期',
    z.CHNAME 'Name (Chi)',
    z.ENNAME 'Name (Eng)',
    z.SEX,
    z.REGNO,
    z.STRN,
    dateformat(z.DOB, 'DD/MM/YYYY') as DOB,
    z.BIRTHCERT,
    z.HKID,
    z.POB,
    z.PLACEOFORIGIN as 'Origin',
    n.CH_DES as 'Nationality',
    h.CH_DES as 'SCHHOUSE',
    z.HOMETEL,
    guard.CHNAME as 'Guardian (Chi)',
    guard.EMERGENCYPHONE,
    guard.occupation,
    f.chname,
    f.phone,
    f.occupation,
    m.chname,
    m.phone,
    m.occupation,
    z.SCHFROM,
    (case when trim(z.CHDISTRICT)<>'' then trim(z.CHDISTRICT) else '' end) +
    (case when trim(z.CHSTREET)<>'' then trim(z.CHSTREET) else '' end) +
    (case when trim(z.CHVILLAGEESTATE)<>'' then trim(z.CHVILLAGEESTATE) else '' end) +(case when trim(z.CHBUILDING)<>'' then trim(z.CHBUILDING) else '' end) +(case when trim(z.CHBLKNO)<>'' then trim(z.CHBLKNO) + '座' else '' end) +(case when trim(z.CHFLOORNO)<>'' then trim(z.CHFLOORNO) + '樓' else '' end)+(case when trim(z.CHFLATNO)<>'' then trim(z.CHFLATNO) + '室' else '' end) '中文地址',
    (case when trim(z.ENFLATNO)<>'' then 'Room ' + trim(z.ENFLATNO) + ', ' else '' end) + (case when trim(z.ENFLOORNO)<>'' then trim(z.ENFLOORNO) + '/F, ' else '' end) +
    (case when trim(z.ENBLKNO)<>'' then 'Block ' + trim(z.ENBLKNO) + ', ' else '' end) + (case when trim(z.ENBUILDING)<>'' then trim(z.ENBUILDING) + ', ' else '' end) +
    (case when trim(z.ENVILLAGEESTATE)<>'' then trim(z.ENVILLAGEESTATE) + ', ' else '' end) + (case when trim(z.ENSTREET)<>'' then trim(z.ENSTREET) + ', ' else '' end) + (case when trim(z.ENDISTRICT)<>'' then trim(z.ENDISTRICT) + '.' else '' end)
    '英文地址'
    from VW_STU_LATESTSTUDENT a
    left outer join TB_SCH_SCHCLASS s
    on s.SUID=z.SUID and s.SCHYEAR=z.SCHYEAR and s.SCHLEVEL=z.SCHLVL and s.SCHSESSION=z.SCHSESS and s.CLASSLEVEL=z.CLASSLVL and s.CLASSCODE=z.CLASSCODE
    left outer join TB_STU_PARENT guard
    on guard.SUID = z.SUID and guard.STUID=z.STUID and guard.GUARDIANIND='1'
    left outer join TB_STU_PARENT m on
    z.SUID = m.SUID and z.STUID = m.STUID and m.RELATION = '02'
    left outer join TB_STU_PARENT f on
    z.SUID = f.SUID and z.STUID = f.STUID and f.RELATION = '01'
    left outer join TB_STU_STUSCHREC t on
    z.SUID=t.SUID and z.STUID=t.STUID and t.SCHYEAR=(SELECT MIN(SCHYEAR)FROM TB_STU_STUSCHREC U WHERE t.SUID = U.SUID AND t.STUID = U.STUID)
    left outer join TB_HSE_COMMON n on
    z.SUID=n.SUID and n.CODE_ID=z.Nationality and n.TB_ID='NATION'
    left outer join TB_HSE_COMMON h on
    z.SUID=h.SUID and h.CODE_ID=z.SCHHOUSE and
    h.TB_ID='SCHHUS'
    left outer join
    (select STUID, min(FIRSTATTDATE) 'FIRSTATTDATE' from TB_STU_STUSCHREC group by STUID) d
    on d.STUID = z.STUID
    where z.SCHYEAR=? and z.areacode=''
    order by z.SUID, z.SCHLVL, z.SCHSESS, z.CLASSLVL, z.CLASSCODE, z.CLASSNO


    未能獲得資料
    是否每個報表都可能需用不同方法?
     
    #3 tr-wong_sir, 2020-01-09
  4. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    97
    讚:
    0
    未改好, VW_STU_LATESTSTUDENT a 要改為 VW_STU_LATESTSTUDENT z

    select
    s.CLASSNAME 'Class',
    z.CLASSNO 'Class No',
    t.schyear 'Year entered School',
    t.classcode 'Class entered school',
    dateformat(d.FIRSTATTDATE, 'DD/MM/YYYY') as '入學日期',
    z.CHNAME 'Name (Chi)',
    z.ENNAME 'Name (Eng)',
    z.SEX,
    z.REGNO,
    z.STRN,
    dateformat(z.DOB, 'DD/MM/YYYY') as DOB,
    z.BIRTHCERT,
    z.HKID,
    z.POB,
    z.PLACEOFORIGIN as 'Origin',
    n.CH_DES as 'Nationality',
    h.CH_DES as 'SCHHOUSE',
    z.HOMETEL,
    guard.CHNAME as 'Guardian (Chi)',
    guard.EMERGENCYPHONE,
    guard.occupation,
    f.chname,
    f.phone,
    f.occupation,
    m.chname,
    m.phone,
    m.occupation,
    z.SCHFROM,
    (case when trim(z.CHDISTRICT)<>'' then trim(z.CHDISTRICT) else '' end) +
    (case when trim(z.CHSTREET)<>'' then trim(z.CHSTREET) else '' end) +
    (case when trim(z.CHVILLAGEESTATE)<>'' then trim(z.CHVILLAGEESTATE) else '' end) +(case when trim(z.CHBUILDING)<>'' then trim(z.CHBUILDING) else '' end) +(case when trim(z.CHBLKNO)<>'' then trim(z.CHBLKNO) + '座' else '' end) +(case when trim(z.CHFLOORNO)<>'' then trim(z.CHFLOORNO) + '樓' else '' end)+(case when trim(z.CHFLATNO)<>'' then trim(z.CHFLATNO) + '室' else '' end) '中文地址',
    (case when trim(z.ENFLATNO)<>'' then 'Room ' + trim(z.ENFLATNO) + ', ' else '' end) + (case when trim(z.ENFLOORNO)<>'' then trim(z.ENFLOORNO) + '/F, ' else '' end) +
    (case when trim(z.ENBLKNO)<>'' then 'Block ' + trim(z.ENBLKNO) + ', ' else '' end) + (case when trim(z.ENBUILDING)<>'' then trim(z.ENBUILDING) + ', ' else '' end) +
    (case when trim(z.ENVILLAGEESTATE)<>'' then trim(z.ENVILLAGEESTATE) + ', ' else '' end) + (case when trim(z.ENSTREET)<>'' then trim(z.ENSTREET) + ', ' else '' end) + (case when trim(z.ENDISTRICT)<>'' then trim(z.ENDISTRICT) + '.' else '' end)
    '英文地址'
    from VW_STU_LATESTSTUDENT z
    left outer join TB_SCH_SCHCLASS s
    on s.SUID=z.SUID and s.SCHYEAR=z.SCHYEAR and s.SCHLEVEL=z.SCHLVL and s.SCHSESSION=z.SCHSESS and s.CLASSLEVEL=z.CLASSLVL and s.CLASSCODE=z.CLASSCODE
    left outer join TB_STU_PARENT guard
    on guard.SUID = z.SUID and guard.STUID=z.STUID and guard.GUARDIANIND='1'
    left outer join TB_STU_PARENT m on
    z.SUID = m.SUID and z.STUID = m.STUID and m.RELATION = '02'
    left outer join TB_STU_PARENT f on
    z.SUID = f.SUID and z.STUID = f.STUID and f.RELATION = '01'
    left outer join TB_STU_STUSCHREC t on
    z.SUID=t.SUID and z.STUID=t.STUID and t.SCHYEAR=(SELECT MIN(SCHYEAR)FROM TB_STU_STUSCHREC U WHERE t.SUID = U.SUID AND t.STUID = U.STUID)
    left outer join TB_HSE_COMMON n on
    z.SUID=n.SUID and n.CODE_ID=z.Nationality and n.TB_ID='NATION'
    left outer join TB_HSE_COMMON h on
    z.SUID=h.SUID and h.CODE_ID=z.SCHHOUSE and
    h.TB_ID='SCHHUS'
    left outer join
    (select STUID, min(FIRSTATTDATE) 'FIRSTATTDATE' from TB_STU_STUSCHREC group by STUID) d
    on d.STUID = z.STUID
    where z.SCHYEAR=? and z.areacode=''
    order by z.SUID, z.SCHLVL, z.SCHSESS, z.CLASSLVL, z.CLASSCODE, z.CLASSNO
     
    #4 edb-catherinewschan, 2020-01-14
  5. 55019624

    tr-wong_sir
    Expand Collapse

    文章:
    60
    讚:
    0
     
  6. 55019624

    tr-wong_sir
    Expand Collapse

    文章:
    60
    讚:
    0

    已能抽取有關資料

    謝謝