5.4. SQLi attack detection

5.4.1. Introduction

This tutorial shows how tu use Haka in order to detect SQL injection attacks (SQLi). SQLi are common web attacks that consit in injecting SQL commands through http requests leading to sensitive data disclosure or authentication scheme bypass.

Note that our goal is not to block 100% of SQLi attacks (with 0% false-positive rate) but to show how to build iteratively an sqli filtering policy thanks to haka capabilities.

5.4.2. How-to

This tutorial introduces a set of lua script files located at <haka_install_path>/share/haka/sample/sqli and which could be run using the hakapcap tool:

$ cd <haka_install_path>/share/haka/sample/qli
$ hakapcap sqli.pcap sqli-sample.lua

All the samples are self-documented.

5.4.3. Writing http rules

To write http rules, we need first to load the ipv4, tcp and http dissectors and set the next dissector to http when the tcp port is equal to 80 (this is done on connection establishment thanks to the tcp-connection-new hook). This is the purpose of the httpconfig.lua which is required by all the samples given in this tutorial.

------------------------------------
-- Loading dissectors
------------------------------------

require('protocol/ipv4')
require('protocol/tcp')
httplib = require('protocol/http')

------------------------------------
-- Setting next dissector
------------------------------------

haka.rule{
    hooks = { 'tcp-connection-new' },
    eval = function (self, pkt)
        if pkt.tcp.dstport == 80 then
            pkt.next_dissector = 'http'
        end
    end
}

-----------------------------------
-- Dumping request info
-----------------------------------

function dump_request(http)
    haka.log("sqli", "receiving http request")
    local uri = http.request.uri
    haka.log("sqli", "    uri: %s", uri)
    local cookies = http.request.headers['Cookie']
    if cookies then
        haka.log("sqli", "    cookies: %s", cookies)
    end
end

5.4.4. My first naive rule

The first example presents a naive rule which checks some malicious patterns against the whole uri. A score is updated whenever an sqli keywords is found (select, update, etc.). An alert is raised if the score exceeds a predefined threshold.

require('httpconfig')

------------------------------------
-- Malicious Patterns
------------------------------------

local keywords = {
    'select', 'insert', 'update', 'delete', 'union'
}

------------------------------------
-- SQLi Naive Rule
------------------------------------

haka.rule{
    -- Evaluation applies on upcoming requests
    hooks = { 'http-request' },
    eval = function (self, http)
        dump_request(http)

        local score = 0
        -- Http fields (uri, headers) are available through 'request' field
        local uri = http.request.uri

        for _, key in ipairs(keywords) do
            -- Check the whole uri against the list of malicious keywords
            if uri:find(key) then
                -- Update the score
                score = score + 4
            end
        end
        
        if score >= 8 then
            -- Raise an alert if the score exceeds a fixed threshold (compact format)
            haka.alert{
                description = string.format("SQLi attack detected with score %d", score),
                severity = 'high',
                confidence = 'low',
            }
            http:drop()
        end
    end
}

5.4.5. Anti-evasion

It is trivial to bypass the above rule with slight modifications on uri. For instance hiding a select keyword using comments (e.g. sel/*something*/ect) or simply using uppercase letters will bypass our naive rule. The script file sqli-decode.lua improves detection by applying first decoding functions on uri. This functions are defined in httpdecode.lua file.

require('httpconfig')
require('httpdecode')

------------------------------------
-- Malicious Patterns
------------------------------------

local keywords = {
    'select', 'insert', 'update', 'delete', 'union'
}

-- Still naive rule
haka.rule{
    hooks = { 'http-request' },
    eval = function (self, http)
        dump_request(http)

        local score = 0
        local uri = http.request.uri

        -- Apply all decoding functions on uri (percent-decode, uncomments, etc.)
        uri = decode_all(uri)
        for _, key in ipairs(keywords) do
            if uri:find(key) then
                score = score + 4
            end
        end

        if score >= 8 then
            haka.alert{
                description = string.format("SQLi attack detected with score %d", score),
                severity = 'high',
                confidence = 'low',
            }
            http:drop()
        end
    end
}

5.4.6. Fine-grained analysis

All the above rules check the malicious patterns against the whole uri. The purpose of this scenario (sqli-fine-grained.lua) is to leverage the http api in order to check the patterns against only subparts of the http request (query’s argument, list of cookies).

