1

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.

8
  • 2
    User defined variables are per session, but deprecated in MySQL-8.0. Store procedures can have local variables which sounds like what you should be using. Is unclear what value a stored procedure is giving you over an application language implementation. Welcome to DBA stack exchange.
    – danblack
    Jun 18 at 5:51
  • @danblack Thanks! And yes I've worked with local variables but as you said I should have been working with those variables for all procedures. I will change the code for avoiding future problems. Jun 18 at 6:08
  • 1
    can exist a collision of values in this case? Absolutely impossible.
    – Akina
    Jun 18 at 12:17
  • 1
    So the @user_defined_variables will always exist. User-defined variable exists always by design. Even when it was not accessed/assigned previously. Until the most first assignment the value of UDV is NULL.
    – Akina
    Jun 18 at 12:19
  • 2
    Errata, it was "Support for setting user variables in statements other than SET was deprecated in MySQL 8.0.13", ref, not the entire user variables. my appoligies.
    – danblack
    Jun 18 at 12:38

1 Answer 1

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.

6
  • Thank you! I'm understanding more about this and as you said I'm better changing the user defined variables for local variables. Jun 18 at 18:25
  • About the sessions I guess I understand and as I said I only have one connection from Node to MySQL (I don't close it never) so all those values of user-defined variables will stay there for that connection / session. And using those variables with a select into I realized is dangerous, because if the query in select into returns null, the variable will use the last value instead of the returned and here was where I had the security problems. Jun 18 at 18:30
  • 1
    If the query returns null, it will assign null to the variable. If the query has no result, that's different from returning null, and you're right, SELECT INTO will not change the variable, whereas SET will change it. Jun 18 at 18:35
  • Oh, so I was wrong with the concept of SELECT INTO too. Thanks for your time! Jun 18 at 18:41
  • 1
    You can test it this way: SELECT 123 INTO @foo WHERE true versus SELECT 123 INTO @foo WHERE false. Jun 18 at 18:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.