While researching an article I came across a piece at http://www.simple-talk.com/sql/t-sql-programming/cursors-and-embedded-sql/. Basically the author says "embedded SQL" is bad -- meaning developers should never put SQL in their code. Nor should they use ORM tools to generate SQL for them.

Instead, they should access everything they need through stored procedures. I have mixed feelings about this. On one hand, you have to give table-access permissions to users and then deal with the resulting security risks sounds very control-freakish to me. On the other hand, I agree that embedded code can be bad because if you change the database model in any way, you have to rewrite the procedural code that relies on the existence of the previous model.

And of course, stored procedures also help make your code more modular. But this article basically advocates that database administrators really need to do a lot of heavy coding into the database.

(The title of this post is just something that came to me when I read the article, because the author's opinions were sparked by a cursor gone bad. (cursors gone wild?) )

So, O'Reilly's ONLamp.com has published the "Top 10 MySQL Best Practices" at http://www.onlamp.com/pub/a/onlamp/2002/07/11/MySQLtips.html. Sadly, I find most "best practice" list do not thoroughly explain the "why" enough so that people can make their own decisions.

For instance, #3 is "Protect the MySQL installation directory from access by other users." I was intrigued at what they would consider the "installation" directory. By reading the tip, they actually mean the data directory. They say nothing of the log directory, nor that innodb data files may be in different places than the standard myisam data directories.

They perpetuate a myth in #4, "Don't store binary data in MySQL." What they really mean is "don't store large data in MySQL", which they go into in the tip. While it's true that there is very little benefit to having binary data in a database, they don't go into what those benefits are. This means that people can't make informed decisions, just "the best practice is this so I'm doing it."

The benefit of putting binary data in MySQL is to be able to associate metadata and other data. For instance, "user 200 owns file 483". If user 200 is gone from the system, how can you make sure file 483 is as well? There's no referential integrity unless it's in the database. While it's true that in most cases people would rather sacrifice the referential integrity for things like faster database backups and easier partitioning of large data objects, I believe in giving people full disclosure so they can make their own informed decision.

#5 is my biggest pet peeve. "Stick to ANSI SQL," with the goal being to be able to migrate to a different platform without having to rewrite the code. Does anyone tell Oracle folks not to use pl/sql like collections? Nobody says "SQL is a declarative language, pl/sql is procedural therefore you should never use it". How about SQL Server folks not to use transact-sql statements like WAITFOR? MATCH... AGAINST is not standard SQL, so I should never use it?

Now, of course, if you're selling a product to be run on different database platforms, then sure, you want to be platform agnostic. But you'd know that from the start. And if you have to migrate platforms you're going to have to do lots of work anyway, because there are third-party additions to all the software any way.

And why would *anyone* choose a specific database, and then *not* use those features? I think that it's a good tip to stick to ANSI SQL if you *know* you want to, or if you have no idea about the DBMS you're using.

If you want to see how this cripples MySQL, check out Visibone's SQL chart at: http://www.visibone.com/sql/chart_1200.jpg -- you can buy it here:

I would amend #6 by explaining how the industry standard is to use sequences, as well as data tables such as calendar tables, and that stored procedures should be built to deal with sequences. The problem with a coded solution is that it is difficult do the proper locking needed for sequences if more than one access is needed at a time. The reason I say stored procedures is that I believe that the database should handle the locking of tables, not the code, just as I believe referential integrity should be enforced at the database level.

Everything else is pretty good. I had a list a while back of "SQL Best Practices" here: http://sheeri.com/archives/104. I may post later on about my own personal MySQL Best Practices....

Mehlam Shakir, CTO of RippleTech, discusses a practical approach for auditing MySQL databases to meet security and compliance regulations. Hear real-world cases and see a live demonstration of how RippleTech's Informant solution compliments MySQL by adding a security layer without any performance impact.

For more information on RippleTech's INFORMANT, visit http://www.rippletech.com/

I have to say, I was a bit worried this would be a typical vendor presentation where every other word is marketing speak for how great the product is. It actually just ended up being "here's how Informant works, and here's how auditing, security and compliance needs can be met," presented in a way that's useful and valuable to anyone who is interested in auditing or security.

Rippletech's Informant is not only interesting because it's currently the only software that audits MySQL, but it's impressive in its simplicity and flexibility. I think my favorite surprise about Informant was that it has the ability to store a user session as just that.

Download the video of the presentation at:
http://technocation.org/movies/mysql/AuditingRippleTech2007MayUGbig.wmv">http://technocation.org/movies/mysql/AuditingRippleTech2007MayUGbig.wmv

http://technocation.org/movies/mysql/AuditingRippleTech2007MayUGbig.wmv (446 Mb)

For those wanting the slides for "Testing the Security of Your Site", they're at:

http://www.sheeri.com/presentations/MySQLSecurity2007_04_24.pdf -- 108 K PDF file

http://www.sheeri.com/presentations/MySQLSecurity2007_04_24.swf -- 56 K Flash file

and some code:

For the UserAuth table I use in the example to test SQL injection (see slides):

CREATE TABLE UserAuth (userId INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, uname VARCHAR(20) NOT NULL DEFAULT '' UNIQUE KEY, pass VARCHAR(32) NOT NULL DEFAULT '') ENGINE=INNODB DEFAULT CHARSET=UTF8;


Populate the table:

INSERT INTO UserAuth (uname) VALUES ('alef'),('bet'),('gimel'),('daled'),('hay'),('vav'),('zayin'),('chet'),('tet'),('yud'),('kaf'),('lamed'),('mem'),('nun'),('samech'),('ayin'),('pe'),('tsadik'),('kuf'),('resh'),('shin'),('tav');
UPDATE UserAuth SET pass=MD5(uname) WHERE 1=1;


Test some SQL injection yourself:
go to Acunetix's test site: http://testasp.acunetix.com/login.asp

Type any of the following as your password, with any user name:
anything' OR 'x'='x
anything' OR '1'='1
anything' OR 1=1
anything' OR 1/'0
anything' UNION SELECT 'a
anything'; SELECT * FROM Users; select '
1234' AND 1=0 UNION ALL SELECT 'admin', '81dc9bdb52d04dc20036dbd8313ed055


And perhaps some of the following:
ASCII/Unicode equivalents (CHAR(39) is single quote)
Hex equivalents (0x27, ie SELECT 0x27726F6F7427)
-- for comments

The February Boston MySQL User Group meeting was great! I spoke about MySQL security; you can now download the slides and the video. I continue to be impressed with the sound quality of the video camera I have, I was pretty good about repeating the question folks asked, but you can clearly hear it in the audio (well, I could when I was wearing headphones, but I also have pretty bad hearing).

Special thanks to http://technocation.org for hosting the bandwidth for the videos.

Topics covered in the talk:
ACLs
Test dbs & anonymous accounts
OS files and permissions
Application data flow
SQL Injection
XSS (Cross-site scripting)

PDF of slides (1.4M):
http://www.sheeri.com/presentations/MySQLSecurity2007_02_08.pdf

Slides in Flash (107K):
http://www.sheeri.com/presentations/MySQLSecurity2007_02_08.swf

Video of presentation (large, 289M)
http://technocation.org/videos/original/mysqlsecurity2007_02_08large.wmv

Video of presentation (small, 27M)
http://technocation.org/videos/original/mysqlsecurity2007_02_08small.wmv

Listener feedback:

MySQL will go public. Would you buy stock if you had the money? Why or why not?
Call the comment line at +1 617-674-2369 (US phone number)

Use Odeo to leave a voice mail through your computer:
http://odeo.com/sendmeamessage/Sheeri

Leave a message at the Technocation forums:
http://technocation.org/forum

Send an e-mail to podcast@technocation.org

Episode 8 Show Notes:
This episode's feature is basic MySQL Security. Not only will we discuss what the basic security is, but we'll discuss the *why*s, not just the how's.

Direct play this episode at:
http://technocation.org/content/oursql-episode-8%3A-basic-mysql-security-0

Subscribe to the podcast by clicking:
http://phobos.apple.com/WebObjects/MZStore.woa/wa/viewPodcast?id=206806301

You can Direct download all the oursql podcasts at:
http://technocation.org/podcasts/oursql/

News
MySQL offers an unlimited number of Gold licenses per year for $40,000:
http://mysql.com/products/enterprise/unlimited.html
http://mysql.com/products/enterprise/features.html

MySQL begins to talk about going public: http://www.businessreviewonline.com/os/archives/2007/01/mysql_set_to_jo.html

Learning Resource:
http://www.hackmysql.com

Feature -- MySQL Security:
Bruce Scneier's latest Crypto-Gram newsletter refers to an article where a person gets on an airplane, having bypassed all airport security via climbing a fence.
http://www.schneier.com/crypto-gram-0701.html
http://www.newsobserver.com/102/story/523482.html

Feedback
To leave a comment, suggestion, question or other feedback:

Call the comment line at +1 617-674-2369 (US phone number)

Use Odeo to leave a voice mail through your computer:
http://odeo.com/sendmeamessage/Sheeri

Leave a message at the Technocation forums:
http://technocation.org/forum

Send an e-mail to podcast@technocation.org

Acknowledgements/Sponsors
www.technocation.org
http://music.podshow.com
www.russellwolff.com
http://www.smallfishadventures.com/Home.html “The Thank you song” -- Smallfish

So I found this piece of code today:

public final boolean isValidPassword(String password) {
String inputHash = Crypto.hash(password);
String correctHash = getPasswordHash();
return inputHash.equals(correctHash);
}

I am not quite sure what the thought process was behind this -- getPasswordHash is a method that simply retrieves a field from the database, so this method gets the password has from the database, hashes the password given, and then uses String.equals() to compare the two.

Why on earth would someone do this instead of just checking the password? I totally understand if the getPasswordHash() method salted the password, or something, but it does not.......

So, Markus Popp's recent blog entry about trying to give a user permissions to all databases except one got me thinking.

MySQL has grown immensely, and like many products, new features are compatible with old features. . . somewhat.

Review/baseline:

For current versions of MySQL, permissions are granted and revoked by the GRANT and REVOKE commands. In older versions, administrators had to muck with the access control tables by hand, and then FLUSH PRIVILEGES to enable the new access controls.

The blog entry got me thinking. Currently, if you want to remove all rights from a user, including the ability to login, you have to REVOKE privileges and then DELETE from the mysql.user table. And then, of course, FLUSH PRIVILEGES because you manually changed that table.

It would be great if an administrator never HAD to play with the mysql.user table.

Now, that seems like a small request. But there also does not seem to be a SHOW ALL GRANTS command. Currently an administrator has to run SELECT host,user FROM mysql.user to see who might have access, and then run a SHOW GRANTS command to see who has access to what.

An alternative to that is to check out the other tables in the mysql database. However, what I'd like to see is the ability to see privileges easier than checking out tables with over a dozen fields.

This would include extending the SHOW GRANTS syntax to be able to show grants for all users, and showing grants for a user (for instance, SHOW ALL GRANTS FOR USERNAME="sheeri").

And, of course, similar functionality in REVOKE syntax. I'd love to revoke grants from a user at all their entries with a touch of a button.

In the current system, administrators get punished for having tight security and only allowing what's necessary. There are more entries in the mysql.users table, and adding, seeing, and removing privileges requires many steps.

Perhaps what I am describing is overkill for the database? Maybe I am just describing an admin interface? But I really think that having commands to do half the functionality (seeing, granting and revoking per user/hostname combination) and having to play with tables for the other half (revoking login access, finding out the user/hostname combination) is somewhat lacking.

Or is this a function of the fact that SHOW GRANTS is available to many users by default and access to the mysql database is not? I could see security implications if the syntax was extended. . .but that's easy enough to do by extending the access rights and adding a SHOW GRANTS right (if there is not already one)

(by the way, I'm using 4.1 and haven't had a chance to play with 5.0 or 5.1, so if this functionality is in a later version, or coming soon, please let me know)

If you store a user's User-Agent and use it again, make sure you scrub that data first.

[If you store anything, make sure you scrub it. Of course, this isn't user-inputted data, it's data that the server gets from the client's browser.]

We were hacked? abused? today by a member who had javascript in place of his User Agent. A very clever hack. However, we have learned our lesson.

Here's hoping you do, too.