How secure is my PHP registration script?
You properly protect against SQL injection, that's good.
I worry about the filtering on the password. If I choose &lt;script&gt;' OR 1=1 &quot;&gt;&gt;&gt; as my password, the filtering should not reduce this to just ' OR 1=1 &quot;, just because some part of it looks like HTML tags. A password is not supposed to be displayed anywhere, it is not supposed to be stored in plain text anywhere, the only valid use is to feed it to password_validate and password_hash, and these can cope with arbitrary input (well, except that they stop processing at the first byte that is 0, that is, the byte value 0, not the digit '0'). When I enter a password, I expect this password to be used exactly, unmodified, unnormalized, just as I entered it.
I dimly remember that uniqid is not very unique at all. You should double-check the documentation whether that function provides enough randomness to protect against guessing attacks.
Your code is sending out a mail containing &lt;a href=&quot;...&quot;&gt;, but nowhere in the code do I see a Content-type: text/html; charset=UTF-8 header.
Your code is sending out malformed HTML. The URL contains a plain &amp;, which in HTML must be escaped as &amp;amp;. To do this, call htmlspecialchars($url). Most browsers accept this malformed HTML, but it's better do know how to correctly convert between plain text and HTML. Just to avoid cross-site scripting from the beginning.
What if the $fname contains an ampersand? Some companies have that in their name. I don't know of actual people with these names, but who knows? An ampersand would make the generated $activation_link invalid.