require('httpconfig')
require('httpdecode')

------------------------------------
-- Malicious Patterns
------------------------------------

local keywords = {
    'select', 'insert', 'update', 'delete', 'union'
}

------------------------------------
-- A Better Naive Rule ...
------------------------------------

haka.rule{
    hooks = { 'http-request' },
    eval = function (self, http)
        dump_request(http)

        local uri = http.request.uri
        local ck = http.request.headers['Cookie']

        -- Initialize the score for query's argument and cookies list
        -- Could be extend to check patterns in other http fields
        local where = {
            args = {
                -- Split query into list of (param-name, param-value) pairs
                value = httplib.uri.split(uri).args,
                score = 0
            },
            cookies = {
                -- Split comma-separated cookies into a list of (key, value)
                -- pairs
                value = httplib.cookies.split(ck),
                score = 0
            }
        }

        for k, v in pairs(where) do
            if v.value then
                -- Loop on each query param | cookie value
                for param, value in pairs(v.value) do
                    local decoded = decode_all(value)
                    for _, key in ipairs(keywords) do
                        if decoded:find(key) then
                            v.score = v.score + 4
                        end
                    end
                end
            end

            if v.score >= 8 then
                local conn = http.connection
                -- Report an alert (more info in alert)
                haka.alert{
                    description = string.format("SQLi attack detected in %s with score %d", k, v.score),
                    severity = 'high',
                    confidence = 'medium',
                    sources = haka.alert.address(conn.srcip),
                    targets = {
                        haka.alert.address(conn.dstip),
                        haka.alert.service(string.format("tcp/%d", conn.dstport), "http") 
                    },
                }
                http:drop()
                return
            end
        end
    end
}

5.4.7. Mutliple rules

The script file introduces additional malicious patterns and use the rule_group feature to define multiple anti-sqli security rules. Each rule focus on the detection of a particular pattern (sql keywords, sql comments, etc.)

require('httpconfig')
require('httpdecode')

------------------------------------
-- Malicious patterns
------------------------------------

local sql_comments = { '%-%-', '#', '%z', '/%*.-%*/' }

-- Common pattern used in intial attack stage to ckeck for SQLi vulnerabilites
local probing = { "^[\"'`´’‘;]", "[\"'`´’‘;]$" }

local sql_keywords = {
    'select', 'insert', 'update', 'delete', 'union',
    -- You can extent this list with other sql keywords
}

local sql_functions = {
    'ascii', 'char', 'length', 'concat', 'substring',
    -- You can extend this list with other sql functions
}

------------------------------------
-- SQLi Rule Group
------------------------------------

-- Define a security rule group related to SQLi attacks
sqli = haka.rule_group{
    name = 'sqli',
    -- Initialize some values before evaluating any security rule
    init = function (self, http)
        dump_request(http)

        -- Another way to split cookie header value and query's arguments
        http.sqli = {
            cookies = {
                value = http.request:split_cookies(),
                score = 0
            },
            args = {
                value = http.request:split_uri().args,
                score = 0
            }
        }
    end,
}

local function check_sqli(patterns, score, trans)
    sqli:rule{
        hooks = { 'http-request' },
        eval = function (self, http)
            for k, v in pairs(http.sqli) do
                if v.value then
                    for _, val in pairs(v.value) do
                        for _, f in ipairs(trans) do
                            val = f(val)
                        end

                        for _, pattern in ipairs(patterns) do
                            if val:find(pattern) then
                                v.score = v.score + score
                            end
                        end
                    end

                    if v.score >= 8 then
                        local conn = http.connection
                        haka.alert{
                            description = string.format("SQLi attack detected in %s with score %d", k, v.score),
                            severity = 'high',
                            confidence = 'medium',
                            sources = haka.alert.address(conn.srcip),
                            targets = {
                                haka.alert.address(conn.dstip),
                                haka.alert.service(string.format("tcp/%d", conn.dstport), "http")
                            },
                        }

                        http:drop()
                        return
                    end
                end
            end
        end
    }
end

