As was already suggested by others, popular way for HTTPS is some kind of MITM. But it breaks end-to-end security, which will be increasingly more problematic. And it’s only usable for devices under your control, because nobody else will trust your fake CA. Plain HTTP can be filtered using transparent proxy, but that’s going away, being replaced by HTTPS. Other protocols, hard to say, either you’ll have same problem with encryption, or they may not contain hostnames at all, unlike HTTP(S).
It’s not just RouterOS. DNS resolution and following connection are two independent things. You start with hostname, ask DNS server for IP address, and then connect to that IP adddress. But when you do that, the connection itself doesn’t contain any reference to original hostname. Some protocol may have it (Host header in HTTP(S), SNI in TLS, …), but it’s on different level, which IP firewall doesn’t see (not counting ugly hacks like L7 filters).
Pi-hole no, it’s just DNS. Privoxy maybe, I don’t know what exact features it has, but if there’s some filtering, it should be good enough for HTTP (without S, so not good enough).
IMHO the problem is not when you want to lock down your own network (devices managed by you), disable everything by default and allow only selected stuff. In that case, address-list based method is almost there. Make sure that devices use router as DNS resolver (use dstnat to redirect all connections to port 53 to router, in case something tries to access different DNS server). Don’t enable (or disable) DoH, DoT or any other ways how to resolve hostnames elsewhere (if you fail here, it’s no big deal for security, because if it’s not in router’s whitelist, connection to it won’t be allowed).
This solution won’t work for dynamic stuff, e.g. if something would use addresses like <something_random>.somecdn.tld, because there’s no way how router can resolve in advance all possible variations of <something_random>. That could be done with proxy. The idea I like is simple SOCKS proxy. If it’s for own devices, you can configure them to use it, many programs support it. Disable requests for connections to numeric IP addresses, allow only hostnames (so client device won’t be doing any DNS resolution at all). Set up ACL to allow access to selected hostnames. Since proxy will see them, it can easily allow even <something_random>.somecdn.tld as any subdomain of somecdn.tld. I can’t recommend any ready to use solution, I assume something exists that can be configured like this. I’m hoping for SOCKS proxy in RouterOS, after they recently added SOCKS5 and someone from MikroTik wrote that they plan to improve it further. And this would not be difficult. But currently it’s not there yet.
Same approach with proxy can be used for blocking something. Again, for your own devices, because you can configure them to use it. If it’s some company network, devices like workstations probably don’t need direct internet access, proxy is enough. And it can be useful not only for filtering, but also for accounting.
The main problem is when you need to do this for devices that are not yours. You can’t force them to use proxy. You can’t force them to use your DNS. If the goal is to block something, then for HTTPS (which is most popular protocol), you can look at SNI (so far, but in future it will be encrypted too). If it contains bad hostname, block it. But the reverse, to allow only something, would be difficult (the part where you need to block everything else), at least in current RouterOS.
But then I found THE SOLUTION TO PRIVACY AND SECURITY!