MySQL Security Talk slides

Direct play this episode at:
 
Download all podcasts at:
 
Subscribe to the podcast at:
 
Google Summer of Code Home Page:
 
MySQL Summer of Code Ideas:
 
MySQL Summer of Code Accepted Projects:
 
 
Feedback:

email
podcast@technocation.org

call the comment line at +1 617-674-2369

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

Or use the Technocation forums:
http://technocation.org/forum

Direct play this episode at:
 
Download all podcasts at:
 
Subscribe to the podcast at:
 
Google Summer of Code Home Page:
 
MySQL Summer of Code Ideas:
 
MySQL Summer of Code Accepted Projects:
 
 
Feedback:

email
podcast@technocation.org

call the comment line at +1 617-674-2369

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

Or use the Technocation forums:
http://technocation.org/forum

In the spirit of humor, healing here are 2 ways I know I am a MySQL geek. These are actually things I do….

You Know You’re a MySQL Geek When….
1. You cannot type the word “myself” without typing “mysql” first, troche deleting 2 characters and finishing out the word.
2. You type “show processlist” at the commandline when you really mean “ps -ef”

Anyone have anything they can add to the list?

Direct play this episode at:
 
Download all podcasts at:
 
Subscribe to the podcast at:
 
Google Summer of Code Home Page:
 
MySQL Summer of Code Ideas:
 
MySQL Summer of Code Accepted Projects:
 
 
Feedback:

email
podcast@technocation.org

call the comment line at +1 617-674-2369

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

Or use the Technocation forums:
http://technocation.org/forum

In the spirit of humor, healing here are 2 ways I know I am a MySQL geek. These are actually things I do….

You Know You’re a MySQL Geek When….
1. You cannot type the word “myself” without typing “mysql” first, troche deleting 2 characters and finishing out the word.
2. You type “show processlist” at the commandline when you really mean “ps -ef”

Anyone have anything they can add to the list?
In this episode, meningitis we go through how a B-tree works. The next episode will use what we learn in this episode to explain why MySQL indexes work the way they do.

Direct play this episode at:
http://technocation.org/content/oursql-episode-13%3A-nitty-gritty-indexes-0

Download all podcasts at:
http://technocation.org/podcasts/oursql/

Subscribe to the podcast at:
http://feeds.feedburner.com/oursql

Register for the MySQL Conference now!:
http://www.mysqlconf.com

Quiz to receive a free certification voucher from Proven Scaling:
http://www.provenscaling.com/freecert

MySQL Full Reference Cards:
http://www.visibone.com/sql

About B-Trees:
http://www.semaphorecorp.com/btp/algo.html

http://perl.plover.com/BTree/article.txt

Feedback:

Email podcast@technocation.org

call the comment line at +1 617-674-2369

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

Or use the Technocation forums:
http://technocation.org/forum

Direct play this episode at:
 
Download all podcasts at:
 
Subscribe to the podcast at:
 
Google Summer of Code Home Page:
 
MySQL Summer of Code Ideas:
 
MySQL Summer of Code Accepted Projects:
 
 
Feedback:

email
podcast@technocation.org

call the comment line at +1 617-674-2369

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

Or use the Technocation forums:
http://technocation.org/forum

In the spirit of humor, healing here are 2 ways I know I am a MySQL geek. These are actually things I do….

You Know You’re a MySQL Geek When….
1. You cannot type the word “myself” without typing “mysql” first, troche deleting 2 characters and finishing out the word.
2. You type “show processlist” at the commandline when you really mean “ps -ef”

Anyone have anything they can add to the list?
In this episode, meningitis we go through how a B-tree works. The next episode will use what we learn in this episode to explain why MySQL indexes work the way they do.

Direct play this episode at:
http://technocation.org/content/oursql-episode-13%3A-nitty-gritty-indexes-0

Download all podcasts at:
http://technocation.org/podcasts/oursql/