-- Generate a security rule for each malicious pattern class
-- (sql_keywords, sql_functions, etc.)
check_sqli(sql_comments, 4, { decode, lower })
check_sqli(probing, 2, { decode, lower })
check_sqli(sql_keywords, 4, { decode, lower, uncomments, nospaces })
check_sqli(sql_functions, 4, { decode, lower, uncomments, nospaces })

Note

Decoding functions are applied depending on the pattern. It is obvious to not apply uncomment function when we are looking for comments.

5.4.8. White list

All the defined rules are too general and will therefore raise many alerts. In the example given hereafter, we show how we could skip evaluation of rules if the uri matches some conditions (for instance, do not evaluate anti-sqli rules when the requested resource is equal to /foo/bar/safepage.php). This shows another advantage of using rules group feature.

Note

The check is done after uri normalisation

require('httpconfig')
require('httpdecode')

------------------------------------
-- Malicious patterns
------------------------------------

local sql_comments = { '%-%-', '#', '%z', '/%*.-%*/' }

local probing = { "^[\"'`´’‘;]", "[\"'`´’‘;]$" }

local sql_keywords = {
    'select', 'insert', 'update', 'delete', 'union',
    -- You can extend this list with other sql keywords
}

local sql_functions = {
    'ascii', 'char', 'length', 'concat', 'substring',
    -- You can extend this list with other sql functions
}

------------------------------------
-- White List resources
------------------------------------

local safe_resources = {
    '/foo/bar/safepage.php', '/action.php',
    -- You can extend this list with other white list resources
}

------------------------------------
-- SQLi Rule Group
------------------------------------

sqli = haka.rule_group{
    name = 'sqli',
    -- Initialisation
    init = function (self, http)
        dump_request(http)

        -- Another way to split cookie header value and query's arguments
        http.sqli = {
            cookies = {
                value = http.request:split_cookies(),
                score = 0
            },
            args = {
                value = http.request:split_uri().args,
                score = 0
            }
        }
    end,

    -- Continue will be executed after evaluation of
    -- each security rule.
    -- Here we check the return value ret to decide
    -- if we skip the evaluation of the rest of the
    -- rule.
    continue = function (self, http, ret)
        return not ret
    end
}

------------------------------------
-- SQLi White List Rule
------------------------------------

sqli:rule{
    hooks = { 'http-request' },
    eval = function (self, http)
        -- Split uri into subparts and normalize it
        local splitted_uri = http.request:split_uri():normalize()
        for _, res in ipairs(safe_resources) do
            -- Skip evaluation if the normalized path (without dot-segments)
            -- is in the list of safe resources
            if splitted_uri.path == res then
                haka.log("sqli", "skip SQLi detection (white list rule)")
                return true
            end
        end
    end
}

------------------------------------
-- SQLi Rules
------------------------------------

local function check_sqli(patterns, score, trans)
    sqli:rule {
        hooks = { 'http-request' },
        eval = function (self, http)
            for k, v in pairs(http.sqli) do
                if v.value then
                    for _, val in pairs(v.value) do
                        for _, f in ipairs(trans) do
                            val = f(val)
                        end

                        for _, pattern in ipairs(patterns) do
                            if val:find(pattern) then
                                v.score = v.score + score
                            end
                        end
                    end

                    if v.score >= 8 then
                        local conn = http.connection
                        -- Report an alert (long format)
                        haka.alert{
                            description = string.format("SQLi attack detected in %s with score %d", k, v.score),
                            severity = 'high',
                            confidence = 'high',
                            method = {
                                description = "SQL Injection Attack",
                                ref = "cwe-89"
                            },
                            sources = haka.alert.address(conn.srcip),
                            targets = {
                                haka.alert.address(conn.dstip),
                                haka.alert.service(string.format("tcp/%d", conn.dstport), "http")
                            },
                        }
                        http:drop()
                        return
                    end
                end
            end
        end
    }
end

check_sqli(sql_comments, 4, { decode, lower })
check_sqli(probing, 2, { decode, lower })
check_sqli(sql_keywords, 4, { decode, lower, uncomments, nospaces })
check_sqli(sql_functions, 4, { decode, lower, uncomments, nospaces })

5.4.9. Going further

As mentioned in the top of this tutorial, our aim is not to block all SQLi attacks. To improve detection rate, one could extend the malicious patterns given throughout these examples and make use of a powerful regular expression engine.