1

I have built a login system where I check if the user exists using either their username or their email address.

This is my code:

// DB Connection Included
$q = $dbcon->prepare("SELECT * FROM table WHERE (username=:e OR email=:e)");
$q->execute(array('e'=>$user));

But it doesn't work. It works perfectly if I change the query to:

$q = $dbcon->prepare("SELECT * FROM table WHERE username=:e)");

or

$q = $dbcon->prepare("SELECT * FROM table WHERE email=:e)");

I've used this code before and it has always worked perfectly, so I'm not sure why it doesn't work this time.

The actual error I get is a validation error in my code. I check to see if the user exists using the rowCount() function; using the desired query, rowCount() returns 0.

I have also tried:

$q = $dbcon->prepare("SET @user = :user SELECT * FROM table WHERE (username=@user OR email=@user)");

This was suggested on another post I found on Google, but this also did not work.

Does anyone have any suggestions?

Thanks in advance.

JustSomeGuy
  • 315
  • 2
  • 17

2 Answers2

3

According to PDO::Prepare

You must include a unique parameter marker for each value you wish to pass in to the statement when you call PDOStatement::execute(). You cannot use a named parameter marker of the same name more than once in a prepared statement, unless emulation mode is on.

So try -

$q = $dbcon->prepare("SELECT * FROM table WHERE (username=:e1 OR email=:e2)");
$q->execute(array(':e1'=>$user,':e2'=>$user));
Sean
  • 12,443
  • 3
  • 29
  • 47
  • @PrateikDarji semicolons in the `prepare()` are required, but optional in `execute()` https://stackoverflow.com/a/17386503/689579 and https://stackoverflow.com/a/38925175/689579 – Sean Jan 06 '18 at 04:50
  • @PrateikDarji it can only be done with one parameter, if as the docs state - *unless emulation mode is on*. So if the OP did `$dbcon->setAttribute(PDO::ATTR_EMULATE_PREPARES, TRUE);`, it would work – Sean Jan 06 '18 at 04:53
  • This worked. However, I have used the code that I provided (i.e. used the same parameter marker) on a different site that I built, and it worked fine. So I still don't understand why it worked then, but doesn't now. Anyway, thanks for your help. – JustSomeGuy Jan 06 '18 at 04:54
  • @JustSomeGuy its possible that the other site had `emulation mode` on by default. – Sean Jan 06 '18 at 04:56
  • Oh I see, I'll try to remember to consider that in the future. Thanks. – JustSomeGuy Jan 06 '18 at 04:58
-1

Looks okay to me except for the parentheses. Try ...

$q = $dbcon->prepare("SELECT * FROM table WHERE username=:e OR email=:e");
BareNakedCoder
  • 3,257
  • 2
  • 13
  • 16
  • 1
    *except for the parentheses*, are you saying that MySQL actually cares whether you put those parentheses or not? It really doesn't, but I personally think the query looks better without them. This can be a suggestion, but not an answer. – Racil Hilan Jan 06 '18 at 04:50
  • Using different parameter names solved my problem, however, I did adopt your method and have removed the parentheses. Thanks for your suggestion. – JustSomeGuy Jan 06 '18 at 04:56