You would need PHP and a database. Most hosts will offer MySQL and one or two other options. Pretty much anything will do.
I'm assuming you already have a login/member registration system set up. From there you can create a template page (for the sake of argument let's say it's "profile.php") and then you can add a $_GET['user'] check so that a link like "http://www.yoursite.com/profile.php?user=john_doe" will take a visitor to john_doe's profile.
This is just one option, really. There are lots of ways to do this. Do you have a more specific question or are general answers good enough?