Security Considerations

With any database functionality, you must be mindful of its security implications, and working with MySQL's XML and XPath capabilities is no different in this regard. There are a number of points to consider, including the ability to read from files using LOAD XML INFILE, the fact that the MySQL privilege system does not apply to the content of XML documents, and the possibilities for subversive user input to have unintended consequences.

Loading Data from Files

As with the LOAD DATA statement, the transfer of the XML file from the client host to the server host is initiated by the MySQL server. In theory, a patched server could be built that would tell the client program to transfer a file of the server's choosing rather than the file named by the client in the LOAD XML statement. Such a server could access any file on the client host to which the client user has read access.

In a Web environment, clients usually connect to MySQL from a Web server. A user that can run any command against the MySQL server can use LOAD XML LOCAL to read any files to which the Web server process has read access. In this environment, the client with respect to the MySQL server actually is the Web server, not the remote program being run by the user who connects to the Web server.

You can disable LOAD XML on the server by starting it with --local-infile=0 or --local-infile=OFF. The result is shown in this example:

shell> mysqld_safe --local-infile=OFF &

shell> mysql -uroot xtest
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 6.0.4-alpha-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> LOAD XML LOCAL INFILE '/home/jon/person.xml' 
    -> INTO TABLE person 
    -> ROWS IDENTIFIED BY '<person>';
ERROR 1148 (42000): The used command is not allowed with this MySQL version 

This option can also be used when starting the mysql client to disable LOAD XML for the duration of the client session.

To prevent a client from loading XML files from the server, do not grant the FILE privilege to the corresponding MySQL user account, or revoke this privilege if the client user account already has it:

shell> mysql -uroot -p
Password: ********
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 6.0.4-alpha-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> REVOKE FILE ON *.* FROM jon@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
shell> mysql -ujon -p
Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 6.0.4-alpha-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> USE xmltest;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

mysql> LOAD XML INFILE '/home/jon/person.xml' 
    -> INTO TABLE person 
    -> ROWS IDENTIFIED BY '>person>';
ERROR 1045 (28000): Access denied for user 'jon'@'localhost' (using password: YES)

Important

Revoking the FILE privilege (or not granting it in the first place) keeps the user only from executing the LOAD XML INFILE statement or the LOAD_FILE() function; it does not prevent the user from executing LOAD XML LOCAL INFILE. To disallow this statement, you must start the server or the client with --local-infile=OFF, as discussed in the previous section (see the section called “Loading Data from Files”).

In other words, the FILE privilege affects only whether the client can read files on the server; it has no bearing on whether the client can read files on the local filesystem.