SQL error in executing SQL

本文由 ksk-admin 在 2020-03-09 發表於 "WebSAMS 討論區" 討論區

  1. 10932608

    ksk-admin
    Expand Collapse

    文章:
    11
    讚:
    0
    WEBSAMS > Security Error

    執行時出現以下句子
    The request may contains malicious code.
    Please contact the system administrator.
    之前執行都冇問題, 請指教, 謝謝

    select
    a.SCHYEAR,
    c.CLASSNAME,
    b.CLASSNO,
    b.chNAME,
    b.regno,
    trim((case when trim(a.PREFIX) <> ''
    then trim(a.PREFIX)
    else ''
    end) +
    (case when trim(a.PREFIX) <> '' and
    (right(trim(a.PREFIX),1) between char(33) and char(126) or
    left(trim(a.DESCRIPTIONSTR),1) between char(33) and char(126) or
    left(trim(h.CH_DES),1) between char(33) and char(126))
    then ' '
    else ''
    end) +
    (case when g.TOTAL>0
    then (case when g.NONATTTYPE = 'ABSNT'
    then '缺席' + cast(cast(g.TOTAL as numeric(10,1)) as text) + '天'
    else '遲到' + cast(g.TOTAL as text) + '次'
    end)
    when a.NAAOFFEREDID>0
    then (case when f.NAAPOSTCODE is null and f.NAAPERFCODE is null
    then i.CH_DES
    when f.NAAPOSTCODE is not null and f.NAAPERFCODE is null
    then i.CH_DES + j.CH_DES
    when f.NAAPOSTCODE is null and f.NAAPERFCODE is not null
    then i.CH_DES + ' (' + k.CH_DES + ')'
    else i.CH_DES + j.CH_DES + ' (' + k.CH_DES + ')'
    end)
    else (case when a.DESCRIPTIONCODE is not null and trim(a.DESCRIPTIONCODE) <> ''
    then trim(h.CH_DES)
    else trim(a.DESCRIPTIONSTR)
    end)
    end) +
    (case when trim(a.POSITION) <> '' and
    (right(trim(a.DESCRIPTIONSTR),1) between char(33) and char(126) or
    right(trim(h.CH_DES),1) between char(33) and char(126) or
    left(trim(a.POSITION),1) between char(33) and char(126))
    then ' '
    else ''
    end) +
    (case when trim(a.POSITION) <> ''
    then trim(a.POSITION)
    else ''
    end) +
    (case when d.SCHEME = 'M'
    then (case when a.LVL1>0
    then ' ' + (case when a.ANPTYPE = 'A' then n.AWD1CHIDESC
    else n.PUNISH1CHIDESC end) + ' ' + cast(a.LVL1 as text) + ' 次'
    else ''
    end) +
    (case when a.LVL2>0
    then ' ' + (case when a.ANPTYPE = 'A' then n.AWD2CHIDESC
    else n.PUNISH2CHIDESC end) + ' ' + cast(a.LVL2 as text) + ' 次'
    else ''
    end) +
    (case when a.LVL3>0
    then ' ' + (case when a.ANPTYPE = 'A' then n.AWD3CHIDESC
    else n.PUNISH3CHIDESC end) + ' ' + cast(a.LVL3 as text) + ' 次'
    else ''
    end) +
    (case when a.LVL4>0
    then ' ' + (case when a.ANPTYPE = 'A' then n.AWD4CHIDESC
    else n.PUNISH4CHIDESC end) + ' ' + cast(a.LVL4 as text) + ' 次'
    else ''
    end) +
    (case when a.LVL5>0
    then ' ' + (case when a.ANPTYPE = 'A' then n.AWD5CHIDESC
    else n.PUNISH5CHIDESC end) + ' ' + cast(a.LVL5 as text) + ' 次'
    else ''
    end)
    else ''
    end)) 'EVENT_CH',
    dateformat(a.ANPDATE, 'DD/MM/YYYY') 'ANPDATE'
    from TB_STU_ANPSTUREC a
    join VW_STU_LATESTSTUDENT b
    on a.SUID = b.SUID and a.STUID = b.STUID and a.SCHYEAR = b.SCHYEAR
    join TB_SCH_SCHCLASS c
    on a.SUID = c.SUID and b.SCHYEAR = c.SCHYEAR and b.SCHLVL = c.SCHLEVEL and
    b.SCHSESS = c.SCHSESSION and b.CLASSLVL = c.CLASSLEVEL and b.CLASSCODE = c.CLASSCODE
    join TB_STU_ANPSCHEME d
    on a.SUID = d.SUID and a.SCHYEAR = d.SCHYEAR
    left outer join TB_NAA_NAAOFFERED e
    on a.SUID = e.SUID and a.SCHYEAR = e.SCHYEAR and a.NAAOFFEREDID = e.NAAOFFEREDID
    left outer join TB_NAA_STUDENTNAA f
    on a.SUID = f.SUID and a.STUID = f.STUID and a.NAAOFFEREDID = f.NAAOFFEREDID
    left outer join
    (select SUID, SCHYEAR, STUID, ANPRECID, NONATTTYPE, NONATTCOUNT, sum(NONATTCOUNT)'TOTAL'
    from TB_ATT_NONATT
    where ANPRECID is not null
    group by SUID, SCHYEAR, STUID, ANPRECID, NONATTTYPE, NONATTCOUNT) g
    on a.SUID = g.SUID and a.SCHYEAR = g.SCHYEAR and a.STUID = g.STUID and a.ANPRECID = g.ANPRECID
    left outer join TB_HSE_COMMON h
    on a.SUID = h.SUID and a.DESCRIPTIONCODE = h.CODE_ID and
    ((a.ANPTYPE = 'A' and h.TB_ID = 'PRIZE') or (a.ANPTYPE = 'P' and h.TB_ID = 'PSHMNT'))
    left outer join TB_HSE_COMMON i on
    a.SUID = i.SUID and e.NAACODE = i.CODE_ID and
    ((i.TB_ID = 'ECACD' and e.NAATYPE = 'E') or
    (i.TB_ID = 'INTERSCH' and e.NAATYPE = 'I') or
    (i.TB_ID = 'SEVPST' and e.NAATYPE = 'S'))
    left outer join TB_HSE_COMMON j on
    a.SUID = j.SUID and f.NAAPOSTCODE = j.CODE_ID and j.TB_ID = 'ECAPST'
    left outer join TB_HSE_COMMON k on
    a.SUID = k.SUID and f.NAAPERFCODE = k.CODE_ID and k.TB_ID = 'ECAPFM'
    left outer join TB_HSE_COMMON l on
    a.SUID = l.SUID and a.AWARDFROM = l.CODE_ID and l.TB_ID = 'AWARDF'
    left outer join TB_HSE_COMMON m on
    a.SUID = m.SUID and a.AWARDCAT = m.CODE_ID and m.TB_ID = 'AWARDC'
    left outer join TB_STU_ANPSCHEMEDETAILMD n on
    a.SUID = n.SUID and a.SCHYEAR = n.SCHYEAR
    left outer join VW_ASR_STAFF o on
    a.SUID = o.SUID and a.STAFFCODE = o.STAFFCODE
    where a.ANPTYPE = 'A' and b.CLASSLVL in ('s1','s2','s3','s4','s5','s6') and a.anpdate >= 20190901 and a.anpdate <= 20200306 and a.DESCRIPTIONCODE in ('38','39','40','53','54','55','56','57','58','59','60'
    ,'61','62','63','64','65','66','67')
    order by b.SCHLVL, b.SCHSESS, b.CLASSLVL, b.CLASSCODE, b.CLASSNO, a.ANPTYPE, RPTCRDPRTSEQ, a.ANPDATE, a.DESCRIPTIONCODE, a.ANPRECID
     
    #1 ksk-admin, 2020-03-09
    Last edited: 2020-03-09
  2. 57873296

    edb-rn
    Expand Collapse

    文章:
    231
    讚:
    0
    老師,請參考以下,

    只需要把table 的alias a. 改為 z.
    就可以成功抽取資料。

    select
    z.SCHYEAR,
    c.CLASSNAME,
    b.CLASSNO,
    b.chNAME,
    b.regno,
    trim((case when trim(z.PREFIX) <> ''
    then trim(z.PREFIX)
    else ''
    end) +
    (case when trim(z.PREFIX) <> '' and
    (right(trim(z.PREFIX),1) between char(33) and char(126) or
    left(trim(z.DESCRIPTIONSTR),1) between char(33) and char(126) or
    left(trim(h.CH_DES),1) between char(33) and char(126))
    then ' '
    else ''
    end) +
    (case when g.TOTAL>0
    then (case when g.NONATTTYPE = 'ABSNT'
    then '缺席' + cast(cast(g.TOTAL as numeric(10,1)) as text) + '天'
    else '遲到' + cast(g.TOTAL as text) + '次'
    end)
    when z.NAAOFFEREDID>0
    then (case when f.NAAPOSTCODE is null and f.NAAPERFCODE is null
    then i.CH_DES
    when f.NAAPOSTCODE is not null and f.NAAPERFCODE is null
    then i.CH_DES + j.CH_DES
    when f.NAAPOSTCODE is null and f.NAAPERFCODE is not null
    then i.CH_DES + ' (' + k.CH_DES + ')'
    else i.CH_DES + j.CH_DES + ' (' + k.CH_DES + ')'
    end)
    else (case when z.DESCRIPTIONCODE is not null and trim
    (z.DESCRIPTIONCODE) <> ''
    then trim(h.CH_DES)
    else trim(z.DESCRIPTIONSTR)
    end)
    end) +
    (case when trim(z.POSITION) <> '' and
    (right(trim(z.DESCRIPTIONSTR),1) between char(33) and char(126) or
    right(trim(h.CH_DES),1) between char(33) and char(126) or
    left(trim(z.POSITION),1) between char(33) and char(126))
    then ' '
    else ''
    end) +
    (case when trim(z.POSITION) <> ''
    then trim(z.POSITION)
    else ''
    end) +
    (case when d.SCHEME = 'M'
    then (case when z.LVL1>0
    then ' ' + (case when z.ANPTYPE = 'A' then n.AWD1CHIDESC
    else n.PUNISH1CHIDESC end) + ' ' + cast(z.LVL1 as text) + ' 次'
    else ''
    end) +
    (case when z.LVL2>0
    then ' ' + (case when z.ANPTYPE = 'A' then n.AWD2CHIDESC
    else n.PUNISH2CHIDESC end) + ' ' + cast(z.LVL2 as text) + ' 次'
    else ''
    end) +
    (case when z.LVL3>0
    then ' ' + (case when z.ANPTYPE = 'A' then n.AWD3CHIDESC
    else n.PUNISH3CHIDESC end) + ' ' + cast(z.LVL3 as text) + ' 次'
    else ''
    end) +
    (case when z.LVL4>0
    then ' ' + (case when z.ANPTYPE = 'A' then n.AWD4CHIDESC
    else n.PUNISH4CHIDESC end) + ' ' + cast(z.LVL4 as text) + ' 次'
    else ''
    end) +
    (case when z.LVL5>0
    then ' ' + (case when z.ANPTYPE = 'A' then n.AWD5CHIDESC
    else n.PUNISH5CHIDESC end) + ' ' + cast(z.LVL5 as text) + ' 次'
    else ''
    end)
    else ''
    end)) 'EVENT_CH',
    dateformat(z.ANPDATE, 'DD/MM/YYYY') 'ANPDATE'
    from TB_STU_ANPSTUREC z
    join VW_STU_LATESTSTUDENT b
    on z.SUID = b.SUID and z.STUID = b.STUID and z.SCHYEAR = b.SCHYEAR
    join TB_SCH_SCHCLASS c
    on z.SUID = c.SUID and b.SCHYEAR = c.SCHYEAR and b.SCHLVL =
    c.SCHLEVEL and
    b.SCHSESS = c.SCHSESSION and b.CLASSLVL = c.CLASSLEVEL and
    b.CLASSCODE = c.CLASSCODE
    join TB_STU_ANPSCHEME d
    on z.SUID = d.SUID and z.SCHYEAR = d.SCHYEAR
    left outer join TB_NAA_NAAOFFERED e
    on z.SUID = e.SUID and z.SCHYEAR = e.SCHYEAR and z.NAAOFFEREDID =
    e.NAAOFFEREDID
    left outer join TB_NAA_STUDENTNAA f
    on z.SUID = f.SUID and z.STUID = f.STUID and z.NAAOFFEREDID =
    f.NAAOFFEREDID
    left outer join
    (select SUID, SCHYEAR, STUID, ANPRECID, NONATTTYPE, NONATTCOUNT,
    sum(NONATTCOUNT)'TOTAL'
    from TB_ATT_NONATT
    where ANPRECID is not null
    group by SUID, SCHYEAR, STUID, ANPRECID, NONATTTYPE, NONATTCOUNT) g
    on z.SUID = g.SUID and z.SCHYEAR = g.SCHYEAR and z.STUID = g.STUID
    and z.ANPRECID = g.ANPRECID
    left outer join TB_HSE_COMMON h
    on z.SUID = h.SUID and z.DESCRIPTIONCODE = h.CODE_ID and
    ((z.ANPTYPE = 'A' and h.TB_ID = 'PRIZE') or (z.ANPTYPE = 'P' and
    h.TB_ID = 'PSHMNT'))
    left outer join TB_HSE_COMMON i on
    z.SUID = i.SUID and e.NAACODE = i.CODE_ID and
    ((i.TB_ID = 'ECACD' and e.NAATYPE = 'E') or
    (i.TB_ID = 'INTERSCH' and e.NAATYPE = 'I') or
    (i.TB_ID = 'SEVPST' and e.NAATYPE = 'S'))
    left outer join TB_HSE_COMMON j on
    z.SUID = j.SUID and f.NAAPOSTCODE = j.CODE_ID and j.TB_ID =
    'ECAPST'
    left outer join TB_HSE_COMMON k on
    z.SUID = k.SUID and f.NAAPERFCODE = k.CODE_ID and k.TB_ID =
    'ECAPFM'
    left outer join TB_HSE_COMMON l on
    z.SUID = l.SUID and z.AWARDFROM = l.CODE_ID and l.TB_ID = 'AWARDF'
    left outer join TB_HSE_COMMON m on
    z.SUID = m.SUID and z.AWARDCAT = m.CODE_ID and m.TB_ID = 'AWARDC'
    left outer join TB_STU_ANPSCHEMEDETAILMD n on
    z.SUID = n.SUID and z.SCHYEAR = n.SCHYEAR
    left outer join VW_ASR_STAFF o on
    z.SUID = o.SUID and z.STAFFCODE = o.STAFFCODE
    where z.ANPTYPE = 'A' and b.CLASSLVL in
    ('s1','s2','s3','s4','s5','s6') and z.anpdate >= 20190901 and
    z.anpdate <= 20200306 and z.DESCRIPTIONCODE in
    ('38','39','40','53','54','55','56','57','58','59','60'
    ,'61','62','63','64','65','66','67')
    order by b.SCHLVL, b.SCHSESS, b.CLASSLVL, b.CLASSCODE, b.CLASSNO,
    z.ANPTYPE, RPTCRDPRTSEQ, z.ANPDATE, z.DESCRIPTIONCODE, z.ANPRECID