OpenEMR: a sort field became SQL syntax

OpenEMR is open-source software for medical records. Its Patient REST API lets a program ask for patient lists over the web and choose their order. The request has a _sort field that holds column names separated by commas, and a leading minus sign means descending. For example, _sort=-date asks for the latest date first.

The server turns that field into SQL, the language that databases read. A sort order is an ORDER BY clause at the end of a query. So the example above becomes ORDER BY date DESC.

# SearchQueryConfig.php
create_config(params):
    config = SearchQueryConfig()
    sort = params["_sort"]
    if sort is empty:
        return config
    for field in split(sort, ","):
        if field starts with "-":
            config.order(field[1:], "DESC")
        else:
            config.order(field, "ASC")
    return config

# SearchConfigClauseBuilder.php
build_sort_clause(config):
    if config.orders is empty:
        return ""
    clauses = []
    for order in config.orders:
        # order.field goes in as SQL text.
        text = order.field + " " + order.dir
        clauses.add(text)
    return "ORDER BY " + join(clauses, ", ")

In the pseudocode, create_config splits the field at commas and reads each minus sign. build_sort_clause then joins the names into SQL text, and that text goes at the end of the query. Neither function asks if a name is a real column.

The parsing can look like validation, because it produces a neat list. But it only finds the separators. It says nothing about what each piece of text is.

The usual protection for SQL does not fit here. Safe code keeps values out of the query text with placeholders, as in WHERE id = ?, and sends each value separately. The database then cannot read the value as SQL. A placeholder can only hold a value, and a column name is part of the query's grammar. So a column name has to enter the query text, and it needs its own check.

Without that check, the database reads whatever the field contains as more of the query. This is SQL injection, where input meant as data gets read as part of a query. It can happen even when the rest of the query handles its values safely. The advisory lists exposure of patient data and credentials as the result.

OpenEMR gives this flaw 9.9, and not 10, because the caller needs a valid API login. The CVE record shows 10.0 for the same low-privilege vector, which is inconsistent, so this page uses the advisory.

AISLE reports AI-assisted discovery. OpenEMR credits its researchers and lists version 8.0.0 as fixed.