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 "firstname.lastname@example.org" & 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 email@example.com with the SQL Query:
SELECT * FROM User_Table WHERE email_id = getEmailId('firstname.lastname@example.org');
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.
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(
create or replace function question_summary_by_session(in skey varchar(255))
RETURNS setof question_summary_data
drop table if exists tmp;
CREATE TEMP TABLE tmp
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
qt.question_id, qt.response_id, count(*) as rcnt
qt.session_key = skey
group by qt.question_id, qt.response_id)rtot
qt.question_id, count(*) as qcnt
qt.session_key = skey
group by qt.question_id)qtot
(rtot.question_id = qtot.question_id);
return QUERY select
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,
response_view rv left join question_view qv on ( rv.question_number = qv.question_number )
(select * from
qv.question_number, rv.response_id, tmp_sub1.rcnt, tmp_sub1.qcnt, tmp_sub1.avgcnt, qv.question, rv.content, rv.correct
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)
qv.question_number, rv.response_id, tmp_sub2.rcnt, tmp_sub2.qcnt, tmp_sub2.avgcnt, qv.question, rv.content, rv.correct
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);
$$ LANGUAGE plpgsql;
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.
Post a Comment