My First MySQL Function

My fancy new cloud server uses MySQL 5.0.91 which includes support for stored functions and procedures. I realize this release is old hat to the annointed, but it's new to me.

Anyway, I spent most of today writing my very first MySQL STORED FUNCTION.

While writing the function, I realized that I had become addicted to a debugger. Apparently, the MySQL doesn't say what is wrong with a program. It simply fails if something is wrong with the syntax.

Finally, I decided to write the program running each line of code through the SQL Query window until I had a procedure that worked.

Anyway, the goal of my first STORED FUNCTION is to encrypt and decrypt the email addresses from the Registration form. The registration form encrypted the email addresses, but I never wrote a decrypt program.

Anyway, since I will release some new features, I decided I should create a password reset program. So, I decided it would be helpful to be able to decrypt the email addresses.

In this new program, I will store the encrypted email addresses in the table Email_X. It has the definition. email_id is a primary key with AUTO_INCREMENT

CREATE TABLE IF NOT EXISTS Email_X (
  email_id int(10) unsigned NOT NULL auto_increment,
  email_bin varbinary(255) NOT NULL,
  decrypt_cnt int(11) NOT NULL default '0',
  block_yn char(1) NOT NULL default 'N',
  PRIMARY KEY  (email_id)
) AUTO_INCREMENT=1 ;

The getEmailId() takes an email address. Converts it to lower case and runs AES_Encrypt with the unimaginative password "password". I convert to make searching for an email address easier. "Bob@example.com" encrypts differently than "bob@example.com" & and I want to find all occurences of Bob's email address.

I will store the email_id in the user table. This makes it easy to find all the accounts owned by the same email address because they will have the name number.

I could find all the accounts for bob@example.com with the SQL Query:

SELECT * 
  FROM User_Table 
 WHERE email_id = getEmailId('bob@example.com');

DROP FUNCTION IF EXISTS getEmailId;

DELIMITER $$

CREATE FUNCTION getEmailId(str VARCHAR(255))
  RETURNS INT
BEGIN
  DECLARE rv INT DEFAULT 0;
  DECLARE encrypted VARBINARY(255) DEFAULT NULL;
  IF (str = '') THEN
    SET rv = 0;
  ELSE 
     SET encrypted = AES_Encrypt(Lower(str),'password');
     SELECT email_id INTO rv FROM Email_X WHERE email_bin = encrypted; 
     IF !(rv > 0) THEN
        INSERT INTO Email_X (email_bin) VALUES (encrypted);
        SET rv = LAST_INSERT_ID();
     END IF;
  END IF;
  RETURN rv;
END$$

DELIMITER ;

I can now call this function with an email address. It will look to see if the address is in Email_X. If so, it returns its current id. If not it will create a new entry in Email_X with an encrypted string in the place of the email addresss.

To decrypt the email addresses, I created the following program:

DROP FUNCTION IF EXISTS getEmailAddr;

DELIMITER $$

CREATE FUNCTION getEmailAddr(eid INT)
  RETURNS VARCHAR(255)
BEGIN
  DECLARE rv VARCHAR(255) DEFAULT NULL;
  DECLARE blockMe CHAR(1) DEFAULT 'N';
  IF eid > 0 THEN
     SELECT AES_Decrypt(email_bin, 'password'), block_yn INTO rv, blockMe  FROM Email_X 
      WHERE email_id = eid; 
     IF blockMe = 'N' THEN 
       UPDATE Email_X SET decrypt_cnt = decrypt_cnt + 1 
        WHERE email_id = eid;
     ELSE 
       SET rv = '';
     END IF;
  END IF;
  RETURN rv;
END$$

DELIMITER ;

getEmailAddr() takes a number then decrypts the encrypted binary string. The function updates a counter every time someone accesses an address. I also added the block_yn feature. If a person requests their email address removed from the system, I can block it at the encryption level.

The next step in this program is to create a send mail program.

The send mail program will decrypt the email address and record the transaction. The number of decrypts should equal the number of emails sent. If it does not, then I know I have a hacker going at the database.