Check if a user is validated mysql

Question

I'm looking to find out if a user is validated when they try to log in but I'm doing something wrong

$validationsql = mysql_query("SELECT Validation FROM users WHERE Username = '".$username."' AND Password = '".$password."'");
$validationresult = mysql_query($validationsql) or die('error');
if ($validationresult == "'FALSE'")
{
         echo "<h1>Validation Error</h1>";
         die;
}

SQL INJECTION!!!! Refer to [this](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) for more details — asprin, Jan 29 '13 at 06:51
What does "doesn't work" mean? "Doesn't work" is an inadequate description for us to understand the problem. What happened when you tried it? Did you get incorrect results? Did you get *no* results? If the results were incorrect, what made them incorrect? What were you expecting instead? Did you get *any* correct results? If so, what were they? Don't make us guess. — Andy Lester, Jan 29 '13 at 06:52
You are leaving yourself wide open to SQL injection. Please learn about using parametrized queries, preferably with the PDO module, to protect your web app. http://bobby-tables.com/php.html has examples to get you started. — Andy Lester, Jan 29 '13 at 06:53
Use mysql_num_rows($validationsql) to get the number of the results. if is equal 1 is true else false — One Man Crew, Jan 29 '13 at 06:53
God that was a lot of replies quicker than I expected, totally new with SQL trying to learn by trial and error. So excuse my ignorance. As I said to PLB I've got the mysql_real_escape_string in there just didn't post it. Have a look down at my reply to him to see where I'm at — Mark Berry, Jan 29 '13 at 07:07

Leri · Answer 1 · 2013-01-29T07:02:20.103

You are doing lots of things wrongly. For authentication you have to see if there's user, submitted by customer, in your database. To do so you have to use something like:

$validationsql = mysql_query("SELECT Validation FROM users WHERE Username = '".$username."' AND Password = '".$password."'"); //Retrives data from database
$usersfound = mysql_num_rows($validationsql) or die('error'); //counts how many records have been retrieved
/*
 *If number of data is not 1 (assuming you have unique users) validation fails
*/
if ($usersfound !== 1)
{
         echo "<h1>Validation Error</h1>";
         die;
}

But if you don't use prepared statements you have to escape data for security purposes (sql injection). To do so you can use mysql_real_escape_string. After this your code will become:

$username = mysql_real_escape_string($username);
$password = mysql_real_escape_string($password);

$validationsql = mysql_query("SELECT Validation FROM users WHERE Username = '".$username."' AND Password = '".$password."'"); //Retrives data from database
$usersfound = mysql_num_rows($validationsql) or die('error'); //counts how many records have been retrieved
/*
 *If number of data is not 1 (assuming you have unique users) validation fails
*/
if ($usersfound !== 1)
{
         echo "<h1>Validation Error</h1>";
         die;
}

And it still does not mean that you're doing everything right. mysql_* functions are deprecated. You should be using either PDO or mysqli_* functions. Also you have to actively use prepared statements.

Sorry man you're right but I've explained my problem wrong. First I've already got the mysql_real_escape_string in there but in all honesty I had no idea what it was for. Also by validation what I meant was to check if they have validated their account, validation is in the database as an ENUM True or False, what I was trying to do is check if they have registered or not. — Mark Berry, Jan 29 '13 at 07:02
@MarkBerry Add one more condition in `where`: `... WHERE Username = 'username' AND password='pass' And activated=1`. Also read more about security I've referenced some good points to start. ;) — Leri, Jan 29 '13 at 07:07

score 0 · Answer 2 · answered Jan 29 '13 at 06:50

0

1st thing just don't store the password like that. Use something like MD5 or a other hashing method.

Try the below code

if (!$validationresult)
{
         echo "<h1>Validation Error</h1>";
         die;
}

answered Jan 29 '13 at 06:50

Techie

44,706
42
157
243

score 0 · Answer 3 · answered Jan 29 '13 at 06:53

Assuming you have a field named "Validation" in your database table, If it is not there change it to SELECT * FROM ...

$validationsql = "SELECT Validation FROM users WHERE Username = '".$username."' AND Password = '".$password."'";
$validationresult = mysql_query($validationsql) or die('error');
if (mysql_num_rows($validationresult) == 0)
{
         echo "<h1>Validation Error</h1>";
         die;
}

Better use mysqli OR PDO, which is secure. Your code has lot of vulnerabilities

Check if a user is validated mysql

3 Answers3