Avoid clear text passwords in MySQL logging.

What happens when you use the PASSWORD() function to insert a password hash into a table?

• The hash will be written to the table
• The password might be written in clear text to the binlog
• The password might be written in clear text to the general log
• The password might be written in clear text to the slow query log

The query

mysql [localhost] {msandbox} (test) > INSERT INTO testpwd(pwd) VALUES(PASSWORD('secret_password'));
Query OK, 1 row affected (0.00 sec)

The General log

130128 16:04:41     1 Query     INSERT INTO testpwd(pwd) VALUES(PASSWORD('secret_password'))

The Slow query log

# Time: 130128 16:04:41
# User@Host: msandbox[msandbox] @ localhost []
# Query_time: 0.004887  Lock_time: 0.001043 Rows_sent: 0  Rows_examined: 0
SET timestamp=1359385481;
INSERT INTO testpwd(pwd) VALUES(PASSWORD('secret_password'));

The binlog:

# at 219
#130128 16:04:41 server id 1  end_log_pos 287  Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1359385481/*!*/;
BEGIN
/*!*/;
# at 287
#130128 16:04:41 server id 1  end_log_pos 315  Intvar
SET INSERT_ID=1/*!*/;
# at 315
#130128 16:04:41 server id 1  end_log_pos 438  Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1359385481/*!*/;
INSERT INTO testpwd(pwd) VALUES(PASSWORD('secret_password'))
/*!*/;
# at 438
#130128 16:04:41 server id 1  end_log_pos 465  Xid = 8
COMMIT/*!*/;

It's possible to do it in a more secure way by using variables.

The query

mysql [localhost] {msandbox} (test) > SET @pwd := PASSWORD('another_secret_password');
Query OK, 0 rows affected (0.00 sec)

mysql [localhost] {msandbox} (test) > INSERT INTO testpwd(pwd) VALUES(@pwd);
Query OK, 1 row affected (0.01 sec)

The General log

130128 16:05:18     1 Query     SET @pwd := PASSWORD('another_secret_password')
130128 16:05:30     1 Query     INSERT INTO testpwd(pwd) VALUES(@pwd)

The Slow query log

# Time: 130128 16:05:18
# User@Host: msandbox[msandbox] @ localhost []
# Query_time: 0.000251  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1359385518;
SET @pwd := PASSWORD('another_secret_password');
# Time: 130128 16:05:30
# User@Host: msandbox[msandbox] @ localhost []
# Query_time: 0.003031  Lock_time: 0.000288 Rows_sent: 0  Rows_examined: 0
SET timestamp=1359385530;
INSERT INTO testpwd(pwd) VALUES(@pwd);

The binlog

# at 465
#130128 16:05:30 server id 1  end_log_pos 533  Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1359385530/*!*/;
BEGIN
/*!*/;
# at 533
#130128 16:05:30 server id 1  end_log_pos 561  Intvar
SET INSERT_ID=2/*!*/;
# at 561
#130128 16:05:30 server id 1  end_log_pos 638  User_var
SET @`pwd`:=_utf8 0x2A41424645453834453346463233423442454338323635383832433244383141414536363744363235 COLLATE `utf8_general_ci`/*!*/;
# at 638
#130128 16:05:30 server id 1  end_log_pos 738  Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1359385530/*!*/;
INSERT INTO testpwd(pwd) VALUES(@pwd)
/*!*/;
# at 738
#130128 16:05:30 server id 1  end_log_pos 765  Xid = 10
COMMIT/*!*/;

An other trick is to set binlog_format to ROW:

# at 555
#130128 16:30:47 server id 1  end_log_pos 623   Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1359387047/*!*/;
BEGIN
/*!*/;
# at 623
# at 672
#130128 16:30:47 server id 1  end_log_pos 672   Table_map: `test`.`testpwd` mapped to number 33
#130128 16:30:47 server id 1  end_log_pos 752   Write_rows: table id 33 flags: STMT_END_F

BINLOG '
p5kGURMBAAAAMQAAAKACAAAAACEAAAAAAAEABHRlc3QAB3Rlc3Rwd2QAAggPAv8AAg==
p5kGURcBAAAAUAAAAPACAAAAACEAAAAAAAEAAv/8BQAAAAAAAAApKkNFNDdGODRBOThDOTI0ODdC
RjI5MUM1QzIyNTY3ODg0RjAxMjdGM0U=
'/*!*/;
# at 752
#130128 16:30:47 server id 1  end_log_pos 779   Xid = 19
COMMIT/*!*/;

And even with the "-v" option for mysqlbinlog the passwords won't show with row based binlogs:

### INSERT INTO `test`.`testpwd`
### SET
###   @1=5
###   @2='*CE47F84A98C92487BF291C5C22567884F0127F3E'

With 5.6 hiding passwords should get done automatically and for all the log types. Unfortunately I didn't get it to work yet. (Bug #68200)

To prevent logging of passwords you need to

• Disable the slow query log
• Disable the general log
• Use variables or disable the binlog
• Or wait on the 5.6 GA version