This is a posting I've been planning to do for several months but am just finding time for. One of the most common questions I get from developers is how they should be handling authentication. My advice is always the same - don't. I have no idea why developers want to be custodians of data as sensitive as a username and password when there are federated identity providers such as OpenID that will accept that risk on the developer's behalf.
Some developers insist on localized identity management, so the next couple of posts are going to illustrate how they can implement web forms securely using MySQL stored procedures and UUIDs to insulate them from the risks storing credentials.
Before getting to code-level specifics, let's define the problem we're solving.
First, we want to make sure that a single user's credentials cannot be discovered via SQL injection or other application-level security flaws. Most developers mitigate this by only storing one-way hashes of passwords.
Second, because hashes are susceptible to rainbow tables attacks, we want to make sure that an attacker cannot obtain any aggregated credential hashes. Keep in mind that many users utilize the same (or similar) passwords across many system. This means that aggregated credential hashed are extremely valuable to an attacker. To mitigate this, we will check authentication within a stored procedure. If the password hash is checked via a stored procedure and the application user only has execute permissions, an attacker cannot extract aggregated credentials even if they completely control an application.
Finally, we want to make sure that an attacker cannot easily mine your database for aggregated private or sensitive data through application-level flaws. By using a UUID as the primary key instead of a username or canonical index, we make it very difficult for an attacker to extract data for a single arbitrary user; this also makes data aggregation more difficult.
That's probably as clear as mud, so I'll explain via code examples. The principals all apply no matter what platform you're on, but the examples are all using MySQL 5.1.x.