the life and times of endenizen » mysql

Today I wrote my first stored procedure. It was pretty exciting. I grew up with MySQL and it has finally grown up too. Joining the ranks of IBM’s DB2 and MS SQL, MySQL now allows the usage of pure fanciness (that’s the one-word summary of everything you can do with stored procedures). Now, don’t be mistaken, my stored procedure was anything but fancy. In fact, it was a “Hello World!” of sorts (and if you’re not familiar with that and you’re still reading, well you must really like me because this is probably going way over your head…).

Stored procedures are most popular with admins who need extra speed and security, and less network traffic. A bank, for instance, may use a stored procedure rather than exposing the database code used to execute a query to prevent anyone from modifying that query before it is sent to the server. Using a stored procedure, the query would be stored on the server and referenced with a procedure name. The only code the end-user (the web-developer, or an evil hacker) would be allowed to change is the parameters they pass to the procedure. Here’s a real example:

Moving money from one account (frank) to another (bob) may look like this:

INSERT $5 INTO bob SELECT $5 FROM frank

If I was a developer, I might perform evil magic by changing this to:

INSERT $4.99 INTO bob INSERT $0.01 INTO brian SELECT $5 FROM frank

Clearly this is a bad idea, because a penny is large enough to notice, but say I were selecting much smaller amounts from a much larger pool of transactions… it would be like taking pennies from the tray. Not the jar, that’s for the crippled children. The tray is for everyone.

Using a stored procedure the code (which is stored on the database, never to be changed) might be:

PROCEDURE transfer (to, from, amount)
INSERT INTO to SELECT amount FROM from

Now, the developer has to run a procedure and the procedure may only be passed two accounts and an amount. Presuming the bank went so far as to log each transaction, this would mean any malicious activity would show up because it would have to be a performed in a separate transaction. For the evil hacker, the most they could do is a SQL injection to modify or add parameters which, in this case, would be much less of a problem than if they could add on their own queries.

Security is only one of the many benefits of stored procedures, and most DBMSs have had them for years. Before version 5.0, MySQL didn’t have any stored procedures and therefore didn’t occupy much of the financial, or otherwise important, sectors of the IT market. That’s not to say MySQL’s use in these areas will now increase dramatically, but certainly more database administrators will weigh MySQL’s benefits as a clear competitor with costly alternatives like MS SQL.

Disclaimer: Don’t try this example code at home, it probably won’t work and was only used for very simple illustration. There isn’t actually a way to move money in a bank by referencing $5, that’s just dumb. Also, I don’t really have a ton of stored procedure experience (hence the “Hello World!” comment above) so as always, do your own research and see what works best in your situation.