mysql prelude-lml rules

joël Winteregg <[email protected]> Fri, 15 Sep 2006 15:34:36 +0200

Newsgroups	gmane.comp.security.ids.prelude.devel
Message-ID	<1158327277.4714.56.camel@hatman>

Hi,


Here is the rulset i wrote to grab strange mysql queries from its "clear
text" logging capability, configured as follow in /etc/mysql/my.cnf:

log = /var/log/mysql.log


The following format configuration need to be done to be able to manage
this kind of logs which doesn't always follow the same template (when
multiple queries are done in a single one separated by ';'):

[format=mysql]
time-format = "%y%m%d %H:%M:%S"
prefix-regex = "^(?:(?P<timestamp>[\d: ]{15})|)"
file = /var/log/mysql.log

Here is the related plugins.rules configuration:
# source                     plugin-name-list      pcre-options
regex
#
# *                          Debug
-                    .*
 /var/log/mysql.log          Pcre[mysql]
-                    .*



Rules are done as a blacklist which try to focus on "strange SQL
queries" which are often use for SQL injection purpose ;-)

I will be happy to have a feedback about it (to know if i forgot some
important "strange SQL queries"). Rules which fetch all log lines are by
default commented (to avoid flooding).


Best regards,

Joël

_______________________________________________
Prelude-devel site list
[email protected]
http://www.prelude-ids.org/mailman/listinfo/prelude-devel

mysql.rules (text/plain, 8.3 KB)

#####
#
# Copyright (C) 2006 Joel Winteregg <joel.winteregg at heig-vd dot ch>
# IICT (Institute for Information and Communication Technologies)
# http://www.iict.ch/  || http://www.fullsecurity.ch/
# All Rights Reserved
#
# This file is part of the Prelude-LML program.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 2, or (at your option)
# any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; see the file COPYING.  If not, write to
# the Free Software Foundation, 675 Mass Ave, Cambridge, MA 02139, USA.
#
#####

#####
#
# The rules included here were developed using Mysql 5.0.24a-3 (Debian etch package, mysql-server_5.0.24a-3_all.deb)
# Please report any inconsistencies on other versions to Joel Winteregg at the address provided above.
#
# By default, only blacklist rules are activated.
# 
# COMMENTS:
# assessment.impact.completion couldn't be set because logs doesn't contain any information about request execution status
#
# target(0).node.address(0).address should be hardcoded because logs doesn't contain any info about the host name or IP.
# It couldn't be auto completed using the (?<hostname>) prefix-regex variable. Please repace all <mysqld_IP> tag with your
# real Mysql server IP.
#
# Mysql inside requests which are not fetched by this ruleset:
#    - Init DB    (Example: "7 Init DB     test")
#    - Field List (Example: "7 Field List  table1")
#
#
#####


#####
#
# Blacklist rules:
# Those rules are detecting SQL queries which could bring the user or attacker
# to add, delete or modify database information using injection methods.
#
#####

# 
# NULL auth injection attack using strings: OR 'x'='x'
#
# LOG:060912  9:40:26       1 Query       select * from table1 where nom='joel' OR '1'='1'
#
regex=(\d+) Query.*OR +'(.+)' *= *'\2'; \
 classification.text=Mysql server: OR '$2'='$2' attempted; \
 id=4600; \
 revision=1; \
 analyzer(0).name=Mysql; \
 analyzer(0).manufacturer=www.mysql.org; \
 analyzer(0).class=Service; \
 assessment.impact.severity=high; \
 assessment.impact.type=other; \
 assessment.impact.description=SQL request: $2 / Session ID: $1; \
 target(0).node.address(0).category=ipv4-addr; \
 target(0).node.address(0).address=<mysqld_IP>; \
 target(0).service.name=mysql; \
 additional_data(0).type=integer; \
 additional_data(0).meaning=Session ID; \
 additional_data(0).data=$1; \


# 
# NULL auth injection attack using numbers: OR 2=2
#
# LOG:060912  9:40:26       1 Query       select * from table1 where nom='joel' OR 1=1
#
regex=(\d+) Query.*OR +(\d) *= *\2; \
 classification.text=Mysql server: OR $2=$2 attempted; \
 id=4601; \
 revision=1; \
 analyzer(0).name=Mysql; \
 analyzer(0).manufacturer=www.mysql.org; \
 analyzer(0).class=Service; \
 assessment.impact.severity=high; \
 assessment.impact.type=other; \
 assessment.impact.description=SQL request: $2 / Session ID: $1; \
 target(0).node.address(0).category=ipv4-addr; \
 target(0).node.address(0).address=<mysqld_IP>; \
 target(0).service.name=mysql; \
 additional_data(0).type=integer; \
 additional_data(0).meaning=Session ID; \
 additional_data(0).data=$1; \


# 
# Query injection attack using a UNION.
# This rule could raise a lot of false positive if your application require UNION queries.
#
# LOG:060912  9:40:26       1 Query       SELECT nom, pass FROM table1 UNION SELECT 1,1 LIMIT 2,1
#
regex=(\d+) Query(.*(?i)union.*); \
 classification.text=Mysql server: Union query attempted; \
 id=4602; \
 revision=1; \
 analyzer(0).name=Mysql; \
 analyzer(0).manufacturer=www.mysql.org; \
 analyzer(0).class=Service; \
 assessment.impact.severity=medium; \
 assessment.impact.type=other; \
 assessment.impact.description=SQL request: $2 / Session ID: $1; \
 target(0).node.address(0).category=ipv4-addr; \
 target(0).node.address(0).address=<mysqld_IP>; \
 target(0).service.name=mysql; \
 additional_data(0).type=integer; \
 additional_data(0).meaning=Session ID; \
 additional_data(0).data=$1; \


