SQL SQL Query contains malicious code

本文由 drizzt 在 2020-07-29 發表於 "WebSAMS 討論區" 討論區

標籤:
  1. 55072874

    drizzt
    Expand Collapse

    文章:
    6
    讚:
    0
    有一個一直用開的 SQL Query, 昨天執行時出現錯誤 "The request may contains malicious code. Please contact the system administrator.", 請問是甚麼原因?


    select
    a.SCHFROM '以前就讀學校',
    a.LASTCLASSLVL '以前就讀班別',
    a.Schyear '年度',
    a.CLASSCODE '班別',
    a.CLASSNO '班號',
    a.CHNAME as '學生姓名',
    a.ENNAME as '英文姓名',
    a.sex as '姓別',
    a.CCC as 'CCC',
    a.POB as '出生地點',
    dateformat(a.admdate,'DD/MM/YYYY') as '入校日期',
    d.EN_DES '社', a.REGNO as'註冊號數',
    a.STRN, a.HKID '身份證號碼',
    dateformat(a.DOB, 'DD/MM/YYYY') as '出生日期',
    (datediff(day, a.dob, now())/365) '年齡',
    a.HOMETEL as 'Phone',
    a.mobileno as 'Mobile',
    a.EMAIL as 'Email',
    b.CH_DES'宗教',
    p1.ENNAME as '父親英文姓名',
    p1.CHNAME as '父親姓名',
    p1.PHONE as'父親電話',
    p1.EMERGENCYPHONE as'父親緊急聯絡電話',
    p1.EMAIL as'父親電郵',
    p2.ENNAME as '母親英文姓名',
    p2.CHNAME as '母親姓名',
    p2.PHONE as'母親電話',
    p2.EMERGENCYPHONE as '母親緊急聯絡電話',
    p2.EMAIL as'母親電郵',
    p3.ENNAME as '監護人英文姓名',
    p3.CHNAME as '監護人姓名',
    ctable.CH_DES as '監護人關係',
    p3.PHONE as'監護人電話',
    p3.EMERGENCYPHONE as '監護人緊急聯絡電話',
    p3.EMAIL as'監護人電郵',
    pg.ENNAME as '緊急聯絡人英文姓名',
    pg.CHNAME as '緊急聯絡人姓名',
    ctable2.CH_DES as '緊急聯絡人關係',
    pg.PHONE as'緊急聯絡人電話',
    pg.EMERGENCYPHONE as '緊急聯絡人緊急聯絡電話',
    pg.EMAIL as'緊急聯絡人電郵',
    (case when trim(a.enflatno)<>'' then
    'RM '+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)+'.'
    as '住址',
    (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)
    as '中文住址',
    g.CH_DES'區議會分區',
    (select list(string(SIBC.CLASSNAME, ' ', SIB.CHNAME), ', ')
    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) '兄弟姊妹',
    a.mobileno '學生手提電話',
    SIB.ENNAME as 'SIB英文姓名', SIB.CHNAME as 'SIB中文姓名', SIB.CLASSCODE as 'SIB班別'
    from VW_STU_LATESTSTUDENT a
    left outer join TB_HSE_COMMON b
    on b.CODE_ID = a.RELIGION and b.TB_ID = 'RELIG' and b.SUID = a.SUID
    left outer join TB_HSE_COMMON d
    on a.SUID=d.SUID and d.CODE_ID=a.SCHHOUSE and d.TB_ID='SCHHUS'
    left outer join TB_HSE_COMMON h
    on h.CODE_ID=a.POB and h.TB_ID='BIRCTY' and h.SUID=a.SUID
    left outer join TB_HSE_COMMON j
    on j.CODE_ID=a.NATIONALITY and j.TB_ID='NATION' and j.SUID=a.SUID
    left outer join TB_HSE_COMMON g
    on g.CODE_ID=a. DISTRICTCOUNCIL and g.TB_ID='HOMEDB' and g.SUID=a.SUID
    left outer join TB_STU_PARENT p1
    on p1.STUID = a.STUID and p1.SUID = a.SUID and P1.RELATION='01'
    left outer join TB_STU_PARENT p2
    on p2.STUID = a.STUID and p2.SUID = a.SUID and P2.RELATION='02'
    left outer join TB_STU_PARENT p3
    on p3.STUID = a.STUID and p3.SUID = a.SUID and P3.RELATION>'02'
    left outer join TB_STU_PARENT pg
    on pg.STUID = a.STUID and pg.SUID = a.SUID and pg.GUARDIANIND=1
    left outer join TB_HSE_COMMON ctable
    on ctable.TB_ID = 'RELATE' and p3.RELATION = ctable.CODE_ID
    left outer join TB_HSE_COMMON ctable2
    on ctable2.TB_ID = 'RELATE' and pg.RELATION = ctable2.CODE_ID
    left outer join VW_STU_LATESTSTUDENT SIB
    on SIB.SUID = a.SUID and SIB.SCHYEAR = a.SCHYEAR and SIB.SIBGRP<>-1 and SIB.SIBGRP = a.SIBGRP and SIB.STUID <> a.STUID
    left outer join TB_SCH_SCHCLASS SIBC
    on SIBC.SUID = SIB.SUID and SIBC.SCHYEAR = SIB.SCHYEAR and SIBC.SCHLEVEL = SIB.SCHLVL and SIBC.CLASSLEVEL = SIB.CLASSLVL and SIBC.CLASSCODE = SIB.CLASSCODE
    where a.SCHYEAR = ?
    order by a.CLASSCODE, a.CLASSNO
     
  2. 55072874

    drizzt
    Expand Collapse

    文章:
    6
    讚:
    0
    剛參考了其他討論, 將 alias a 改成 z 就冇問題, 是否以後不能用 a 做 alias?
     
  3. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    124
    讚:
    0
    其實WEBSAMS最新的build已經解決了用 a 做 alias的問題
     
    #3 edb-catherinewschan, 2020-08-04 , 10:45 下午