Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
Avoid clear text passwords in MySQL logging.
+3 Vote Up -0 Vote Down
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


Votes:

You must be logged in with a MySQL account to vote on Planet MySQL entries. More information on PlanetMySQL voting.

Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.