MySQL dynamic SQL table name and result

Question

i'm having a lot of troubles with my sql dinamic code:

DELIMITER $$

CREATE DEFINER=`remotecontroller`@`%` PROCEDURE `check_pwd`(IN pwd VARCHAR(20), 
IN user VARCHAR(20), IN tab VARCHAR(30), IN pwd_f varchar(30), IN id_f varchar(30), 
OUT result Bit(1))
    READS SQL DATA
BEGIN
 set @u_pwd= null;
 set @txt= concat("select pwd_f from ",tab," where id_f = upper(user) into @u_pwd");
 prepare exec from @txt;
 execute exec;
-- IN previous version the table and column names was preselected: select pwd_f from clinic.np where id_f like upper(user) into u_pwd;

     If Password(pwd) =  @u_pwd then set result= 1;
     else set result= 0;
     end if;
END

The goal of code is check one password with other one in table and return bool. Is for test project, i know that i need to change the password plugin, but i'll do in the final project

When i execute the code throws one error 1064, sql syntax error. Can anybody help me? I try a lot of things, thanks everybody!

EDIT - Now, with the same code throws error 1054: unknown column pwd_f in field_list;

Answer 1

This does not select a column named by the variable pwd_f, it selects the column whose name is literally "pwd_f".

set @txt= concat("select pwd_f from ",tab," where id_f = upper(user) into @u_pwd");

Likewise the column named by the variable id_f.

You probably want this:

set @txt= concat("select ",pwd_f," from ",tab," where ",id_f," = upper(user) into @u_pwd");

Note that your stored procedure is vulnerable to SQL injection. Make sure the code calling this stored procedure can only pass column names and table names from a finite list. You should use a whitelisting technique to do this.

---

Re your comment:

user isn't a column, is row value for where!

Okay then it's the same problem -- you're putting variables into your SQL string, but they won't be interpreted as variables that way. The proper way to include a value in a dynamic query is using a parameter.

set @txt= concat("select ",pwd_f," from ",tab," where ",id_f," = upper(?) into @u_pwd");
set @user= user;
prepare exec from @txt;
execute exec using @user;

The extra @user variable is to work around a weird restriction on prepare/execute in stored procedures: MySQL doesn't allow procedure variables to be query parameters, you must use session variables (the ones with @ prefix).

---

I'm glad you got it working. For what it's worth, I was also testing it so here's my test script (which works):

use test
drop table if exists np;
create table np (
    id int primary key,
    username varchar(50),
    passwd varchar(50)
);
insert into np values (1, upper('bill'), password('xyzzy'));

DROP PROCEDURE check_pwd;

DELIMITER $$

CREATE DEFINER=`root`@`%` PROCEDURE `check_pwd`(
    IN pwd VARCHAR(20),
    IN user VARCHAR(20),
    IN tab VARCHAR(30),
    IN pwd_f varchar(30),
    IN id_f varchar(30),
    OUT result Bit(1))
    READS SQL DATA
BEGIN
 set @u_pwd= null;
 set @txt= concat("select ",pwd_f," from ",tab," where ",id_f," = upper(?) into @u_pwd");
 set @user= user;
 prepare exec from @txt;
 execute exec using @user;
-- IN previous version the table and column names was preselected: select pwd_f from clinic.np where id_f like upper(user) into u_pwd;

     If Password(pwd) =  @u_pwd then
    set result= 1;
     else
    set result= 0;
     end if;
END $$

DELIMITER ;

CALL check_pwd('xyzzy','bill','np','passwd','username',@did_i_match);

SELECT @did_i_match;

Other tips:

• PASSWORD() is not a good function to use for application-level password hashing. They even say so in a note in the documentation.

  Also read You're Probably Storing Passwords Incorrectly

• MySQL's BIT data type has known bugs. Experienced MySQL experts recommend using TINYINT as a de facto boolean type.

• I'm not sure why you're using a stored procedure for this task anyway. MySQL stored procedures are pretty inefficient, and most developers would do what you're doing using application code. It would be easier to develop and debug, run faster, and easier to protect against SQL injection.

Answer 2

Perhaps this:

 set @txt= concat("select pwd_f from ",tab," where id_f = upper(user) into @u_pwd");
 prepare exec from @conc;

Should be more like:

 set @txt= concat("select pwd_f from ",tab," where id_f = upper(user) into @u_pwd");
 prepare exec from @txt;