GitLab

Summary

SQL DLZ query are implemented diffently between BIND 9.9.7 and BIND 9.11.5 and above : When we submit a IN A query for domain.com (@) BIND 9.9.7 DLZ do 2 queries, one for "@" and another one for "*" BIND 9.11.5 and above do only one query for "@"

BIND version used

BIND 9.9.7 and BIND 9.11.5

Steps to reproduce

Compilation options : ./configure --with-dlz-mysql

SQL Data base store zone like this

+----------------------+----------------+------+
| category             | zone           | host |
+----------------------+----------------+------+
| mycategory           | domain.com     | *    |
+----------------------+----------------+------+

Queries :

dig -t a domain.com @127.0.0.1
dig -t a www.domain.com @127.0.0.1

What is the current bug behavior?

SQL Trace given by above queries with BIND 9.11.5 and BIND 9.12.3:

181204  9:16:12   113 Query     SELECT zone FROM blacklist WHERE zone = 'domain.com' AND (category = 'None' OR category = 'mycategory')
                  113 Query     SELECT NULL as ttl, 'CNAME' as type, NULL as mx_priority, 'domain.com.' as data FROM blacklist WHERE zone = 'domain.com' AND host = '@'
                  113 Query     SELECT 86400 as ttl, 'SOA' as type, 'localhost.' as data, 'root.localhost.' as resp_person, 1 as serial, 3600 as refresh, 200 as retry, 3600000 as expire, 3600 as minimum FROM `blacklist` WHERE zone = 'domain.com' UNION SELECT NULL as ttl, 'NS' as type, 'localhost.' as data, NULL as resp_person, NULL as serial, NULL as refresh, NULL as retry, NULL as expire, NULL as minimum FROM `blacklist` WHERE zone = 'domain.com'
                  113 Query     SELECT NULL as ttl, 'CNAME' as type, NULL as mx_priority, 'domain.com.' as data FROM blacklist WHERE zone = 'domain.com' AND host = '@'
                  113 Query     SELECT 86400 as ttl, 'SOA' as type, 'localhost.' as data, 'root.localhost.' as resp_person, 1 as serial, 3600 as refresh, 200 as retry, 3600000 as expire, 3600 as minimum FROM `blacklist` WHERE zone = 'domain.com' UNION SELECT NULL as ttl, 'NS' as type, 'localhost.' as data, NULL as resp_person, NULL as serial, NULL as refresh, NULL as retry, NULL as expire, NULL as minimum FROM `blacklist` WHERE zone = 'domain.com'

181204  9:16:24   113 Query     SELECT zone FROM blacklist WHERE zone = 'www.domain.com' AND (category = 'None' OR category = 'mycategory')
                  113 Query     SELECT zone FROM blacklist WHERE zone = 'domain.com' AND (category = 'None' OR category = 'mycategory')
                  113 Query     SELECT NULL as ttl, 'CNAME' as type, NULL as mx_priority, 'domain.com.' as data FROM blacklist WHERE zone = 'domain.com' AND host = '@'
                  113 Query     SELECT 86400 as ttl, 'SOA' as type, 'localhost.' as data, 'root.localhost.' as resp_person, 1 as serial, 3600 as refresh, 200 as retry, 3600000 as expire, 3600 as minimum FROM `blacklist` WHERE zone = 'domain.com' UNION SELECT NULL as ttl, 'NS' as type, 'localhost.' as data, NULL as resp_person, NULL as serial, NULL as refresh, NULL as retry, NULL as expire, NULL as minimum FROM `blacklist` WHERE zone = 'domain.com'
                  113 Query     SELECT NULL as ttl, 'CNAME' as type, NULL as mx_priority, 'domain.com.' as data FROM blacklist WHERE zone = 'domain.com' AND host = 'www'
                  113 Query     SELECT NULL as ttl, 'CNAME' as type, NULL as mx_priority, 'domain.com.' as data FROM blacklist WHERE zone = 'domain.com' AND host = '*'

What is the expected correct behavior?

