Select statement in login. Query failed

Question

This is my code for check login. It return query failed. I don't know which syntax is wrong. Any help ? Do I need to SELECT everything in my table only ? In my login page, there are only need username, password and role. But in my table there are other things to as stated at my $_SESSION below. Do I need to call back everything in my table or something ? Because my coding can't read the query. There is no syntax error but when I try to login, query failed appear.

<?php
session_start();
require 'database.php';

//to store validation errors
$errmsg_arr = array();

//validation error flag
$errflag = false;

//function to sanitize values from the form. Preventing the SQL injection
function clean ($str){
    $str = @trim($str);
    if (get_magic_quotes_gpc()){
        $str = striplashes ($str);
    }
    return mysql_real_escape_string($str);
}

//sanitize POST values
$myusername = clean ($_POST['username']);
$mypassword = clean ($_POST['password']);
$role = clean ($_POST['role']);

//input validations
if ($myusername = ''){
    $errmsg_arr[] = 'Insert your username';
    $errflag = true;
}
if ($mypassword = ''){
    $errmsg_arr[]= 'Insert you password';
    $errflag = true;
}

//if there are input validation, redirect back to home
if ($errflag){
    $_SESSION['ERRMSG_ARR'] = $errmsg_arr;
    session_write_close();
    header("location:index.php");
    exit();
}

$qry = "SELECT user_id, username, name, password, role FROM student WHERE username = '$myusername', password = '".md5($_POST['password'])."' AND role = '$role' ";
$result = mysql_query($qry);

if ($result){
    if (mysql_num_rows($result)== 1){

        session_regenerate_id();
        $student = mysql_fetch_assoc($result);  
        $_SESSION['SESS_USER_ID']= $student['user_id'];
        $_SESSION['SESS_NAME']= $student['name'];
        $_SESSION['SESS_GENDER']= $student['gender'];
        $_SESSION['SESS_MATRIC']= $student['matric'];
        $_SESSION['SESS_COLLEGE']= $student['college'];
        $_SESSION['SESS_FACULTY']= $student['faculty'];
        $_SESSION['SESS_COURSE']= $student['course'];
        $_SESSION['SESS_EMAIL']= $student['email'];
        $_SESSION['SESS_PHONE']= $student['phone'];     
        session_write_close();
        header("location: profile.php");
        exit();
    }else {
        header ("location: login_failed.php");
        exit();
    }
}else {
    die ("Query failed");
}
?>

