$30 off During Our Annual Pro Sale. View Details »

Automatic Whitelist Generation for SQL Queries Using Web Application Tests

Automatic Whitelist Generation for SQL Queries Using Web Application Tests

COMPSAC2019 NETSAP 2019: The 9th IEEE International Workshop on Network Technologies for Security, Administration and Protection

Komei Nomura

July 15, 2019
Tweet

More Decks by Komei Nomura

Other Decks in Research

Transcript

  1. Komei Nomura , Kenji Rikitake , Ryosuke Matsumoto
    1. Pepabo R&D Institute GMO pepabo, Inc. / 2. KRPEO / 3. SAKURA Research Center, SAKURA Internet Inc.
    2019.07.15 The 9th IEEE International Workshop on Network Technologies for Security, Administration and Protection
    Automatic Whitelist Generation for SQL
    Queries Using Web Application Tests
    1 1,2 3

    View Slide

  2. 1. Introduction
    2. Related works
    3. Proposed method
    4. Evaluation
    5. Conclusion
    2
    Table of contents

    View Slide

  3. 1.
    Introduction

    View Slide

  4. • Stealing confidential information from a database has become a severe
    vulnerability issue for web applications
    • e.g: SQL injection, OS command injection and so on
    • The attacks are caused by executing illegal queries to the database
    • The Illegal query is an unexpected query for web application developers
    • To prevent the attacks, illegal queries must be detected before they are
    executed in the database
    4
    Background

    View Slide

  5. • Blacklist method
    • define illegal query pattern in a list and detect queries which matched the list
    • Whitelist method
    • define normal query pattern in a list and detect queries which doesn’t
    matched the list
    5
    Illegal query detection method
    Using only the blacklist can't detect unknown illegal query
    → Using the whitelist is required to detect Illegal query which has unknown patterns

    View Slide

  6. • Developers manually create a whitelist of the queries issued by the web
    application
    • The large-scale web application issue enormous queries
    → Registering all queries in whitelist is difficult
    • Queries issued by the web application change with updating of the web
    application
    → Developers need to update the whitelist
    6
    Whitelist creation and its issue
    5IFNFUIPEJNQPTFTBOJNQSBDUJDBMCVSEFOPOEFWFMPQFST

    View Slide

  7. • Realization of a mechanism that
    • developers can create a whitelist without much effort
    • and detect illegal queries using it
    → The whitelist should be automatically generated according to changes of
    queries issued by a web application
    7
    Purpose of our research

    View Slide

  8. 2.
    Related works

    View Slide

  9. • A method generates a whitelist by collecting queries issued while the web
    application is running
    • The method can create a whitelist independently of the web application
    implementation
    • Programming language, Framework
    9
    Generating a whitelist using issued queries
    %BUBCBTF
    8FCBQQMJDBUJPO
    2VFSZ
    8IJUFMJTU
    %VSJOHXFCBQQMJDBUJPOJTSVOOJOH
    )551SFRVFTU

    View Slide

  10. • The method can’t detect illegal queries immediately after running the web
    application
    • need a period to collect queries during running the web application
    • The period which can’t detect illegal queries occurs frequently
    • Queries change frequently because web services are frequently updated
    10
    5IFXIJUFMJTUHFOFSBUJPOTIPVMECFEPOFCFGPSFSVOOJOHUIFXFCBQQMJDBUJPO
    Generating a whitelist using issued queries

    View Slide

  11. • A method generates a whitelist by analyzing the process of issuing the
    query in the web application source code
    • The method can generate a whitelist before web application running by
    using the source code as input
    11
    Generating a whitelist using static analysis
    "OBMZ[FS
    4PVSDFDPEF
    8IJUFMJTU
    #FGPSFXFCBQQMJDBUJPOSVOT

    View Slide

  12. • The method can’t be commonly used in multiple web application with different
    implementations
    • Source code analysis depends on the implementation of the web application
    • If web service is constructed various languages and frameworks,

    implementing an analyzer for each application impose high workload
    12
    8IJUFMJTUHFOFSBUJPOTIPVMECFQFSGPSNFEJOEFQFOEFOUMZPGUIFXFC
    BQQMJDBUJPOJNQMFNFOUBUJPO
    Generating a whitelist using static analysis

    View Slide

  13. 3.
    Proposed method

    View Slide

  14. 1. The whitelist generation should be done before running the web application
    • to detect illegal queries immediately after running the web application
    2. The whitelist generation should be performed independently of the web
    application implementation
    • to reduce the workload to implement for each web application
    14
    Requirements of proposed method

    View Slide

  15. • Automatic whitelist generation method using queries issued during testing
    • The whitelist generation incorporates into the development process
    using an automatic test
    • Database proxy collects the queries issued during testing
    15
    Proposed method

    View Slide

  16. 16
    Development process using automatic test
    %FWFMPQNFOU
    8SJUFUFTUDPEF
    %FQMPZUPTFSWFS
    &YFDVUFBVUPNBUJDUFTU
    /P
    :FT
    4UBSUBQQMJDBUJPO
    5FTU
    TVDDFFE
    "EEOFXGVODUJPOT
    .PEJGZFYJTUJOHGVODUJPOT
    8SJUFUFTUDBTFTBOEFYQFDUFESFTVMUJOUFTUDPEF
    &YFDVUFBMMUFTUTVTJOHUFTUDPEF
    $IFDLXIFUIFSUIFXFCBQQMJDBUJPOPQFSBUFTBTTQFDJpFE
    %FQMPZUIFXFCBQQMJDBUJPOUPTFSWFS

    View Slide

  17. 17
    Development process with whitelist generation
    5FTUDPEFJTDIBOHFEBDDPSEJOHUPUIFXFCBQQMJDBUJPO

    DIBOHFT

    ˠ8IJUFMJTUJTVQEBUFEBDDPSEJOHUPUIFDIBOHFT
    8IJUFMJTUJTHFOFSBUFECFGPSFSVOOJOHUIFXFCBQQMJDBUJPO

    ˠ5IFQSPQPTFENFUIPEDBOEFUFDUJMMFHBMRVFSJFTBGUFS

    SVOOJOHUIFXFCBQQMJDBUJPO
    %FWFMPQNFOU
    8SJUFUFTUDPEF
    %FQMPZUPTFSWFS
    /P
    :FT
    4UBSU8FCBQQMJDBUJPO
    5FTU
    TVDDFFEʁ
    $PMMFDURVFSJFT
    &YFDVUFBVUPNBUJDUFTU
    (FOFSBUFXIJUFMJTU
    5IFQSPQPTFENFUIPEDPMMFDURVFSJFT

    EVSJOHUFTUJOHBOEHFOFSBUFBXIJUFMJTU
    %FQMPZGPMMPXJOHJUFNUPTFSWFS
    w 5IFTPVSDFDPEFPGXFCBQQMJDBUJPO
    w 5IFXIJUFMJTU

    View Slide

  18. 18
    Whitelist generation
    %BUBCBTF
    8FCBQQMJDBUJPO %BUBCBTFQSPYZ
    8IJUFMJTU
    $PMMFDURVFSJFT
    $POWFSUJOUPRVFSZTUSVDUVSFUIBUSFQMBDF

    MJUFSBMTPGUIFRVFSZXJUIQMBDFIPMEFST

    3FHJTUFSUIFRVFSZTUSVDUVSFXJUIBXIJUFMJTU
    2VFSZ 2VFSZ
    4&-&$5'30.VTFST8)&3&JE
    4&-&$5'30.VTFST8)&3&JE
    &YBNQMFPGRVFSZTUSVDUVSF
    Collecting queries using the database proxy realize whitelist generation independent of the
    web application implementation

    View Slide

  19. 19
    %BUBCBTF
    8FCBQQMJDBUJPO
    %BUBCBTFQSPYZ
    %VSJOHXFCBQQMJDBUJPOJTSVOOJOH
    2VFSZ 2VFSZ
    *MMFHBMRVFSZ
    0VUQVU
    8IJUFMJTU
    Detection using the whitelist
    3FDFJWFRVFSZBOEDPOWFSUUIFRVFSZJOUPRVFSZTUSVDUVSF
    $IFDLXIFUIFSUIFRVFSZTUSVDUVSFJTPOUIFXIJUFMJTU
    *GUIFRVFSZTUSVDUVSFJTOPUPOUIFXIJUFMJTU 

    UIFRVFSZJTEFUFDUFEBTBOJMMFHBMRVFSZ

    View Slide

  20. 4.
    Evaluation

    View Slide

  21. • Define two indicators of detection accuracy
    • False positive means that normal query is determined as illegal
    • The normal query is an expected query issued by a web application
    receiving user input.
    • False negative means that illegal query is determined as normal
    • The illegal query is an unexpected query issued by attacks such as web
    application vulnerability attacks.
    21
    Indicator of detection accuracy

    View Slide

  22. • The relation of queries issued during testing and running affect the detection accuracy
    22
    Queries that cause false positive / negative
    #2VFSJFTEVSJOHSVOOJOH
    "2VFSJFTEVSJOHUFTUJOH
    • The reason for queries issued only
    during testing
    • Registering test data
    • Deleting all test data
    • The reason for queries issued
    only during running
    • Test cases are a subset of
    usage during running
    5IFDBVTFPGGBMTFOFHBUJWF 5IFDBVTFPGGBMTFQPTJUJWF

    View Slide

  23. • We verified the queries that cause false positive/negative in production
    • We obtained query log in production for 3 days of holidays
    • to remove the changes of queries issued by the web application
    • We ran tests of the web application that was running during the query log
    period and obtained the queries issued during testing
    23
    Experiment in production

    View Slide

  24. 24
    Experiment result
    #2VFSZTUSVDUVSFTJTTVFEJOQSPEVDUJPO
    "2VFSZTUSVDUVSFTJTTVFEJOUFTU
    5PUBMPGRVFSZTUSVDUVSFTJTTVFEJOUFTUBOEJOQSPEVDUJPOɿ
    5IFRVFSJFTUIBUDBVTF
    GBMTFQPTJUJWF
    5IFRVFSJFTUIBUDBVTF
    GBMTFOFHBUJWF

    View Slide

  25. • All queries in this red area were issued by the normal process
    • These queries are not issued in the test by lacking test
    case or skipping access to the database
    • Complementing queries lacking in the whitelist is necessary
    • Applying the proposed method only to the database table
    with confidential information is important
    • Reducing false positive by reducing the queries of the
    detection target
    25
    Consideration of false positive cases
    #2VFSZTUSVDUVSFTJTTVFEJOQSPEVDUJPO
    "2VFSZTUSVDUVSFTJTTVFEJOUFTU

    View Slide

  26. #2VFSZTUSVDUVSFTJTTVFEJOQSPEVDUJPO
    "2VFSZTUSVDUVSFTJTTVFEJOUFTU
    • Green area includes two categories of the query
    1. Queries issued not issued during the query log period
    2. Queries issued only in the test
    • Include a query that deletes all confidential data in the
    database table
    • The detection combined whitelist and blacklist is necessary
    • Registering queries handling a lot of data into the blacklist
    • e.g: query deleting all data in the database table
    26
    Consideration of false negative cases

    View Slide

  27. 5.
    Conclusion

    View Slide

  28. • The existing methods of automatic whitelist generation have issues that
    • can’t detect illegal queries immediately after running the web application
    • can’t be commonly used in multiple web application with different
    implementations
    • The proposed method solves these issues
    • by incorporating whitelist generation into the development process
    • by collecting queries during testing using the database proxy
    28
    Conclusion

    View Slide

  29. • The experimental results show that the proposed method causes false
    positive and false negative
    • Regarding false positive cases
    • Complementing queries lacking in the whitelist
    • Applying the proposed method only to the table with confidential
    information
    • Regarding false negative cases
    • Detection combining whitelist and blacklist for illegal queries
    29
    Conclusion

    View Slide