Does click fraud protection actually work?

It works in a narrow way. Against repeat sources that keep hitting the same campaigns, exclusions genuinely stop the ad being served and no further click is billed. Against a single motivated person rotating addresses, or on campaign types that do not support exclusions, it does not. Whether that narrow win is worth the subscription depends entirely on your cost per click.