SQL if statement with unknown value

Question

I have a situation where I am supposed to be pulling a customer`s file from a database and returning their password if their file is found, but if the customer ID that was entered has not been found, I am to return "NOT FOUND."

I was trying to set it up as an if statement like this: if $customerID = anything return password;

However, I wasn't sure how exactly I would go about assigning "anything" as a real part of the code. Is there a way to do this, or should I reevaluate the way I`m going at this entirely?

Hmmm... What if an evil customer sets his password to NOT FOUND.? — Cristian Lupascu
– Cristian Lupascu, Commented Jul 14, 2013 at 20:58
Use a try catch block? if it returns the password great, if not the catch block can output the message — null
– null, Commented Jul 14, 2013 at 20:58
please post what you have tried so far and we can try to help you figure it out — logixologist
– logixologist, Commented Jul 14, 2013 at 20:58
@w0lf - I hope no one is that mean! @logixologist - I`m working in mySQL, sorry! — cvt
– cvt, Commented Jul 14, 2013 at 21:00

Paul Dixon · Accepted Answer · 2013-07-14 21:00:01Z

1

You can use IFNULL to do something like this, but really, it's better to detect the NULL in your code and deal with the 'no user' condition.

SELECT IFNULL(password, 'NOT FOUND') AS password 
FROM yourtable 
WHERE customerid=XXX;

The problem, as others have noted, is that you can't tell the difference between a missing user record, and one with 'NOT FOUND' as the password. The implications of this might be benign, but also might lead to an exploitable condition in future. When it comes to check user inputs, trust nothing :)

answered Jul 14, 2013 at 21:00

Paul Dixon

302k54 gold badges315 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Moo-Juice Over a year ago

This solution falls down if I set my password to NOT FOUND :)

Paul Dixon Over a year ago

That's a flaw in the OP's request, not the solution :)

Fabian Bigler Over a year ago

@Moo-Juice why would the OP store passwords in plain text anyway?!

cvt Over a year ago

Thank you for your help! Like I said above, I should hope in this situation my professor wouldnt be mean enough to put me in that situation :P Should I use this solution, since I dont have a customer ID, could I just plus $customerID into the "XXX"?

Moo-Juice Over a year ago

@FabianBigler, why would you return "NOT FOUND" as the password at all? Why not just return NULL? :)

|

Gordon Linoff · Accepted Answer · 2013-07-14 21:00:08Z

0

You can do this in SQL, by using a subquery and coalesce(). The following is a guess at your data structure, but it shows how you can return NOT FOUND when nothing is found:

select coalesce((select f.password
                 from files f
                 where f.customerId = $CustomerId
                ), 'NOT FOUND')

Note: This assumes that at most one file has a given customer id. If not, it will return an error, something like "subquery returns more than one value".

answered Jul 14, 2013 at 21:00

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

1 Comment

cvt Over a year ago

Thank you for your help! I am completely unfamiliar with using coalesce, though I am intrigued. I am being graded in goBONGO, and it is very picky, so since I have not learned it, it will most likely not be accepted. However, you gave me something to think and learn about- which is better than the solution itself (:

Collectives™ on Stack Overflow

SQL if statement with unknown value

2 Answers 2

6 Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

1 Comment

Related