[MXS-1280] dbfwfilter: Query could not be tokenized and will hence be rejected Created: 2017-06-07  Updated: 2020-01-20  Resolved: 2020-01-20

Status: Closed
Project: MariaDB MaxScale
Component/s: dbfwfilter
Affects Version/s: 2.0.4
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: ssauravy Assignee: Johan Wikman
Resolution: Won't Fix Votes: 0
Labels: None
Environment:

OS : CentOS 6.8
MySQL 5.6.33


 Description   

I set dbfwfilter in Maxscale as follows.

  1. # vi /etc/maxscale.cnf
    ~
    [DBFW_goods]
    type=filter
    module=dbfwfilter
    action=block
    rules=/var/log/maxscale/rules.xxxxx.txt

~

[MasterRoute]
type=service
router=readconnroute
#router_options=slave_selection_criteria=LEAST_CURRENT_OPERATIONS
servers=writeserver
user=maxscale
passwd=*****************
filters=DBFW_xxxxx
~

[root@session1 maxscale]# cat rules.goods.txt
rule safe_drop deny regex '.*drop.*table.*xxxxx'
users %@% match all rules safe_drop

The following error occurs on the maxscale log.
2017-06-07 16:00:31 warning: dbfwfilter: Query could not be tokenized and will hence be rejected:
(select
a.no, a.subject, a.contents, a.image_type,
(select ifnull(replace(img, '_org.', '_270.'), '') from curating_image where c_no = a.no and rep_yn = 'Y' limit 0, 1) as img
from curating a
where a.use_yn = 'Y' and ifnull(if(a.start_date = '','0000000000',a.start_date),'0000000000') <= date_format(now(), '%Y%m%d%H')
and ifnull(if(a.end_date='','9999999999',a.end_date),'9999999999') >= date_format(now(), '%Y%m%d%H')
and a.image_type = 'A'
order by a.notice_yn desc, a.rt desc
limit 45, 15)
union all
(select
a.no, a.subject, a.contents, a.image_type,
(select ifnull(replace(img, '_org.', '_270.'), '') from curating_image where c_no = a.no and rep_yn = 'Y' limit 0, 1) as img
from curating a
where a.use_yn = 'Y' and ifnull(if(a.start_date = '','0000000000',a.start_date),'0000000000') <= date_format(now(), '%Y%m%d%H')
and ifnull(if(a.end_date='','9999999999',a.end_date),'9999999999') >= date_format(now(), '%Y%m%d%H')
and a.image_type = 'B'

order by a.notice_yn desc, a.rt desc
limit 18, 6)

failed. Access denied for user 'user3'@'192.168.0.198' to database 'svcdb': dbfwfilter: Query could not be tokenized and will hence be rejected.<br>
1141: Access denied for user 'user3'@'192.168.0.198' to database 'svcdb': dbfwfilter: Query could not be tokenized and will hence be rejected.<br>

It seems that dbfwfilter-related errors are occurring, and the query does not execute.



 Comments   
Comment by markus makela [ 2017-06-07 ]

Can you try and see if this is fixed in 2.1.3?

Comment by ssauravy [ 2017-06-07 ]

Maxscale The result of doing the following in 2.1.3.

CREATE TABLE `t3` (
`id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COLLATE=utf8_bin ;

CREATE TABLE `t4` (
`id` int(11) DEFAULT NULL,
`nm` varchar(10) CHARACTER SET utf8mb4 DEFAULT NULL,
KEY `nm` (`nm`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin ;

Case1)

(SELECT id
,IFNULL((SELECT IFNULL(nm,'sss') FROM t4 WHERE t4.id=t3.id LIMIT 0,1),'xxx') AS img
FROM t3 LIMIT 0,2)
UNION ALL
(SELECT id
,(SELECT IFNULL(nm,'sss') FROM t4 b WHERE b.id=t4.id LIMIT 0,1) AS img
FROM t4
WHERE id>0 LIMIT 0,4)

오류 코드: 1141
Access denied for user 'ssauravy'@'::ffff:192.168.0.11': Query could not be tokenized and will hence be rejected. Please ensure that the SQL syntax is correct.

Case2)

SELECT s.*
FROM (
(SELECT id
,IFNULL((SELECT IFNULL(nm,'sss') FROM t4 WHERE t4.id=t3.id LIMIT 0,1),'xxx') AS img
FROM t3 LIMIT 0,2)
UNION ALL
(SELECT id
,(SELECT IFNULL(nm,'sss') FROM t4 b WHERE b.id=t4.id LIMIT 0,1) AS img
FROM t4
WHERE id>0 LIMIT 0,4)
) s

1 queries executed, 1 success, 0 errors, 0 warnings
Success!!

Comment by Johan Wikman [ 2017-06-08 ]

The query is too complex for the sqlite based parser that we use. And currently, the firewall filter works so that if a statement cannot be completely parsed, it is rejected.

Do you use the firewall filter to protect the database against malicious attacks or as a safeguard against stupid mistakes?

If the latter, then it would make sense to provide a "relaxed" mode that would cause the firewall filter not to reject all statements that cannot be parsed.

Comment by ssauravy [ 2017-06-08 ]

Thank you for answer.
Once I found a way to bypass, I do not hurry.
So, are you planning to change the parser in the future?

Comment by Johan Wikman [ 2017-06-08 ]

The aim is that the parser in MaxScale should be capable of parsing everything that the server accepts. But exactly in what timeframe the parser will be improved I can't say at the moment.

Comment by Johan Wikman [ 2017-06-09 ]

Just noticed that you are using a regex rule. In that case the database firewall filter actually shouldn't do any parsing, since the parse result is not used at all.

That can be considered to be a bug and I've created a Jira item to that effect: https://jira.mariadb.org/browse/MXS-1284

Comment by Johan Wikman [ 2020-01-20 ]

MaxScale 2.0. 2.1 and 2.2 are no longer supported. Please reopen or recreate if this is still an issue in 2.3 and 2.4.

Generated at Thu Feb 08 04:05:33 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.