| Bug #104121 | PSI_THREAD_INFO not updated while executing PREPARED STATEMENTS. | ||
|---|---|---|---|
| Submitted: | 26 Jun 2021 15:19 | Modified: | 24 May 2024 13:12 |
| Reporter: | Pranay Motupalli | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Performance Schema | Severity: | S3 (Non-critical) |
| Version: | >=5.7.28 , >=8.0.18, 8.0.25, 5.7.34 | OS: | Any |
| Assigned to: | Marc ALFF | CPU Architecture: | Any |
[26 Jun 2021 15:19]
Pranay Motupalli
[28 Jun 2021 14:46]
Pranay Motupalli
root@ip-172-31-50-102:~/mysql-server# git diff @{upstream}
diff --git a/sql/sql_class.cc b/sql/sql_class.cc
index b8f43573b21..5c0dc2ffb01 100644
--- a/sql/sql_class.cc
+++ b/sql/sql_class.cc
@@ -4369,6 +4369,7 @@ void THD::set_query(const LEX_CSTRING& query_arg)
mysql_mutex_lock(&LOCK_thd_query);
m_query_string= query_arg;
mysql_mutex_unlock(&LOCK_thd_query);
+ current_thd->set_query_for_display(query_arg.str, query_arg.length);
}
[29 Jun 2021 5:30]
MySQL Verification Team
Hello Pranay, Thank you for the report and feedback. Verified as described. regards, Umesh
[29 Jun 2021 6:21]
MySQL Verification Team
MySQL Server 8.0.25, 5.7.34 test results
Attachment: 104121.results (application/octet-stream, text), 16.37 KiB.
[29 Jun 2021 6:27]
MySQL Verification Team
Hi Pranay, Please note that in order to submit contributions you must first sign the Oracle Contribution Agreement (OCA). More details are described in "Contributions" tab. Also, once you sign OCA ensure to re-send the patch via "Contributions" tab. Otherwise we would not be able to accept it. regards, Umesh
[30 Jun 2021 1:04]
Pranay Motupalli
I hereby submit the above one-lined under public domain for anybody to use freely. I will not sign the OCA.
[17 Aug 2021 17:29]
Pranay Motupalli
Hi Team, Can you please provide the status of this bug fix. Thanks
[2 May 2023 22:28]
Daniel Lenski
> This is a regression caused due to commit https://github.com/mysql/mysql-server/commit/afcaac4d6f8aecc70c418138f1d8dd4e4965e629 We can be *more precise* about what caused this regression. It was, very specifically, the removal of the following 3 lines from sql/sql_class.cc https://github.com/mysql/mysql-server/commit/afcaac4d6f8aecc70c418138f1d8dd4e4965e629#diff... diff --git a/sql/sql_class.cc b/sql/sql_class.cc index 49ac2015b3ba..91cfb7ca73fc 100644 --- a/sql/sql_class.cc +++ b/sql/sql_class.cc @@ -4354,10 +4354,6 @@ void THD::set_query(const LEX_CSTRING& query_arg) mysql_mutex_lock(&LOCK_thd_query); m_query_string= query_arg; mysql_mutex_unlock(&LOCK_thd_query); - -#ifdef HAVE_PSI_THREAD_INTERFACE - PSI_THREAD_CALL(set_thread_info)(query_arg.str, query_arg.length); -#endif } The one-line patch provided in https://bugs.mysql.com/bug.php?id=104121#c513825 has the unwanted side effect of overwriting `performance_schema.events_statements_history.SQL_TEXT` and causing it to appear as `NULL`. The safest fix is simply to re-add the removed lines (above), since those caused the regression.
[4 May 2023 18:27]
Daniel Lenski
My colleague has now submitted a pull request to resolve this bug. (The PR description includes manual demonstrating that the PR does not reintroduce unobfuscated passwords into the query text in PERFORMANCE_SCHEMA.THREADS table.) https://github.com/mysql/mysql-server/pull/462
[27 Jun 2023 18:30]
Daniel Lenski
Adding a new comment here to try to debug why Oracle team cannot see my account.
[14 Feb 2024 14:11]
OCA Admin
Contribution submitted via Github - Bug#104121: Ensure that PREPAREd statements are shown correctly in PERFORMANCE_S (*) Contribution by Daniel Lenski (Github dlenski, mysql-server/pull/519#issuecomment-1942861612): This contribution is under the OCA signed by Amazon and covering submissions to the MySQL project.
Contribution: git_patch_1724489440.txt (text/plain), 3.91 KiB.
[20 Mar 2024 14:34]
Marc ALFF
Thanks for submitting this contribution on Feb 14, 2014, under the terms of the OCA. Our technical analysis of the contribution is as follows. 1) The fix for: Bug#20712046: SHOW PROCESSLIST AND PERFORMANCE_SCHEMA TABLES DO NOT MASK PASSWORD FROM QUERY indeed caused the regression reported in: Bug#104121 PSI_THREAD_INFO not updated while executing PREPARED STATEMENTS The analysis is correct here. 2) The proposed contribution: From 9b03ce5519273efdcecc2ef0a484f59e6997be7d Mon Sep 17 00:00:00 2001 From: Daniel Lenski <dlenski@amazon.com> Date: Tue, 13 Feb 2024 11:17:52 -0800 Subject: [PATCH] Bug#104121: Ensure that PREPAREd statements are shown correctly in PERFORMANCE_SCHEMA.THREADS table claims that: We verified that this change *does not* undo the intended effect of the original commit. Passwords (still) do not leak into the `PERFORMANCE_SCHEMA.THREADS` table with this change: Our assessment of the patch is different. Applying the proposed patch on top of the current MySQL code, and applying the extra following change: --- begin diff malff@malff-desktop GIT_BUG33057164_AMAZON]$ git diff storage/perfschema/pfs.cc diff --git a/storage/perfschema/pfs.cc b/storage/perfschema/pfs.cc index 5a0bde3cd54..d0f834f19dc 100644 --- a/storage/perfschema/pfs.cc +++ b/storage/perfschema/pfs.cc @@ -3389,6 +3389,10 @@ void pfs_set_thread_info_vc(const char *info, uint info_len) { pfs_dirty_state dirty_state; PFS_thread *pfs = my_thread_get_THR_PFS(); + if (info != nullptr) { + fprintf(stderr, "THREAD.PROCESSLIST_INFO = %.*s\n", info_len, info); + } + if (likely(pfs != nullptr)) { if (info_len > sizeof(pfs->m_processlist_info)) { info_len = sizeof(pfs->m_processlist_info); --- end diff shows that, after issuing the following query: CREATE USER alice IDENTIFIED BY 'thisisapassword'; the stderr log contains: THREAD.PROCESSLIST_INFO = CREATE USER alice IDENTIFIED BY 'thisisapassword' THREAD.PROCESSLIST_INFO = CREATE USER 'alice'@'%' IDENTIFIED BY <secret> In other words, the full query, including sensitive data like the password in cleartext, is printed in table performance_schema.threads when this patch is applied. Actually observing the sensitive data might be difficult in practice, because the PROCESSLIST_INFO column is soon overwritten with the sanitized version (as seen in the second line), but the fact remains that sensitive data is leaked. The proposed patch contains a security vulnerability, and can not be accepted as is. This issue: Bug#104121 PSI_THREAD_INFO not updated while executing PREPARED STATEMENTS is still unresolved at this point.
[20 Mar 2024 14:37]
Marc ALFF
Correction: Thanks for submitting this contribution on Feb 14, 2024.
[24 May 2024 13:12]
Edward Gilmore
Posted by developer:
Added the following note to the MySQL Server 8.0.38, 8.4.1, and 9.0.0 release notes:
The PROCESSLIST_INFO column of THREADS was not updated when executing a prepared statement.
Thank you for the bug report.