Subscribe to the podcast at:
http://feeds.feedburner.com/oursql

Register for the MySQL Conference now!:
http://www.mysqlconf.com

Quiz to receive a free certification voucher from Proven Scaling:
http://www.provenscaling.com/freecert

MySQL Full Reference Cards:
http://www.visibone.com/sql

About B-Trees:
http://www.semaphorecorp.com/btp/algo.html

http://perl.plover.com/BTree/article.txt

Feedback:

Email podcast@technocation.org

call the comment line at +1 617-674-2369

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

Or use the Technocation forums:
http://technocation.org/forum

This article shows the easiest way to audit commands to a MySQL database, refractionist assuming all content happens from an application. Now, viagra this will use a lot of storage, dosage and doubles the query load for each query, but it’s useful for when you know you want to capture the information of someone using the application.

The basic premise is simple. Logon to your nearest MySQL server and type the following:

SELECT CURRENT_USER(), USER();

Chances are the values are different. More on this later.

First, create a table:

CREATE TABLE `action` (
`user` varchar(77) NOT NULL default '',
`asuser` varchar(77) NOT NULL default '',
`db` varchar(64) NOT NULL default '',
`query` mediumtext NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Why varchar(77)? Because the mysql.user table puts a maximum of 16 characters for the username, and 60 characters for the hostname. And then there’s the 1 character “@”. Similarly, database names are limited to varchar(64).

The “asuser” column is the grant record that the user is acting as. For instance, a connection with the username “sheeri” from the host “www.sheeri.com” has a user value of “sheeri@www.sheeri.com” but may have an asuser value of “sheeri@’%.sheeri.com'” — whatever the GRANT statement that applies to my current user is. This is the difference between CURRENT_USER() and USER().

Then, create the function — here’s a PHP example:
function my_mysql_query ($query, $dblink) {
$action="INSERT INTO action (user,asuser,db,query) VALUES (CURRENT_USER(), USER(), DATABASE(), $query)";
mysql_query($action, $dblink);
mysql_query($query, $dblink);
}

Of course, we could also add in application specific information. For a web-based application where there is an overall password instead of a different password for each customer or user, this does not help. However in that case, a session username and client IP can be easily gotten from environment variables and used instead of the MySQL-specific “user@host”.

To use it, simply use my_mysql_query in place of mysql_query.

Note that this is the quick-and-dirty way to do it.

Direct play this episode at:
 
Download all podcasts at:
 
Subscribe to the podcast at:
 
Google Summer of Code Home Page:
 
MySQL Summer of Code Ideas:
 
MySQL Summer of Code Accepted Projects:
 
 
Feedback:

email
podcast@technocation.org

call the comment line at +1 617-674-2369

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

Or use the Technocation forums:
http://technocation.org/forum

In the spirit of humor, healing here are 2 ways I know I am a MySQL geek. These are actually things I do….

You Know You’re a MySQL Geek When….
1. You cannot type the word “myself” without typing “mysql” first, troche deleting 2 characters and finishing out the word.
2. You type “show processlist” at the commandline when you really mean “ps -ef”

Anyone have anything they can add to the list?
In this episode, meningitis we go through how a B-tree works. The next episode will use what we learn in this episode to explain why MySQL indexes work the way they do.

Direct play this episode at:
http://technocation.org/content/oursql-episode-13%3A-nitty-gritty-indexes-0

Download all podcasts at:
http://technocation.org/podcasts/oursql/

Subscribe to the podcast at:
http://feeds.feedburner.com/oursql

Register for the MySQL Conference now!:
http://www.mysqlconf.com

Quiz to receive a free certification voucher from Proven Scaling:
http://www.provenscaling.com/freecert

MySQL Full Reference Cards:
http://www.visibone.com/sql

About B-Trees:
http://www.semaphorecorp.com/btp/algo.html

http://perl.plover.com/BTree/article.txt

Feedback:

Email podcast@technocation.org

call the comment line at +1 617-674-2369

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

Or use the Technocation forums:
http://technocation.org/forum

This article shows the easiest way to audit commands to a MySQL database, refractionist assuming all content happens from an application. Now, viagra this will use a lot of storage, dosage and doubles the query load for each query, but it’s useful for when you know you want to capture the information of someone using the application.

The basic premise is simple. Logon to your nearest MySQL server and type the following:

SELECT CURRENT_USER(), USER();

Chances are the values are different. More on this later.

First, create a table:

CREATE TABLE `action` (
`user` varchar(77) NOT NULL default '',
`asuser` varchar(77) NOT NULL default '',
`db` varchar(64) NOT NULL default '',
`query` mediumtext NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Why varchar(77)? Because the mysql.user table puts a maximum of 16 characters for the username, and 60 characters for the hostname. And then there’s the 1 character “@”. Similarly, database names are limited to varchar(64).

The “asuser” column is the grant record that the user is acting as. For instance, a connection with the username “sheeri” from the host “www.sheeri.com” has a user value of “sheeri@www.sheeri.com” but may have an asuser value of “sheeri@’%.sheeri.com'” — whatever the GRANT statement that applies to my current user is. This is the difference between CURRENT_USER() and USER().

Then, create the function — here’s a PHP example:
function my_mysql_query ($query, $dblink) {
$action="INSERT INTO action (user,asuser,db,query) VALUES (CURRENT_USER(), USER(), DATABASE(), $query)";
mysql_query($action, $dblink);
mysql_query($query, $dblink);
}

Of course, we could also add in application specific information. For a web-based application where there is an overall password instead of a different password for each customer or user, this does not help. However in that case, a session username and client IP can be easily gotten from environment variables and used instead of the MySQL-specific “user@host”.

To use it, simply use my_mysql_query in place of mysql_query.

Note that this is the quick-and-dirty way to do it.
Well, page I came late, prostate so I missed the first one….so we start with #2

#2. Ambiguous GROUP BY —

query BUGS and include details on the corresponding PRODUCT rows —

SELECT b.bug_id, p.product_name from bugs b NATURAL JOIN bugs_products NATURAL JOIN products GROUP BY b.bug_id;

We use the GROUP BY to get one row per bug, but then you lose information.

Standard SQL says that GROUP BY queries require the GROUP BY columns to be in the SELECT clause. MySQL does not enforce this. If a column is in a SELECT clause but not in the GROUP BY clause it displays a random value.

[my note, not said in the presentation this fools people when they want the groupwise maximum, they think that selecting multiple columns and grouping means that you get some particular row ]

Solution 1: Restrict yourself to standard SQL — do not allow columns in SELECT if
use GROUP BY

Solution 2: Use GROUP_CONCAT() to get a comma-separated list of distinct values in the row.

SELECT b.bug_id, GROUP_CONCAT(p.product_name) AS product_names FROM bugs b NATURAL JOIN bugs_products NATURAL JOIN products GROUP BY b.bug_id;

Performance: no worse than doing a regular group function because the concat happens after the grouping is done.

3. EAV Tables — Entity-Attribute-Value Tables.

Example: product catalog w/ attributes, too many to use one column per attribute. Not every product has every attribute. ie, DVD’s don’t have pages and books don’t have a region encoding.

Most people make an “eav” table, that has the attribute name and value and the entity name. It associates the entity name (say, “spiderman DVD”) with an attribute (“region encoding”) and value (“region 1”)

Why is this bad? It’s harder to apply constraints because the column may have many different values (ie, # of pages should be a number but region encoding is a character). This may be a sign of a bad data model.

So why is this bad?

EAV cannot require an attribute — if you were doing many columns per table, you could specify NOT NULL (ie, price). Well, you could do that with TRIGGERs, but MySQL does not raise errors or abort an operation that spawned a trigger — in other words, you can’t stop the row from being inserted, just have an event when a row is inserted.

EAV cannot have referential integrity to multiple lookup tables, or only for some values.

It’s also expensive and complex to find all the attributes for one entity. In order to get one row that looks like normalized data, you need one join per attribute, and you may not even know how many there are.

Solution: Try not to use EAV tables, defining your attributes in your data model (ie, one table per attribute type). If you do, application logic should enforce constraints. Don’t try to fetch attributes in a single row (that looks like normalized data); fetch multiple rows and use the application code to reconstruct the entity.

4. Letting users crash your server
Example: people request ability to query database flexibility. So the antipattern is to give them access to run their own SQL.

Solution: give an interface which allows parameters to queries. But watch out for SQL injection!

Filter input escaping strings, or use parameterized queries.

6. Forcing primary keys to be contiguous

Example: managers don’t like gaps in invoice #’s. Antipattern is to try to reuse primary key values to fill in the gaps. Another antipattern is to change values to close the gaps.

Solution — deal with it. Do not reuse primary keys. Also, do not use auto_increment surrogate keys for everything if you do not need to.

Direct play this episode at:
 
Download all podcasts at:
 
Subscribe to the podcast at:
 
Google Summer of Code Home Page:
 
MySQL Summer of Code Ideas:
 
MySQL Summer of Code Accepted Projects:
 
 
Feedback:

email
podcast@technocation.org

call the comment line at +1 617-674-2369

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

Or use the Technocation forums:
http://technocation.org/forum

In the spirit of humor, healing here are 2 ways I know I am a MySQL geek. These are actually things I do….

You Know You’re a MySQL Geek When….
1. You cannot type the word “myself” without typing “mysql” first, troche deleting 2 characters and finishing out the word.
2. You type “show processlist” at the commandline when you really mean “ps -ef”

Anyone have anything they can add to the list?
In this episode, meningitis we go through how a B-tree works. The next episode will use what we learn in this episode to explain why MySQL indexes work the way they do.

Direct play this episode at:
http://technocation.org/content/oursql-episode-13%3A-nitty-gritty-indexes-0

Download all podcasts at:
http://technocation.org/podcasts/oursql/

Subscribe to the podcast at:
http://feeds.feedburner.com/oursql

Register for the MySQL Conference now!:
http://www.mysqlconf.com

Quiz to receive a free certification voucher from Proven Scaling:
http://www.provenscaling.com/freecert

MySQL Full Reference Cards:
http://www.visibone.com/sql

About B-Trees:
http://www.semaphorecorp.com/btp/algo.html

http://perl.plover.com/BTree/article.txt

Feedback:

Email podcast@technocation.org

call the comment line at +1 617-674-2369

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

Or use the Technocation forums:
http://technocation.org/forum

This article shows the easiest way to audit commands to a MySQL database, refractionist assuming all content happens from an application. Now, viagra this will use a lot of storage, dosage and doubles the query load for each query, but it’s useful for when you know you want to capture the information of someone using the application.

The basic premise is simple. Logon to your nearest MySQL server and type the following:

SELECT CURRENT_USER(), USER();

Chances are the values are different. More on this later.

First, create a table:

CREATE TABLE `action` (
`user` varchar(77) NOT NULL default '',
`asuser` varchar(77) NOT NULL default '',
`db` varchar(64) NOT NULL default '',
`query` mediumtext NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Why varchar(77)? Because the mysql.user table puts a maximum of 16 characters for the username, and 60 characters for the hostname. And then there’s the 1 character “@”. Similarly, database names are limited to varchar(64).

The “asuser” column is the grant record that the user is acting as. For instance, a connection with the username “sheeri” from the host “www.sheeri.com” has a user value of “sheeri@www.sheeri.com” but may have an asuser value of “sheeri@’%.sheeri.com'” — whatever the GRANT statement that applies to my current user is. This is the difference between CURRENT_USER() and USER().

Then, create the function — here’s a PHP example:
function my_mysql_query ($query, $dblink) {
$action="INSERT INTO action (user,asuser,db,query) VALUES (CURRENT_USER(), USER(), DATABASE(), $query)";
mysql_query($action, $dblink);
mysql_query($query, $dblink);
}

Of course, we could also add in application specific information. For a web-based application where there is an overall password instead of a different password for each customer or user, this does not help. However in that case, a session username and client IP can be easily gotten from environment variables and used instead of the MySQL-specific “user@host”.

To use it, simply use my_mysql_query in place of mysql_query.

Note that this is the quick-and-dirty way to do it.
Well, page I came late, prostate so I missed the first one….so we start with #2

#2. Ambiguous GROUP BY —

query BUGS and include details on the corresponding PRODUCT rows —

SELECT b.bug_id, p.product_name from bugs b NATURAL JOIN bugs_products NATURAL JOIN products GROUP BY b.bug_id;

We use the GROUP BY to get one row per bug, but then you lose information.

Standard SQL says that GROUP BY queries require the GROUP BY columns to be in the SELECT clause. MySQL does not enforce this. If a column is in a SELECT clause but not in the GROUP BY clause it displays a random value.

[my note, not said in the presentation this fools people when they want the groupwise maximum, they think that selecting multiple columns and grouping means that you get some particular row ]

Solution 1: Restrict yourself to standard SQL — do not allow columns in SELECT if
use GROUP BY

Solution 2: Use GROUP_CONCAT() to get a comma-separated list of distinct values in the row.

SELECT b.bug_id, GROUP_CONCAT(p.product_name) AS product_names FROM bugs b NATURAL JOIN bugs_products NATURAL JOIN products GROUP BY b.bug_id;

Performance: no worse than doing a regular group function because the concat happens after the grouping is done.

3. EAV Tables — Entity-Attribute-Value Tables.

Example: product catalog w/ attributes, too many to use one column per attribute. Not every product has every attribute. ie, DVD’s don’t have pages and books don’t have a region encoding.

Most people make an “eav” table, that has the attribute name and value and the entity name. It associates the entity name (say, “spiderman DVD”) with an attribute (“region encoding”) and value (“region 1”)

Why is this bad? It’s harder to apply constraints because the column may have many different values (ie, # of pages should be a number but region encoding is a character). This may be a sign of a bad data model.

So why is this bad?

EAV cannot require an attribute — if you were doing many columns per table, you could specify NOT NULL (ie, price). Well, you could do that with TRIGGERs, but MySQL does not raise errors or abort an operation that spawned a trigger — in other words, you can’t stop the row from being inserted, just have an event when a row is inserted.

EAV cannot have referential integrity to multiple lookup tables, or only for some values.

It’s also expensive and complex to find all the attributes for one entity. In order to get one row that looks like normalized data, you need one join per attribute, and you may not even know how many there are.

Solution: Try not to use EAV tables, defining your attributes in your data model (ie, one table per attribute type). If you do, application logic should enforce constraints. Don’t try to fetch attributes in a single row (that looks like normalized data); fetch multiple rows and use the application code to reconstruct the entity.

4. Letting users crash your server
Example: people request ability to query database flexibility. So the antipattern is to give them access to run their own SQL.

Solution: give an interface which allows parameters to queries. But watch out for SQL injection!

Filter input escaping strings, or use parameterized queries.

6. Forcing primary keys to be contiguous

Example: managers don’t like gaps in invoice #’s. Antipattern is to try to reuse primary key values to fill in the gaps. Another antipattern is to change values to close the gaps.

Solution — deal with it. Do not reuse primary keys. Also, do not use auto_increment surrogate keys for everything if you do not need to.

For those wanting the slides for “Testing the Security of Your Site”, anorexia 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

Comments are closed.