# 
# Dump file attack
# This rule could raise a lot of false positive if your application require 'INTO OUTILE' or 'INTO DUMPFILE' queries.
#
#LOG:060912  9:40:26       1 Query       SELECT nom FROM table1 INTO OUTFILE '/home/http/site/result.txt'
#LOG:060912  9:40:26       1 Query       SELECT nom FROM table1 INTO DUMPFILE '/home/http/site/result.txt'
#
regex=(\d+) Query.*(?i)into +(?:dump|out)file +'(.*)'.*; \
 classification.text=Mysql server: a query wrote its result to a file; \
 id=4603; \
 revision=1; \
 analyzer(0).name=Mysql; \
 analyzer(0).manufacturer=www.mysql.org; \
 analyzer(0).class=Service; \
 assessment.impact.severity=medium; \
 assessment.impact.type=other; \
 assessment.impact.description=An SQL query attempted to write its results to '$2' file with the session ID $1; \
 target(0).node.address(0).category=ipv4-addr; \
 target(0).node.address(0).address=<mysqld_IP>; \
 target(0).service.name=mysql; \
 target(0).file(0).name=$2; \
 target(0).file(0).path=$2; \
 target(0).file(0).category=current; \
 additional_data(0).type=integer; \
 additional_data(0).meaning=Session ID; \
 additional_data(0).data=$1; \
 last


#####
#
# 'Normal' rules: To get each SQL query, connection log, etc...
# 
#
#####

##LOG:060912  9:40:26       1 Query       SELECT pass FROM table1
#regex=(\d+) Query +(.*); \
# classification.text=Mysql server: Query; \
# id=4604; \
# revision=1; \
# analyzer(0).name=Mysql; \
# analyzer(0).manufacturer=www.mysql.org; \
# analyzer(0).class=Service; \
# assessment.impact.severity=low; \
# assessment.impact.type=other; \
# assessment.impact.description=SQL request: $2 / Session ID: $1; \
# target(0).node.address(0).category=ipv4-addr; \
# target(0).node.address(0).address=<mysqld_IP>; \
# target(0).service.name=mysql; \
# additional_data(0).type=integer; \
# additional_data(0).meaning=Session ID; \
# additional_data(0).data=$1; \
# last

#LOG:060914 16:58:33       7 Connect     root@localhost on
#regex=(\d+) Connect +([^ ]+)@([^ ]+) on ?(.*); \
# classification.text=Mysql server: Client connection; \
# id=4605; \
# revision=1; \
# analyzer(0).name=Mysql; \
# analyzer(0).manufacturer=www.mysql.org; \
# analyzer(0).class=Service; \
# assessment.impact.severity=low; \
# assessment.impact.type=other; \
# assessment.impact.description=User $2 is connected to $3 (DB '$4') with the session ID $1; \
# target(0).node.address(0).category=ipv4-addr; \
# target(0).node.address(0).address=<mysqld_IP>; \
# target(0).service.name=mysql; \
# additional_data(0).type=integer; \
# additional_data(0).meaning=Session ID; \
# additional_data(0).data=$1; \
# additional_data(1).type=string; \
# additional_data(1).meaning=Database name; \
# additional_data(1).data=$4; \
# last

##LOG:060914 17:23:47       8 Connect     Access denied for user 'root'@'localhost' (using password: YES)
#regex=(\d+) Connect +Access denied for user '([^ ]+)'@'([^ ]+)' (.*); \
# classification.text=Mysql server: Client connection failed; \
# id=4606; \
# revision=1; \
# analyzer(0).name=Mysql; \
# analyzer(0).manufacturer=www.mysql.org; \
# analyzer(0).class=Service; \
# assessment.impact.severity=medium; \
# assessment.impact.type=other; \
# assessment.impact.description=User $2 tryed to connect to host $3 with the session ID $1; \
# target(0).node.address(0).category=ipv4-addr; \
# target(0).node.address(0).address=<mysqld_IP>; \
# target(0).service.name=mysql; \
# additional_data(0).type=integer; \
# additional_data(0).meaning=Session ID; \
# additional_data(0).data=$1; \
# last


##LOG:060914 16:59:43       7 Quit
#regex=(\d+) Quit; \
# classification.text=Mysql server: closing client connection; \
# id=4607; \
# revision=1; \
# analyzer(0).name=Mysql; \
# analyzer(0).manufacturer=www.mysql.org; \
# analyzer(0).class=Service; \
# assessment.impact.severity=medium; \
# assessment.impact.type=other; \
# assessment.impact.description=Session ID $1 has been closed; \
# target(0).node.address(0).category=ipv4-addr; \
# target(0).node.address(0).address=<mysqld_IP>; \
# target(0).service.name=mysql; \
# additional_data(0).type=integer; \
# additional_data(0).meaning=Session ID; \
# additional_data(0).data=$1; \
# last