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.