Pass custom variables in MySQL connection

Question

I am setting up a MySQL connection (in my case PDO but it shouldn't matter) in a REST API.

The REST API uses an internal authentication (username / password). There are multiple user groups accessing the REST API, e.g. customers, IT, backend, customer service. They all use the same MySQL connection in the end because they also use the same end points most of the time.

In the MySQL database I would like to save the user who is responsible for a change in a data set.

I would like to implement this on the MySQL layer through a trigger. So, I have to pass the user information from the REST API to this trigger somehow. There are some MySQL calls like CURRENT_USER() or status that allow to query for meta-information. My idea was to somehow pass additional information in the connection string to MySQL, so that I don't have to use different database users but I am still able to retrieve this information from within the trigger.

I have done some research and don't think it is possible, but since it would facilitate my task a lot, I still wanted to ask on SO if someone did know a solution for my problem.

Way to broad a question and not enough information for use to be of any use other than to make a guess — RiggsFolly, Dec 01 '16 at 10:42
Even though both concepts share the same name ("user") your application users are by no means related to MySQL users. They are entirely independent entities with nothing in common. MySQL doesn't know anything about your users. If you are using PHP you could use the `PDO::MYSQL_ATTR_INIT_COMMAND` connection option to launch a custom command on connect and e.g. run a `insert into connection_log ...` query (beware of performance though). — Álvaro González, Dec 01 '16 at 10:48
@RiggsFolly Don't understand why you think it's too broad. It's a very specific question. I want to pass additional information (username from the API) to the MySQL connection in order to read out that username in the MySQL trigger. — Horen, Dec 01 '16 at 10:51
@Horen Try this - When a user is connected to a database. Call a stored procedure that will create a temporary table with user information. Temporary table's scope is valid withing the connection. If your connection is not broken directly, you could use the temporary table information to get user's info. Now, when a user manually logs out, drop the table, even if he won't with lost connection, table will also be dropped. Sounds to solve your problem? — Rehban Khatri, Dec 04 '16 at 07:35
But a user could create the same table with fake data by sql injection... — SmokeMachine, Apr 15 '19 at 22:07

score 3 · Accepted Answer · answered Dec 04 '16 at 08:48

I would set a session variable on connect.

Thanks to the comment from @Álvaro González for reminding me about running a command on PDO init.

The suggestion of adding data to a temp table isn't necessary. It's just as good to set one or more session variables, assuming you just need a few scalars.

$pdo = new PDO($dsn, $user, $password, [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::MYSQL_ATTR_INIT_COMMAND => "SET @myvar = 'myvalue', @myothervar = 'othervalue'"
]);

It's also possible to set session variables at any time after connect, with a call to $pdo->exec().

$pdo->exec("SET @thirdvar = 1234");

You can read session variables in your SQL queries:

$stmt = $pdo->query("SELECT @myvar, @myothervar");
foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
    print_r($row);
}

You can also read session variables in triggers:

CREATE TRIGGER mytrig BEFORE INSERT ON mytable
FOR EACH ROW
    SET NEW.somecolumn = @myvar;

That's a very good idea. I'll try to implement this tomorrow :) — Horen, Dec 04 '16 at 18:43
But another user could override its variable by sql injection... is there a way to make it read only? — SmokeMachine, Apr 15 '19 at 22:11
No, there's no way to make session variables read-only. If you have SQL injection vulnerabilities, that's a different problem. One should make code changes to avoid those vulnerabilities. — Bill Karwin, Apr 15 '19 at 22:43

Pass custom variables in MySQL connection

1 Answers1