SQL Trace given by above queries with BIND 9.9.7 :
181204  9:25:21    39 Connect   user@localhost on my_db
                   39 Query     SET NAMES latin1
                   39 Query     SELECT zone FROM blacklist WHERE zone = 'domain.com' AND (category = 'None' OR category = 'mycategory')
                   39 Query     SELECT NULL as ttl, 'CNAME' as type, NULL as mx_priority, 'domain.com.' as data FROM blacklist WHERE zone = 'domain.com' AND host = '@'
                   39 Query     SELECT NULL as ttl, 'CNAME' as type, NULL as mx_priority, 'domain.com.' as data FROM blacklist WHERE zone = 'domain.com' AND host = '*' <<<-- !!! This query is missing in new BIND version !!!                   
                   39 Query     SELECT 86400 as ttl, 'SOA' as type, 'localhost.' as data, 'root.localhost.' as resp_person, 1 as serial, 3600 as refresh, 200 as retry, 3600000 as expire, 3600 as minimum FROM `blacklist` WHERE zone = 'domain.com' UNION SELECT NULL as ttl, 'NS' as type, 'localhost.' as data, NULL as resp_person, NULL as serial, NULL as refresh, NULL as retry, NULL as expire, NULL as minimum FROM `blacklist` WHERE zone = 'domain.com'

181204  9:25:27    39 Query     SELECT zone FROM blacklist WHERE zone = 'www.domain.com' AND (category = 'None' OR category = 'mycategory')
                   39 Query     SELECT zone FROM blacklist WHERE zone = 'domain.com' AND (category = 'None' OR category = 'mycategory')
                   39 Query     SELECT NULL as ttl, 'CNAME' as type, NULL as mx_priority, 'domain.com.' as data FROM blacklist WHERE zone = 'domain.com' AND host = '@'
                   39 Query     SELECT NULL as ttl, 'CNAME' as type, NULL as mx_priority, 'domain.com.' as data FROM blacklist WHERE zone = 'domain.com' AND host = '*'
                   39 Query     SELECT 86400 as ttl, 'SOA' as type, 'localhost.' as data, 'root.localhost.' as resp_person, 1 as serial, 3600 as refresh, 200 as retry, 3600000 as expire, 3600 as minimum FROM `blacklist` WHERE zone = 'domain.com' UNION SELECT NULL as ttl, 'NS' as type, 'localhost.' as data, NULL as resp_person, NULL as serial, NULL as refresh, NULL as retry, NULL as expire, NULL as minimum FROM `blacklist` WHERE zone = 'domain.com'
                   39 Query     SELECT NULL as ttl, 'CNAME' as type, NULL as mx_priority, 'domain.com.' as data FROM blacklist WHERE zone = 'domain.com' AND host = 'www'
                   39 Query     SELECT NULL as ttl, 'CNAME' as type, NULL as mx_priority, 'domain.com.' as data FROM blacklist WHERE zone = 'domain.com' AND host = '*'

Relevant configuration files

DLZ Config in a view :

view my_view {
    include "/opt/myapp/bind/etc/named.conf.default-zones";

    match-clients { my_acl; };

    allow-recursion { any; };

        zone "rootzone"{type master;file "domain.com.zone";};

        dlz "blacklist zones" {
                database "mysql
                        {host=localhost dbname=my_db user=user pass=*******}
                        {SELECT zone FROM blacklist WHERE zone = '$zone$' AND (category = 'None' OR category = 'mycategory')}
                        {SELECT NULL as ttl, 'CNAME' as type, NULL as mx_priority, 'domain.com.' as data FROM blacklist WHERE zone = '$zone$' AND host = '$record$'}
                        {SELECT 86400 as ttl, 'SOA' as type, 'localhost.' as data, 'root.localhost.' as resp_person, 1 as serial, 3600 as refresh, 200 as retry, 3600000 as expire, 3600 as minimum FROM `blacklist` WHERE zone = '$zone$' UNION SELECT NULL as ttl, 'NS' as type, 'localhost.' as data, NULL as resp_person, NULL as serial, NULL as refresh, NULL as retry, NULL as expire, NULL as minimum FROM `blacklist` WHERE zone = '$zone$'}
                ";
        };
};

Relevant logs and/or screenshots

See SQL trace above.