search

darold / pgFormatter

A PostgreSQL SQL syntax beautifier that can work as a console program or as a CGI. On-line demo site at http://sqlformat.darold.net/
PostgreSQL License
1.69k stars 101 forks source link

Parenthesis error introduced on formatting #346

Open beperpg opened 1 month ago

beperpg commented 1 month ago

As per $title. I understand that during formatting some not needed parenthesis are cleaned up, but it broke this query for me. (It worked without a hiccup for hundreds of other queries, but I cannot figure out why it broke this one.)

The command used was: find ./ -name "*.sql" -print0 | xargs -0 -I pg_format {} -u 0 -B -o {}

Screenshot attached where the parenthesis was missing pairs (marked red): parenthesis_error

All suggestions are welcome. Thank you.

`-- original

select * FROM xxx WHERE ( timestamp_load >= '2024-09-19 00:00:00' AND timestamp_load < '2024-09-19 00:01:00' ) AND (event_action == 'accept') AND NOT ((source_ip != '' AND (toIPv4OrNull(source_ip) BETWEEN '192.168.125.0' AND '192.168.125.255')) OR destination_ip IN ('1.1.1.1') OR process_name IN ('PING')) AND ((((threat_threat_matches_destination_ip IS NOT NULL AND threat_threat_matches_destination_ip != '')) AND NOT ((destination_ip != '' AND (destination_ip IN ('127.0.0.1') OR toIPv4OrNull(destination_ip) BETWEEN '10.0.0.0' AND '10.255.255.255' OR toIPv4OrNull(destination_ip) BETWEEN '192.168.0.0' AND '192.168.255.255' OR toIPv4OrNull(destination_ip) BETWEEN '172.16.0.0' AND '172.31.255.255' OR toIPv6OrNull(destination_ip) BETWEEN 'fe80::' AND 'febf:ffff:ffff:ffff:ffff:ffff:ffff:ffff' OR toIPv6OrNull(destination_ip) BETWEEN '::1' AND '::1')))) OR (((threat_threat_matches_source_ip IS NOT NULL AND threat_threat_matches_source_ip != '')) AND NOT ((source_ip != '' AND (source_ip IN ('127.0.0.1') OR toIPv4OrNull(source_ip) BETWEEN '10.0.0.0' AND '10.255.255.255' OR toIPv4OrNull(source_ip) BETWEEN '192.168.0.0' AND '192.168.255.255' OR toIPv4OrNull(source_ip) BETWEEN '172.16.0.0' AND '172.31.255.255' OR toIPv6OrNull(source_ip) BETWEEN 'fe80::' AND 'febf:ffff:ffff:ffff:ffff:ffff:ffff:ffff' OR toIPv6OrNull(source_ip) BETWEEN '::1' AND '::1')))) OR (((threat_threat_matches_server_ip IS NOT NULL AND threat_threat_matches_server_ip != '' )) AND NOT ((server_ip != '' AND (server_ip IN ('127.0.0.1') OR toIPv4OrNull(server_ip) BETWEEN '10.0.0.0' AND '10.255.255.255' OR toIPv4OrNull(server_ip) BETWEEN '192.168.0.0' AND '192.168.255.255' OR toIPv4OrNull(server_ip) BETWEEN '172.16.0.0' AND '172.31.255.255' OR toIPv6OrNull(server_ip) BETWEEN 'fe80::' AND 'febf:ffff:ffff:ffff:ffff:ffff:ffff:ffff' OR toIPv6OrNull(server_ip) BETWEEN '::1' AND '::1')))) OR (((threat_threat_matches_client_ip IS NOT NULL AND threat_threat_matches_client_ip != '')) AND NOT ((client_ip != '' AND (client_ip IN ('127.0.0.1') OR toIPv4OrNull(client_ip) BETWEEN '10.0.0.0' AND '10.255.255.255' OR toIPv4OrNull(client_ip) BETWEEN '192.168.0.0' AND '192.168.255.255' OR toIPv4OrNull(client_ip) BETWEEN '172.16.0.0' AND '172.31.255.255' OR toIPv6OrNull(client_ip) BETWEEN 'fe80::' AND 'febf:ffff:ffff:ffff:ffff:ffff:ffff:ffff' OR toIPv6OrNull(client_ip) BETWEEN '::1' AND '::1')))) OR (((threat_threat_matches_p IS NOT NULL AND threat_threat_matches_p != '')) AND NOT ((threat_threat_matches_destination_ip IS NOT NULL AND threat_threat_matches_destination_ip != '') OR (threat_threat_matches_destination_ip_nat IS NOT NULL AND threat_threat_matches_destination_ip_nat != '') OR (threat_threat_matches_source_ip IS NOT NULL AND threat_threat_matches_source_ip != '') OR (threat_threat_matches_server_ip IS NOT NULL AND threat_threat_matches_server_ip != '') OR (threat_threat_matches_client_ip IS NOT NULL AND threat_threat_matches_client_ip != ''))))

-- formatted, parenthesis error introduced in line with word PING

select * FROM xxx WHERE (timestamp_load >= '2024-09-19 00:00:00' AND timestamp_load < '2024-09-19 00:01:00') AND (event_action == 'accept') AND NOT (source_ip != '' AND (toIPv4OrNull(source_ip) BETWEEN '192.168.125.0' AND '192.168.125.255')) OR destination_ip IN ('1.1.1.1') OR process_name IN ('PING')) AND ((((threat_threat_matches_destination_ip IS NOT NULL AND threat_threat_matches_destination_ip != '') AND NOT (destination_ip != '' AND (destination_ip IN ('127.0.0.1') OR toIPv4OrNull(destination_ip) BETWEEN '10.0.0.0' AND '10.255.255.255' OR toIPv4OrNull(destination_ip) BETWEEN '192.168.0.0' AND '192.168.255.255' OR toIPv4OrNull(destination_ip) BETWEEN '172.16.0.0' AND '172.31.255.255' OR toIPv6OrNull(destination_ip) BETWEEN 'fe80::' AND 'febf:ffff:ffff:ffff:ffff:ffff:ffff:ffff' OR toIPv6OrNull(destination_ip) BETWEEN '::1' AND '::1')))) OR (((threat_threat_matches_source_ip IS NOT NULL AND threat_threat_matches_source_ip != '') AND NOT (source_ip != '' AND (source_ip IN ('127.0.0.1') OR toIPv4OrNull(source_ip) BETWEEN '10.0.0.0' AND '10.255.255.255' OR toIPv4OrNull(source_ip) BETWEEN '192.168.0.0' AND '192.168.255.255' OR toIPv4OrNull(source_ip) BETWEEN '172.16.0.0' AND '172.31.255.255' OR toIPv6OrNull(source_ip) BETWEEN 'fe80::' AND 'febf:ffff:ffff:ffff:ffff:ffff:ffff:ffff' OR toIPv6OrNull(source_ip) BETWEEN '::1' AND '::1')))) OR (((threat_threat_matches_server_ip IS NOT NULL AND threat_threat_matches_server_ip != '') AND NOT (server_ip != '' AND (server_ip IN ('127.0.0.1') OR toIPv4OrNull(server_ip) BETWEEN '10.0.0.0' AND '10.255.255.255' OR toIPv4OrNull(server_ip) BETWEEN '192.168.0.0' AND '192.168.255.255' OR toIPv4OrNull(server_ip) BETWEEN '172.16.0.0' AND '172.31.255.255' OR toIPv6OrNull(server_ip) BETWEEN 'fe80::' AND 'febf:ffff:ffff:ffff:ffff:ffff:ffff:ffff' OR toIPv6OrNull(server_ip) BETWEEN '::1' AND '::1')))) OR (((threat_threat_matches_client_ip IS NOT NULL AND threat_threat_matches_client_ip != '') AND NOT (client_ip != '' AND (client_ip IN ('127.0.0.1') OR toIPv4OrNull(client_ip) BETWEEN '10.0.0.0' AND '10.255.255.255' OR toIPv4OrNull(client_ip) BETWEEN '192.168.0.0' AND '192.168.255.255' OR toIPv4OrNull(client_ip) BETWEEN '172.16.0.0' AND '172.31.255.255' OR toIPv6OrNull(client_ip) BETWEEN 'fe80::' AND 'febf:ffff:ffff:ffff:ffff:ffff:ffff:ffff' OR toIPv6OrNull(client_ip) BETWEEN '::1' AND '::1')))) OR (((threat_threat_matches_p IS NOT NULL AND threat_threat_matches_p != '') AND NOT ((threat_threat_matches_destination_ip IS NOT NULL AND threat_threat_matches_destination_ip != '') OR (threat_threat_matches_destination_ip_nat IS NOT NULL AND threat_threat_matches_destination_ip_nat != '') OR (threat_threat_matches_source_ip IS NOT NULL AND threat_threat_matches_source_ip != '') OR (threat_threat_matches_server_ip IS NOT NULL AND threat_threat_matches_server_ip != '') OR (threat_threat_matches_client_ip IS NOT NULL AND threat_threat_matches_client_ip != '')))) `

beperpg commented 1 month ago

update: I went back a few versions and 5.0 for example does not break the query above.