Special characters in database passwords: percent-encoding connection URLs

The short answer

A connection URL puts the password between the first : and the @:

postgresql://app:<password>@db.example.com:5432/shop

If the password itself contains @, :, /, ?, # or %, the client can’t tell where it ends. Replace each such character with % and its two-digit hexadecimal code. That’s percent-encoding:

password:  p@ss:w/rd#100%
encoded:   p%40ss%3Aw%2Frd%23100%25
URL:       postgresql://app:p%40ss%3Aw%2Frd%23100%25@db.example.com:5432/shop

The client decodes it back before sending it to the server. The same applies to user names, and to postgres://, postgresql://, mysql://, mongodb:// and mongodb+srv:// URLs alike.

The safe rule: encode every character except letters, digits and - . _ ~. Some clients tolerate more, but none we tested minded the extra encoding.

The characters

These are the encodings, and what happened when we put each character unencoded in the middle of a password (ab<char>cd) with psql 18.6 (libpq), MySQL Shell 8.4.10 and mongosh 2.12.0. With the encoded form, every one of them connected on all three.

“Worked” only means that client, with the character in that position, happened to cope. Other drivers parse differently, so encode them anyway. MongoDB’s documentation, for example, lists $ among the characters to encode, though mongosh accepted it.

What goes wrong

There are three kinds of failure, and the third is the hard one to spot.

The URL doesn’t parse. With p@ss:w/rd#100% unencoded:

psql 'postgresql://seo_strings_pw:p@ss:w/rd#100%@localhost:54318/inlet'

psql: error: invalid percent-encoded token: "rd#100%@localhost:54318/inlet"

mysqlsh:  Invalid URI: Illegal character [w] found at position 28
mongosh:  MongoRuntimeError: Unable to parse ss:w with URL

The client connects somewhere else. libpq splits at the first @, so everything after it becomes the host. A password of ab@cd:

psql: error: could not translate host name "cd@localhost" to address: nodename nor servname provided, or not known

And with ab/cd, the / ended the host part early: invalid integer value "ab" for connection option "port".

The client sends a different password. A % followed by two hex digits is a valid encoding of some other character. The password Spring%2024 in a URL is decoded to Spring 24 (%20 is a space), and the server rejects it:

psql: error: connection to server at "localhost" (::1), port 54318 failed: FATAL:  password authentication failed for user "seo_strings_pw"

MySQL Shell said MySQL Error 1045 (28000): Access denied for user 'seo_strings_pw'@'127.0.0.1' (using password: YES) and mongosh MongoServerError: Authentication failed. Nothing in those messages points at the URL, so the password looks wrong when it isn’t. Encoded as Spring%252024, all three connected.

The encoded URL works

The same role, user and password on each server, this time encoded:

psql 'postgresql://seo_strings_pw:p%40ss%3Aw%2Frd%23100%25@localhost:54318/inlet' -c 'select current_user'

  current_user
----------------
 seo_strings_pw
(1 row)

mysqlsh --sql 'mysql://seo_strings_pw:p%40ss%3Aw%2Frd%23100%25@127.0.0.1:3306' -e 'select current_user()'

current_user()
seo_strings_pw@%

mongosh 'mongodb://seo_strings_pw:p%40ss%3Aw%2Frd%23100%25@localhost:27017/seo_strings' \
  --eval 'db.runCommand({connectionStatus: 1}).authInfo.authenticatedUsers'

[ { user: 'seo_strings_pw', db: 'seo_strings' } ]

User names

The same rules apply to the user name, where : matters even more: it’s what separates the user from the password. Some services use user names with @ in them (for example app@servername on older Azure PostgreSQL servers, or an email address). For a role called seo_strings_app@acme:

postgresql://seo_strings_app@acme:<password>@localhost:54318/inlet
psql: error: invalid integer value "<password>@localhost:54318" for connection option "port"

postgresql://seo_strings_app%40acme:<password>@localhost:54318/inlet
→ connected as seo_strings_app@acme

Encode it safely in a shell

Python’s urllib.parse.quote with safe='' encodes everything except letters, digits and -._~:

python3 -c "import urllib.parse,sys; print(urllib.parse.quote(sys.argv[1], safe=''))" '<password>'

$ python3 -c "import urllib.parse,sys; print(urllib.parse.quote(sys.argv[1], safe=''))" 'p@ss:w/rd#100%'
p%40ss%3Aw%2Frd%23100%25

Single quotes stop the shell from interpreting $, ! and the rest. A password that contains a single quote needs it written as '\''.

The password still lands in your shell history and, briefly, in the process list. To avoid both, read it without echoing and pipe it in:

read -rs PW            # type the password, press Return; nothing is shown
printf %s "$PW" | python3 -c "import sys,urllib.parse; print(urllib.parse.quote(sys.stdin.read(), safe=''))"
unset PW

Other ways to get the same result:

jq -rn --arg v '<password>' '$v|@uri'
node -e 'console.log(encodeURIComponent(process.argv[1]))' '<password>'

jq encodes the same characters as Python. JavaScript’s encodeURIComponent leaves ! * ' ( ) as they are, which all three clients accepted. To check an encoded string, decode it:

python3 -c "import urllib.parse,sys; print(urllib.parse.unquote(sys.argv[1]))" 'p%40ss%3Aw%2Frd%23100%25'

p@ss:w/rd#100%

Don’t use form encoding. Python’s quote_plus and JavaScript’s URLSearchParams turn a space into +. Connection URLs don’t: libpq read a+b as a+b. Use %20.

Or keep the password out of the URL

Every client can take the password separately, with no encoding at all:

• PostgreSQL: ~/.pgpass, the PGPASSWORD variable, or the key/value form, password='p@ss:w/rd#100%', which uses quotes instead of percent-encoding. All three connected with the raw password. See PostgreSQL connection strings.
• MySQL: mysql -p (it asks), an option file such as ~/.my.cnf, or mysql_config_editor. See MySQL connection strings.
• MongoDB: mongosh '<url without credentials>' -u <user> -p (it asks); -p '<password>' also worked with the raw password.

Some formats look like URLs but aren’t, and must not be encoded: libpq’s key/value strings, MySQL option files, and Go’s MySQL DSN (user:password@tcp(host:3306)/db), whose documentation says the password needs no escaping.

SQLite

SQLite has no user names or passwords. The same encoding rules apply to file names inside file: URIs, where ?, # and % must be encoded; see SQLite connection strings.

In Inlet

Paste a URL with an encoded password and Inlet fills in the connection form. Or type the password into the form as it is: it has its own field, so there’s nothing to encode, and Inlet keeps it in the Keychain. If the server rejects it, the connection window asks for it again.