Tuesday, February 08, 2011

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.

2 comments:

RD said...

Their is certainly many fun things you can do with functions and procedures. I use them to analyse things or generate statistics all the time, given we use postgresql not mysql.

This is a query I wrote just a few days ago for some statics related to quiz questions from a online testing program we wrote. =)

drop type question_summary_data CASCADE;
create type question_summary_data as(
question_number text,
correct boolean,
totavg int,
avgcnt int,
response_count int,
question_count int,
content text,
question text);

create or replace function question_summary_by_session(in skey varchar(255))
RETURNS setof question_summary_data
AS $$
BEGIN
drop table if exists tmp;
CREATE TEMP TABLE tmp
(
question_id int,
response_id int,
rcnt int,
qcnt int,
avgcnt int
);

delete from tmp;

insert into tmp select cast(rtot.question_id as int) as question_id,
cast(rtot.response_id as int) as response_id,
cast(rtot.rcnt as int) as rcnt,
cast(qtot.qcnt as int) as qcnt,
cast((100 * cast(rcnt as float) / cast(qcnt as float)) as integer) as avgcnt
from
(select
qt.question_id, qt.response_id, count(*) as rcnt
from
quiztrack qt
where
qt.session_key = skey
group by qt.question_id, qt.response_id)rtot
left join
(select
qt.question_id, count(*) as qcnt
from
quiztrack qt
where
qt.session_key = skey
group by qt.question_id)qtot
on
(rtot.question_id = qtot.question_id);

return QUERY select
rv.question_number,
rv.correct,
cast(COALESCE(qc.avgcnt , 0) as int) as totavg,
cast(COALESCE(qps2.avgcnt, 0) as int) as avgcnt,
cast(COALESCE(qps2.rcnt , 0) as int) as response_count ,
cast(COALESCE(qc.qcnt , 0) as int) as question_count,
rv.content,
qv.question
from
response_view rv left join question_view qv on ( rv.question_number = qv.question_number )
left join
(select * from
(select
qv.question_number, rv.response_id, tmp_sub1.rcnt, tmp_sub1.qcnt, tmp_sub1.avgcnt, qv.question, rv.content, rv.correct
from
tmp tmp_sub1
left join question_view qv on (tmp_sub1.question_id = qv.question_uuid)
left join response_view rv on (tmp_sub1.response_id = rv.response_id and rv.question_number = qv.question_number)
order by qv.question_number, rv.response_id
)qps where qps.correct = true
)qc on (rv.question_number = qc.question_number)
left join
(select
qv.question_number, rv.response_id, tmp_sub2.rcnt, tmp_sub2.qcnt, tmp_sub2.avgcnt, qv.question, rv.content, rv.correct
from
tmp tmp_sub2
left join question_view qv on (tmp_sub2.question_id = qv.question_uuid)
left join response_view rv on (tmp_sub2.response_id = rv.response_id and rv.question_number = qv.question_number)
order by qv.question_number, rv.response_id
) qps2 on (rv.question_number = qps2.question_number and rv.response_id = qps2.response_id);
END;
$$ LANGUAGE plpgsql;

y-intercept said...

RD, feel free to link to the page.

BTW, My post was a squidgen disingenuous. I conveniently left out the part about few decades experience writing PL/SQL for Oracle Databases.

I had two hidden motives for this post. The first was that the examples I found googling "MySQL Stored Procedures" were all weak. Some of the examples in online tutorials had flaws and could not compile.

The second motive was that I wanted to encourage any amateur programmer out there to encrypt their email addresses. Spammers often get email addresses by hacking databases. Any obfuscation added to a database helps reduce.

Encrypting email addresses is a very good example for learning a procedural SQL languages.

Unfortunately, I can't find a good hidey place to stick the security token.