XPath SQL Injection in OpenEMR

Published in

R3d Buck3T

8 min readNov 16, 2020

Data Exfiltration in OpenEMR 2018 v5.0.1

Applications errors and warning messages are significant for understanding the application’s infrastructure and its underlying technologies. If you can get an application to communicate back to you during testing with viewable errors, you will understand the application’s behavior better.

This article will focus on exfiltrating data through XPATH functions in MySQL error-based injections. I’ll be going through the manual enumeration of an application, discovering the SQL vulnerability, and then using the Intruder feature in Burp Suite to automate the database dumping.

📔 $_Key_Concepts_To_Cover:

In-Band Error Based SQL Injection.
XPath functions in MySQL.
Identifying the SQL vulnerability with manual testing.
Use Burp Suite to speed up the process of the data exfiltration.

$_In_Band_Error_Based_SQL_Injection

In-band SQL Injection occurs when an attacker is able to use the same communication channel to both launch the attack and gather results. — “Acunetix.com”

$_XPath_functions

XPath functions are XML functions used in SQL injections to generate outputs in error messages crafted to reveal confidential data. Two functions were introduced in MySQL version 5.1 and later:

ExtractValue() : Extract a value from an XML string using XPath notation
UpdateXML(): Return replaced XML fragment

For this tutorial, we need the ExtractValue() function for the data exfiltration.

$_Exploitation_Time:

To demonstrate the vulnerability and its exploitation, I picked up a retired machine from Hack The Box called Cache to exploit its MySQL vulnerability use it to exfiltrate the information from the database.

That being said, Let’s jump into it….

When testing a web application, the first thing is to intercept a request with a proxy and analyze its content to identify input points and parameters to fuzz.

I always start from the beginning of the request, jotting down what looks interesting and make mental notes of possible attacks on the application.

In the below screenshot, I see that the application is a PHP application with two visible parameters auth and site included in the POST request.

hmm interesting 💭

💭 $_Possible _Attacks_:

1- Local File Inclusion

2- Remote File Inclusion [rare but still a possibility] !!

3- Code Injection such as SQL, XML, etc

4- Command Injection

5- Stored Cross-Site Scripting

✏️ Note: I won’t be analyzing the entire request as that would be out of this tutorial’s scope, trying to keep it short and concise 😃.

Back to fuzzing, I tried LFI and RFI payloads; both did not work and led me nowhere…

Next, I started testing for any SQL related vulnerabilities. I began the test by adding a comma, the most common SQL injection testing character, to check if the server will complain back in the response.

Added a comma to auth=login’. The response is the same. No change

However, when I added a comma after the parameter default

site=default.’

The server responded with a visible error. “Site ID ‘default” contains invalid characters.” that indicated the comma is an invalid character. This led me to believe that the application is vulnerable to In-band Error-based SQL Injection.

Now that we have identified, the application is vulnerable to SQL injection. I started going through my compiled list of commonly seen injections, checking if any would return more useful information. Unfortunately, none of them worked right off the bat.

Hmm, the next step was looking for SQL injection vulnerabilities for OpenEMR 2018 release. I found an analysis done by Project Insecurity researchers identifying all the application pages that are vulnerable to SQL injections.

Going through the report, It mentioned that the eid parameter in “add_edit_event_user.php” is unsanitized and vulnerable to Injections. To exploit it, we need to pass the PHP cookie on the registration page to the add_edit_event_user.php page to interact with the “eid” parameter.

Let’s roll our selves and do it !!

First, we intercept the register.php page and copy the PHP Session Cookie.

Then, we intercept the vulnerable page add_edit_event_user.php and add the parameter eid with the PHP cookie on the request to interact with it.

***Finding the SQL injection in add_eidt_event_user.php***

Great!!! We replicated the vulnerability locally; we will use the XPath function — Extractvalue() mentioned in the POC to generate the needed errors to extract the information.

Before diving into generating the errors, let’s take a step back and understand how the function works, then use it for our advantage. If you read the MySQL documentation[link], they talked about the right way of passing the XML data into the Extractvalue() function to retrieve the attributes that contain the XML data.

The function takes two arguments: attribute name (xml_frag) and XPath expression enclosed in single quotes (xpath_expr).

In the example below, the MySQL table is created and loaded in XML format. To retrieve the title information from the created table, we pass the attribute name doc and the XPath query ‘/book/title’ in a single quote.

The function will look up the table for any attribute for doc return all its related titles as requested:

A guide to the SQL standard
SQL:1999

The SQL example is taken from Professor Peter Wood at Birkbeck

Awesome, now we know how the function works. We will pass values to the ExtractValue function that can’t be interpreted into valid results like letters and numbers. Instead, we will be giving characters like comma or semicolon in a hex representation to ensure the XML parsing will always fail and generate error messages.

We will pass the semicolon character in hex 0x3b for (xml_frag) and MySQL function for XPath expression (xpath_expr)to trigger the errors.

I started requesting some basic information using MySQL functions of Version(), Database name and Current_User().

Then I noticed that the current user is not fully displayed, only part of it “@localhost.” Reading about it turns out that the XPath functions have character limitations up to 32 characters, and we need Concatenation and substring functions if we want to cycle through database information.

Started by adding Concat() function with the Current_User() and got the full name of the user “openemr@localhost”. GREAT !!!

Using Concat() function to get the full name of the user

Payloads to extract other information from the DB

Since the database is big and has multiple tables, we will use Burp Suite the Intruder feature to speed up extracting the information, so we can look for the users’ tables and get the stored usernames passwords.

$_Use_Intruder_In_Burp_Suite_(Automation)_:

Click on the Action button and choose to Send the request to Intruder.

2.In the Positions tab, Use the Clear button to clear the highlighted area between the symbols § §, and add them only around the number “0” after Limit. We are only interested in enumerating the tables from 0 to, let’s say, 500 in the OpenEMR database.

3. In the Payloads tab, Choose Numbers for the Payload Type, the Number range is sequential starting from 0 to 500, and Step is 1 for incrementing by 1.

Start the attack …

As you see below in the response tab. XPath errors displaying the names of the tables back to us 😃

XPATH syntax error: ‘;; addresses’ → first table

To view the the table names quickly without keep scrolling through the response tab hundred times checking the errors, there are two handy features in Burp can help with that:

Use the Auto-scroll feature

Paste XPATH error in the search bar, click on the gear icon next to it, and choose Auto-Scroll to match the text changes.

2. Use Grep-Extract option

Go to Options in Attack window under Grep-Extract option; click on Add.

Since we are interested in the content between the single quote ‘XPATH syntax error: ‘;; addresses’ the starting point will be XPATH syntax error: ‘

and the ending point will be a closing quote.’

If you go back to the Results tab, you will see a new column added for the extracted contents.

Going through the extracted results, we see that we have 4 interesting tables that might contain juicy information.

Tested each one of them, users_secure is the table that contains the user ids and passwords.

Repeated the same process with Intruder to enumerate all the columns in the users_secure table.

***Extracted columns from Users_Secure Table***

ID: 1
Username : Openemr_admin
Password: $2a$05$l2sTLIG6G

📝 When extracting the long password hash, I was hit with the XPath character limitation of ONLY 32 characters again. This time, I used the Substring function to grab all characters in the password.