**[Don't use mysql_*; use mysqli_* or PDO](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php?rq=1).** — elixenide, Dec 06 '13 at 17:32
You're checking the return values of your database calls; but if they've failed, you can use `mysql_error()` to get the actual error message from the database. — andrewsi, Dec 06 '13 at 17:34

Krish R · Answer 1 · 2013-12-06T17:43:35.347

Try using and instead of , between username and password

WHERE username = '$myusername' and  password = '".md5($_POST['password'])."' AND role = '$role'

instead of

WHERE username = '$myusername', password = '".md5($_POST['password'])."' AND role = '$role'

PHP

 $result = mysql_query($qry) or die (mysql_error());
 if (mysql_num_rows($result) > 0){

    session_regenerate_id();
    $student = mysql_fetch_assoc($result);  
    $_SESSION['SESS_USER_ID']= $student['user_id'];
    $_SESSION['SESS_NAME']= $student['name'];
    $_SESSION['SESS_GENDER']= $student['gender'];
    $_SESSION['SESS_MATRIC']= $student['matric'];
    $_SESSION['SESS_COLLEGE']= $student['college'];
    $_SESSION['SESS_FACULTY']= $student['faculty'];
    $_SESSION['SESS_COURSE']= $student['course'];
    $_SESSION['SESS_EMAIL']= $student['email'];
    $_SESSION['SESS_PHONE']= $student['phone'];     
    session_write_close();
    header("location: profile.php");
    exit();
}else {
    header ("location: login_failed.php");
    exit();
}

NOTE: Use mysqli_* functions or PDO instead of mysql_* functions(deprecated)

for the query, the problem solved. THANK YOU ! But now, there is error keep on saying the login failed even the username and password is correct. — user3051803, Dec 06 '13 at 17:35
Remove ` if ($result){ if (mysql_num_rows($result)== 1){` and use like ` if (mysql_num_rows($result)>0){` — Krish R, Dec 06 '13 at 17:39

score 1 · Answer 2 · edited May 23 '17 at 11:57

In your WHERE clause you have username='$myusername', password= which should be username='$myusername' AND password=

If you had a checked for the query failing, you'd probably could have found out that yourself, e.g.

$result = mysql_query($qry) or die(mysql_error());

Also keep in mind that mysql_* functions are officially deprecated and hence should not be used in new code. You can use PDO or MySQLi instead. See this thread on SO for more information.

In addition to that, md5 is not a secure method to hash passwords!

score 0 · Answer 3 · answered Dec 06 '13 at 17:38

try out this code

<?php
session_start();
require 'database.php';

//to store validation errors
$errmsg_arr = array();

//validation error flag
$errflag = false;

//function to sanitize values from the form. Preventing the SQL injection
function clean ($str){
    $str = @trim($str);
    if (get_magic_quotes_gpc()){
        $str = striplashes ($str);
    }
    return mysql_real_escape_string($str);
}

//sanitize POST values
$myusername = clean ($_POST['username']);
$mypassword = clean ($_POST['password']);
$role = clean ($_POST['role']);

//input validations
if ($myusername = ''){
    $errmsg_arr[] = 'Insert your username';
    $errflag = true;
}
if ($mypassword = ''){
    $errmsg_arr[]= 'Insert you password';
    $errflag = true;
}

//if there are input validation, redirect back to home
if ($errflag){
    $_SESSION['ERRMSG_ARR'] = $errmsg_arr;
    session_write_close();
    header("location:index.php");
    exit();
}

try{
    $qry = "SELECT user_id, username, name, password, role FROM student WHERE username = '$myusername' AND password = '".md5($_POST['password'])."' AND role = '$role' ";
$result = mysql_query($qry);

    if (mysql_num_rows($result)== 1){

        session_regenerate_id();
        $student = mysql_fetch_assoc($result);  
        $_SESSION['SESS_USER_ID']= $student['user_id'];
        $_SESSION['SESS_NAME']= $student['name'];
        $_SESSION['SESS_GENDER']= $student['gender'];
        $_SESSION['SESS_MATRIC']= $student['matric'];
        $_SESSION['SESS_COLLEGE']= $student['college'];
        $_SESSION['SESS_FACULTY']= $student['faculty'];
        $_SESSION['SESS_COURSE']= $student['course'];
        $_SESSION['SESS_EMAIL']= $student['email'];
        $_SESSION['SESS_PHONE']= $student['phone'];     
        session_write_close();
        header("location: profile.php");
        exit();
    }else {
        header ("location: login_failed.php");
        exit();
    }

}catch(Exception $e){
    echo $e->getMessage();
}

yes , first , i modified the query in the where section , i added "and " between username and password , and second , i added try / catch , if there is any error regarding the DB resource value , the catch will print the error — Leo Bali, Dec 06 '13 at 17:41
problem solved thank you ! but now i can't login even I've entered username, password and role correctly (according to my database). Help ? — user3051803, Dec 06 '13 at 17:44
I don't believe mysql_* functions create exceptions, though, so you need to handle errors differently. — andrewsi, Dec 06 '13 at 17:45
please login using a correct username and pass and role , and try to var_dump(mysql_num_rows($result)) to check if the code is getting inside the if statment — Leo Bali, Dec 06 '13 at 17:46

Select statement in login. Query failed

3 Answers3