How to safely retrieve sql data using a stored function

Question

I'm trying to write a function in MySQL that gets the password for a username given as parameter, while avoiding SQL injection.

Here's my code:

CREATE FUNCTION get_password_for_user (p_username varchar(30))
    returns varchar(32)
BEGIN
    declare passwd varchar(32) default "";

   PREPARE stmt FROM 'select password into @passwd from users where name=?';
   SET @name = p_username;
   EXECUTE stmt USING @name;
   DEALLOCATE PREPARE stmt;

   return passwd;
END $$

When I try to create it, it gives the Dynamic SQL error:

Error Code: 1336. Dynamic SQL is not allowed in stored function or trigger

I've found solutions involving CONCAT on the web, but that would mean being vulnerable to SQL injection and I obviously don't want that.

Is there something I can do about this? Or is there any other proper way to deal with this situation?

Answer 1

Why are you using dynamic sql?

CREATE FUNCTION get_password_for_user (p_username varchar(30))
    returns varchar(32)
BEGIN
    declare passwd varchar(32) default '';

   select passwd := u.password
   from users u
   where u.name = p_username;

   return passwd;
END $$