sql athentication in mysql

Question

0.00/5 (No votes)

See more:

-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE FUNCTION `bank`.`FetchUserDetails` (parUname varchar(45), parpassword varchar(45),parusertype varchar(45))
RETURNS varchar(200)
DETERMINISTIC
BEGIN

declare varuname varchar(45);
declare varpassword varchar(45);
declare varuseType varchar(45);

select username,password,userType into varuname,varpassword,varuseType
from bank.user
where varuname = parUname and
varpassword = parpassword and
varuseType = parusertype;

RETURN varuname and varpassword and varuseType;
END

Can someone please tell me what is wrong with this sql statement?
what i am trying to do is authenticate a user based on the details provided and after authenticating return the username, password and role to the calling method.

Posted 31-Oct-12 8:09am

Georges23

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2012-10-31T09:07:00

Solution 1

Well first off, you shouldn't be storing passwords in clear text - see here: Password Storage: How to do it.[^]

Other than that we would need to know what is does that it shouldn't, or doesn't do that it should.

Posted 31-Oct-12 9:07am

OriginalGriff

Comments

Georges23 31-Oct-12 15:12pm

its suppose to return the password, username, usertype. right now its not.

OriginalGriff 31-Oct-12 15:21pm

So what is it returning?

Georges23 31-Oct-12 15:45pm

its returning null when i call it

OriginalGriff 31-Oct-12 16:17pm

The most likely cause is that it is failing one or more of the AND clauses in the WHERE statement. (Assuming you are using MySql, MsSql doesn't use that syntax)

I would try changing it to check just one field against a constant value, and then move up adding a bit at a time. It may be that the data you are seeking is not in the DB.

Georges23 31-Oct-12 16:19pm

when i return one variable it works. you are right the "and" key cannot be used there.

OriginalGriff 31-Oct-12 16:26pm

It's probably not the AND itself, but that one of the tests doesn't match - add them one at a time, and you should find it. But seriously, you shouldn't store text based passwords at all!

Georges23 31-Oct-12 16:20pm

do you perhaps know how i can return multiple variables ?

OriginalGriff 31-Oct-12 16:29pm

Why do you want to? The caller already knows the user name, password and type - because it sent them to you.
Why not just return a single boolean - TRUE for match, FALSE for no match.
It seems pointless returning exactly what the inputs were when you have a match!

Georges23 31-Oct-12 16:21pm

CREATE PROCEDURE `bank`.`FetchUserDetails` (in parUname varchar(45), in parpassword varchar(45),in parusertype varchar(45),
out parUnameI varchar(45), out parpasswordI varchar(45),out parusertypeI varchar(45) )
BEGIN

select username,password,userType into parUnameI,parpasswordI,parusertypeI
from bank.user
where username = parUname and
password = parpassword and
userType = parusertype;
END

how do i call this procedure ?

Georges23 31-Oct-12 16:37pm

the thing is i want to know the kind of user i am dealing with. i have two kinds of users and they have different roles. and about the password storage i will change that.