mysql | function multiple returns

Question

I am using mysql function for login authentication

DELIMITER ||
CREATE FUNCTION CheckLogin (username VARCHAR(10), _password VARCHAR(20))
    RETURNS BOOL
    NOT DETERMINISTIC
    READS SQL DATA
BEGIN
    RETURN EXISTS (SELECT ID FROM `users` WHERE name = _username AND passwd = _password);
END;
||
DELIMITER ;

This is my Java code:

try {
    cStmt = (CallableStatement) sql.connection.prepareCall("{? = call CheckLogin(?, ?)}");
        cStmt.registerOutParameter(1,Types.BOOLEAN);
        cStmt.setString(2, name);
        cStmt.setString(3, passwd);
        cStmt.execute();
         outputValue = cStmt.getBoolean(1);
    } catch (SQLException e1) {
        e1.printStackTrace();

I have no idea how to return the result of SELECT ID I want to get the ID.

because if exist the username with pass than returns True else false — Norbert, Aug 20 '15 at 11:20
Because at last you said "I have no idea how to return the result of "SELECT ID" i want to get the ID". — Serhat Akay, Aug 20 '15 at 11:27
Yes, the authentication is OK, but I want to display the Select result too. I have no idea how to return the bool and the Int too, the bool is that the username and passed EXISTS and the INT is the ID of the select result — Norbert, Aug 20 '15 at 11:30
***@Norbert***: Changes suggested are as `RETURNS INTEGER` and declare an int variable. `select id into variable from table ... ` and lastly return variable. — Ravinder Reddy, Aug 20 '15 at 11:31
Careful, please: the method of password validation you are using is dangerously insecure. If you use this on the public net, bad actors *will* pwn your site and steal your users' information. Do some research on "salted hashed passwords." http://stackoverflow.com/questions/27843642/store-and-validate-hashed-password-with-salt — O. Jones, Aug 20 '15 at 11:35

score 1 · Answer 1 · answered Aug 20 '15 at 11:30

Since your function only returns a boolean value there is no way for java to know about the ID as that is internal to the mysql function. Rather than returning a boolean you could return the Id in the mysql function and in java it could check if its null where null means login fails otherwise login is successful and you have the id. Alternatively you could return both the boolean and id as a CSV varchar and then in java split it to extract the boolean and id.

mysql | function multiple returns

1 Answers1