These are all great questions, hopefully I can help shed some light on the differences here (although I admit there are some similarities). Like the Desktop AntiVirus space, there are various players right? Trend, AVG, AVAST, SYMANTEC... etc... each one does the task a little differently.
Your question was around Website Security so let's focus there.
SiteLock's latest plans bundle their Website Firewall with their Malware Detection. Two fundamentally different tasks. What they don't do is include remediation in the event your website is hacked already.
CloudFlare's plans focus on network / content optimization (speeding up your website). They're best known for being a great free CDN, but they too have a Website Firewall that comes at the $20 plan as they mentioned above. They recently acquired a Malware Detection firm and just recently released a Malware Detection scanner based on that technology. They're not actively doing remediation through their main property, but they have a secondary property that probably still is.
Sucuri, the folks I represent, will offer you Malware Detection and Remediation as well as a Website Firewall. The Detection / Remediation is $89.99 per domain a year with the Website Firewall starting at $9.99 / month.
So, not sure if that clarified things so let me focus on the things I mentioned above:
Malware Detection - this is the act of identifying when your website is being used for something other than what you intended.
Malware Cleanup - if something goes horribly wrong, the attackers figures out how to get past all your hardening and security, this is the process of getting you cleared up.
Malware Prevention - this is the process in which the Website Firewall comes into play. It's designed to stop attacks, keep malware off your website and keep the hacker s out.
This is perhaps the most interesting question in your piece:
I don't claim to be a website security expert but I'm competent enough to form at least a basic line of defense: .htaccess rules, limiting login attempts, additional security plugins for CMS etc... Is it worth spending the extra money for these services?
I'm obviously bias, but the answer is most often yes. .htaccess rules, limiting login attetmps and additional security plugins are in every website we, Sucuri, clean on a daily basis. It's not to say that they don't work, but they're very limited and are specific at the local based protection. The most effective website security today is being built and found at the edge, that's something all three organizations are offering.
The real difference in protection between the three comes in the way the applications are built. CloudFlare just rebuilt their WAF to be more effective, SiteLock leases their WAF and Sucuri built their WAF based on a fundamentally different model than both existing models.
Perhaps the biggest difference you should be asking, especially if what you're working with is CMS' is which company is best known for their CMS work. That would be Sucuri, by far. We know and understand CMS', things like WordPress, Joomla, osCommerce, Magento, etc... so much so that we spend a good amount of talking about it.
As for the basic question, is it worth it? I guess the real question comes down to each individual. How much time do you want to spend yourself hardening and monitoring each website and it's environment? If you feel you absolutely must, then there is your answer. But if you find yourself needing to focus on more important aspects of your business, then there too you have your answer..
Hope this helps.