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?
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 $$
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments