Is it safe to use user defined variables inside stored procedures in MySQL?

Question

Currently I'm working with Stored Procedures in MySQL and I'm using in some procedures user-defined variables and I've seen that type of variables are initialized in the current session and keep their value until the session ends.

I was also working with statements like select into @user_defined_variable but I realized that doing that is very risky, specially on logins/authentications. So the solution in this case was to use the statement set @user_defined_variable instead of select into.

But I'm really not sure if it's enough using the set, because that type of variables will keep their value while the session is not finished.

Now imagine that the server receives several requests at same time on the Stored Procedures that are using the same @user_defined_variable, can exist a collision of values in this case? For example, if the stored procedure called login uses the user defined variables @uuidUser and the stored procedure called home also uses the @uuidUser, does exist the risk that the home procedure uses the value of @uuidUser assigned inside the login procedure?

Note: I'm working with Node.js and I only have one connection to the MySQL instance,I don't create a connection for every request. So the @user_defined_variables will always exist.

Answer 1

User-defined variables are specific to the session where they were set. They don't leak over to other sessions.

Each session has its own variables. A variable of the same name in a different session does not hold the same value. Just like local variables in any programming language.

Even if you use a connection pool so a given connection is reused by a subsequent request, there is a reset of the session when the connection is recycled. So all session-specific state is discarded. That includes session variables (both system variables and user-defined variables), temp tables, transactions, the session character set, session counters, etc. It would be very bad for security if any of those things leaked over to a subsequent request that reused a connection from the connection pool.

There is no difference with respect to scope or security between SELECT ... INTO @user_defined_variable versus SET @user_defined_variable = .... Both assign a value to that user-defined variable. The variable holds its value for the duration of the session, or until it is assigned a different value. At the end of the session, the variable is discarded.

I would also suggest using local variables in your stored procedures or triggers if you need to. You must use DECLARE at the top of the stored routine to create such a variable. The scope of the local variable is only the routine where it was created. After that routine returns, the local variables are discarded.