XML and MySQL Privileges

The granularity found in MySQL's privilege system with regard to most database objects does not extend to XML documents. A MySQL client having access to an XML document can access the entire document or any part of it, and there is no way in which this can be restricted to particular XML fragments or elements.

Keep in mind that MySQL does not have preventative privileges; that is, you cannot disallow access of a given type on a particular database object. Instead, if you wish to prevent a client from accessing XML found in a database table, then you must grant privileges to this client in such a way that the client has no access to the table at all, or only to columns of that table which do not contain the XML that you do not wish the client to read.

XPath Injection

One of the greatest security threats to applications is code injection whereby malicious code is introduced into the system to gain unauthorized access to privileges and data. These are known to exist in any number of programming and scripting languages; what they all have in common is the exploiting of the assumptions made by developers about the type and content of data input from users. XPath is no exception in this regard.

Let's suppose that your application handles authorization by matching the combination the combination of a login names and password with those found in an XML file named users.xml, whose contents are shown here:

<?xml version="1.0" encoding="UTF-8"?>
<users>
  <user id="00327">
    <login>douglas42</login>
    <password>24ph0d</password>
  </user>
  <user id="13579">
    <login>cherrygarcia</login>
    <password>1c3cr34m</password>
  </user>
  <user id="02403">
    <login>jimbob</login>
    <password>p4nc4k35</password>
  </user>
  <user id="42354">
    <login>kitten</login>
    <password>m3330w</password>
  </user>
  <user id="28570">
    <login>lucyvanpelt</login>
    <password>f0076411</password>
  </user>
</users>

Assuming that each user's combination of login name and password is unique, your application could use an XPath expression like this one to validate the user, begin a user session, and associate the session with the user's unique ID:

//user[login/text()='cherrygarcia' and password/text()='1c3cr34m']/attribute::id

This is the XPath equivalent of an SQL statement like this one:

SELECT id FROM users WHERE login='cherrygarcia' AND password='1c3cr34m';

A PHP application employing XPath and the users.xml file might handle the login process via a Web form like this:

<?php

  $file     =   "users.xml";
  
  $login    =   $POST["login"];
  $password =   $POST["password"];

  $xpath = "//user[login/text()=$login and password/text()=$password]/attribute::id";

  if( file_exists($file) )
  {
    $xml = simplexml_load_file($file);
    
    if($result = $xml->xpath($xpath))
      echo "You are now logged in as user $result[0].";
    else
      echo "Invalid login name or password.";
  }
  else
    exit("Failed to open $file.");
  
?>

The input is completely unchecked, which means that a malevolent user can short-circuit the test by entering ' or 1=1 for both the login name and password, resulting in $xpath being evaluated as shown here:

//user[login/text()='' or 1=1 and password/text()='' or 1=1]/attribute::id

The expression inside the square brackets evaluates as true, and so is effectively the same as this one, which matches the id attribute of every user element in the XML document:

//user/attribute::id

One way in which this particular attack can be circumvented is simply by quoting the variable names to be interpolated in the definition of $xpath:

$xpath = "//user[login/text()='$login' and password/text()='$password']/attribute::id";

This forces the values passed from the user to be converted to strings.

If this “fix” seems familiar, that is because it is the same one that is often recommended for helping to prevent SQL injection attacks. In general, the practices you should follow for preventing XPath injection attacks are the same as for preventing SQL injection:

• Never accepted untested data from users in your application.

• Check all user-submited data for type; reject or convert data that is of the wrong type

• Test numerical data for out of range values; truncate, round, or reject values that are out of range. Test strings for illegal characters and either strip them out or reject input containing them.

• Do not output explicit error messages that might provide an unauthorized user with clues that could be used to compromise the system; log these to a file or database table instead.

Just as SQL injection attacks can be used to obtain information about database schemas, so can XPath injection be used to traverse XML files to uncover their structure. Space does not permit us to go into detail here, but you can read more about such techniques in Amit Klein's paper Blind XPath Injection (PDF, 46KB).

It is also important to check the output being sent back to the client. For an example, let's revisit our previous example; however, this time, instead of using PHP's XPath functionality, we use the MySQL ExtractValue() function:

mysql> SELECT ExtractValue(
    ->     LOAD_FILE('users.xml'), 
    ->     '//user[login/text()="" or 1=1 and password/text()="" or 1=1]/attribute::id' 
    -> ) AS id;
+-------------------------------+
| id    |
+-------------------------------+
| 00327 13579 02403 42354 28570 |
+-------------------------------+
1 row in set (0.01 sec)

Because ExtractValue() returns multiple matches as a single space-delimited string, this injection attack provides to the user in a single row every valid ID contained within users.xml. As an extra safeguard, you should also test output before returning it to the user. Here is a simple example:

mysql> SELECT @id = ExtractValue(
    ->     LOAD_FILE('users.xml'),
    ->     '//user[login/text()="" or 1=1 and password/text()="" or 1=1]/attribute::id' 
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT IF(
    ->     INSTR(@id, ' ') = 0, 
    ->     @id,
    ->     'Unable to retrieve user ID')
    -> AS singleID;
+----------------------------+
| singleID       |
+----------------------------+
| Unable to retrieve user ID |
+----------------------------+
1 row in set (0.00 sec)

In general, the guidelines for returning data to users securely are the same as for accepting user input. These can be summed up as:

• Always test outgoing data for type and allowable values.

• Never allow unauthorized users to view error messages that might provide information about the application that could be used to exploit it.

Following these principles in architecting an XML application can help to insure that it is a secure application.

---