Secure web form authentication using stored procedures

 

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.

 

 

Here's a simple MySQL stored function for creating users:

DELIMITER |

 

CREATE FUNCTION addUser (

name VARCHAR(16),

pass VARCHAR(32),

fullname VARCHAR(32),

email VARCHAR(64))

RETURNS CHAR(36)

 

 

BEGIN

DECLARE returnValue CHAR(36) DEFAULT UUID();

IF ((SELECT COUNT(username) FROM users WHERE username = name

GROUP BY username) > 0)

THEN SET returnValue := NULL;

ELSE

INSERT INTO users (userid, username, passphrase, fullname, emailaddr) VALUES (returnValue, name, pass,

fullname, email);

END IF;

RETURN returnValue;

END;

 

In this case, we are inserting values (username, password, full name, and e-mail) into a table called users. We are also creating a UUID for the account as it's created. If the function is successful, it will return the UUID. If it fails, it will return NULL.

 

If we create this function with "definer" privileges, then it can be executed as different user than the web application's MySQL account, which is the invoker. This way, the invoker account can create a user without any permissions to the underlying users table. So, even if an attacker can compromise the application's MySQL username and password, they cannot extract any data from the users table.

 

Here's how we authenticate the user:

 

DELIMITER |

 

CREATE PROCEDURE authenticateUser (

IN uname VARCHAR(16),

IN passphrase VARCHAR(32)

)

 

BEGIN

SELECT u.userid FROM users AS u WHERE u.username = uname AND u.pass = passphrase;

END

 

 

Notice that all we're getting back is a UUID (or NULL if the authentication attempt fails). This UUID is the primary key for the user and necessary to extract any other data from the database. For example:

 

DELIMITER |

 

CREATE PROCEDURE getEmail (

IN userUuid CHAR(36)

)

 

BEGIN

SELECT u.e-mail FROM users AS u WHERE u.userid = userUuid;

END

 

Do you see the elegance of the solution? The attacker cannot get the e-mail address without the UUID. In order to get the UUID, the attacker needs the username and password even if they've compromised the web application's database account. While e-mail isn't particularly risky, you can image the usefulness of the technique in protecting your customer's sensitive data.

 

There is one important limitation on this technique -- it doesn't solve old fashioned SQL injection. To solve that, only access the database using parameterized queries (or, as PHP developers refer to them, prepared statements). This is the only technique that can completely eliminate SQL Injection vulnerabilities.

 

Comments

[webdevguy]

Would this not need any input filtering before use in a sql statement?

[Don Ankney]

If you are calling the stored procedure using parameterized queries, then you don't need to do any additional sanitization to prevent SQL Injection. You still will want to do appropriate sanitization to mitigate against other injection attacks such as XSS, though.

When relying on parameterized queries, make sure that you're using a database/driver combination that supports them. The PHP PDO class will emulate prepared statements (PHP's term) if the database driver doesn't support them. Emulated parameterization won't protect against SQL Injection; it's simply there for uniform data access. MySQL5 -- the usual suspect in PHP -- works just fine.

You can check other PHP drivers here: http://us2.php.net/manual/en/pdo.drivers.php