Vieing other's session variables or injecting admin code

PART 1: Vieing other's session variables

Perhaps we want to see details of context for some existing MySQL session. In particular session variables or session status details, or even want to change sonfiguration of others session. This may be the most useful for replication session or any other long running job.

Official way is that such feature is not supported, but there situations when we still able to make it.

The easiest case is one when we know that the session updates some table, and this table has some BEFORE/AFTER trigger slot empty. The trick is to create trigger and check connection_id() inside it. Example below demonstrates idea, (invovling stored procedure):


delimiter ;;

create procedure export_session_vars(connid int)
begin
    declare exit handler for SQLEXCEPTION begin end;
    if CONNECTION_ID() = connid and @hack_export_happened is null then
        select * from information_schema.session_variables order by 1 into outfile '/tmp/output.txt';
        set @hack_export_happened = 1;
    end if;
end;;


create trigger hack after insert on t for each row call export_session_vars(2);


Voila, when session with SESSION_ID=2 inserts some row in table `t`, we have magically created file '/tmp/output.txt' with session variables values. We can also insert results into some table instead of file if we wish. Now we can drop trigger to avoid useless performance penalty from it.


Note 1: Creating such triggers may work but also may break your system completely. I cannot make any guarantee, so do it only if you are sure what you do and only on your own risk.

Note 2: In 5.0 we don't have session_variables table in information_schema, so we should use SELECT with implicit variables values, e.g.:
SELECT "auto_increment_increment" as variable_name, @auto_increment_increment as variable_value
UNION
SELECT "auto_increment_offset" as variable_name, @auto_increment_offset as variable_value
UNION
...


In case if we don't know involved tables or if session does only reads - I don't know any solution.
If we would be able to create similar trigger on slow_log table, that would solve problem. But triggers on system tables are not allowed. (Theoretically we even could ALTER mysql.slow_log to be FEDERATED to test.slow_log and create such trigger on test.slow_log, but it will not help anyway).



PART 2: Injecting Admin Code.

So considerations above lead to possible useful feature in future of MySQL. (Not sure if similar feature exists in other RDBMS).

Perhaps we have table mysql.injection similar to mysql.event, but with two additional columns `CONNECTION_ID` and `RESULT`.

Now each session, before handling next query, checks table mysql.injection and executes commands with matching CONNECTION_ID, (e.g. if `RESULT` column is NULL).

Note 1: Introducing additional (automatic) column `READONLY` may be necessary: 'READONLY' injections may happen on next query execution, while others may happen only if no tables are locked and after transaction is completed.
Note 2: Details may differ, but I hope general idea is clear and shouldn't be difficult to implement.

This tool will be really powerful, and shouldn't give any performance penalty if implemented properly (e.g. dedicated thread may read new entries, parse and place them into memory, so other sessions will not bother to actually select from table).

Conclusion: First part of this post brings idea about using triggers to inject code into other sessions. Second part brings an idea of implementing general Injection of admin code in RDBMS.