- 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
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
I would say, avoid using PASSWORD()s beyond the limited and intended use. My personal opinion is that it should be deprecated to avoid confusion.
ReplyDeleteOnly few shipper loan banks don't require the dealer to change their charge card processors organization. Most time this won't be an issue at all since the rates will be coordinated. Cash Advances Chicago
ReplyDeletenow present in your city cara menggugurkan kandungan
ReplyDelete