In a comment on my post about Using SSL with MySQL xiaochong zhang asked if
it is possible to decode SSL/TLS encrypted MySQL traffic. The
short answer is: It depends.
To test this we need a MySQL server which is SSL enabled. I used
MySQL Sandbox to create a sandboxed 5.6.19 server. Then I used
mysslgen to create the config and the
certificates.
$ make_sandbox 5.6.19
$ ./mysslgen.py --config=sandboxes/msb_5_6_19/my.sandbox.cnf --ssldir=sandboxes/msb_5_6_19/ssl
This assumes there already is a extracted tarball of MySQL 5.6.19
in ~/mysql/5.6.19
The mysslgen.py script will return a message with the changes you
should make in your mysqld and client sections of the
my.sandbox.cnf file. Then restart the server to make it
active.
For SSL to work we need to connect using TCP/IP instead of over a
UNIX socket. So we connect with "./my sql -h 127.0.0.1". Now
execute "\s" or "status" to see if we're indeed using SSL.
It probably looks like this:
mysql [127.0.0.1] {msandbox} ((none)) > \s
--------------
/home/dveeden/opt/mysql/5.6.19/bin/mysql Ver 14.14 Distrib 5.6.19, for linux-glibc2.5 (x86_64) using EditLine wrapper
Connection id: 3
Current database:
Current user: msandbox@localhost
SSL: Cipher in use is DHE-RSA-AES256-SHA
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.6.19 MySQL Community Server (GPL)
Protocol version: 10
Connection: 127.0.0.1 via TCP/IP
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
TCP port: 5619
Uptime: 1 hour 32 min 48 sec
Threads: 1 Questions: 18 Slow queries: 0 Opens: 67 Flush tables: 1 Open tables: 60 Queries per second avg: 0.003
--------------
Now disconnect and start the trace.
sudo tcpdump -i lo -s 65535 port 5619 -w /tmp/mysql.pcap
First connect w/o SSL: "./my sql -h 127.0.0.1 --skip-ssl". And
then with SSL: "./my sql -h 127.0.0.1"
Stop the tcpdump session and start wireshark and open the
mysql.pcap file. Now we can inspect the protocol. If MySQL is
using the default port (3306) then wireshark will automatically
decode the traffic, but now we have to use 'Decode as...' to tell
wireshark this is MySQL traffic. The server greeting packet and
the login request should now be visible. In the login request
there are client capability flags, one of the flags indicates
'Switch to SSL after handshake' and should be set for the SSL
session.
Both SSL and non-SSL sessions will use the same port and start an
unencrypted session. The encrypted session will switch to SSL
after the handshake. This is a bit like STARTTLS for IMAP. The
current version of the MySQL protocol dissector is not (yet)
aware of some of the new information in the initial handshake. So
the information for the authentication plugins and connection
attributes is not decoded yet. The documentation about the protocol can be found
in the MySQL Internals manual.
So that's the plaintext part. Now we get to the SSL part. In my
setup the default cipher suite which is used for SSL is
DHE-RSA-AES256-SHA. With OpenSSL's ciphers command we can get
some more details:
$ openssl ciphers -v 'DHE-RSA-AES256-SHA'
DHE-RSA-AES256-SHA SSLv3 Kx=DH Au=RSA Enc=AES(256) Mac=SHA1
This means that SHA 1 is use for the MAC part and that AES-256 is
used for encryption and the keyexchange is done with DH (Diffie-Hellman). This poses a problem as DH
will generate a session key, and we don't have that in the
traffic dump as it's not sent over the network. We could use gdb
(and maybe a debug trace?) to get the DH keys out, but for now we
have an easier solution: use a different cipher suite.
So start tcpdump again and run "./my sql -h 127.0.0.1
--ssl-cipher=AES256-SHA". This cipher uses RSA for keyexchange
instead of DH. This means everything we need is send over the
network or is present in the SSL certificate and/or key.
Now start wireshark again and use 'Decode as...' and choose SSL.
Then go Edit→Preferences→Protocols→SSL→'RSA key list' and add the
server's SSL key. Now you should see the decoded traffic.
So decoding SSL/TLS encrypted MySQL traffic is possible. But you
need to have:
- All traffic since the beginning of the TCP/IP connection
- The server's SSL key
- The DH session key if DH is used. (you might want to read about Forward secrecy (PFS) if you're interested in the details).