Error php pdo select Command

[Maideen]

Hi

I am new in php. Below code works fine. But If I change Select Command Like this:

$sql = "SELECT login_id,password FROM user_right WHERE login_id = '$username' and password = '$password'";

It shows error recorde not found. Also I need to show the role of select user. Pls Help me

<?php
include_once'../inc/header.php';
if (isset($_POST['submit']) && $_POST['submit']  != "" )
{ 
       if(empty($_POST['username']))
            {
                $handleError = "User is empty!";
                $_SESSION["errormsg"] = $handleError;
                header("Location:../notification/errormsg.php"); 
                //echo $handleError;
                return false;
            }
    if(empty($_POST['password']))
            {
                $handleError = "Password is empty!";
                $_SESSION["errormsg"] = $handleError;  
                header("Location:../notification/errormsg.php"); 
                //echo $handleError;
                return false;
            }
        $username = trim($_POST['username']);
        $password = trim($_POST['password']);
        try 
            {
                $sql  = "SELECT * FROM user_right WHERE login_id = '$username' and password = '$password'";  
                $stmt = $dbh->prepare($sql); 
                $stmt->setFetchMode(PDO::FETCH_ASSOC);  
                if ($stmt = $dbh->query($sql)) {
                    if ($stmt->fetchColumn() > 0) 
                        {
                            $_SESSION["username"] = $username;
                            $_SESSION["password"] = $password;
                            header("Location:../admin/"); 
                            exit();
                        }
                    else 
                        {
                            $handleError="user name or password is wrong";
                            $_SESSION["errormsg"] = $handleError;  
                            header("Location:../notification/errormsg.php"); 
                            exit(); 
                        };
                }                
            }
         catch (PDOException $e) 
            {
              echo $e->getMessage() . "\n";
              file_put_contents('PDOErrors.txt', $e->getMessage(), FILE_APPEND);
            }
}
include '../inc/footer.php';

pls help me

[cereal]

Hi,

the prepare() method requires execute() not query(), so this:

$sql  = "SELECT * FROM user_right WHERE login_id = '$username' and password = '$password'"; 
$stmt = $dbh->prepare($sql); 
$stmt->setFetchMode(PDO::FETCH_ASSOC);
if($stmt = $dbh->query($sql)){
...

Should be:

$values = array(
    $username,
    $password
);

$sql  = "SELECT * FROM user_right WHERE login_id = ? and password = ? limit 1";
$stmt = $dbh->prepare($sql); 
$stmt->execute($values);
$result = $stmt->fetchAll();
$countRows = $dbh->query("SELECT FOUND_ROWS() as total")->fetchColumn();

if($countRows > 0)
{
    foreach($result as $row)
    {
        $_SESSION["username"] = $row['username'];
        $_SESSION["password"] = $row['password']; # to remove
        header("Location:../admin/"); 
    }
}
else
{
    $handleError="user name or password is wrong";
    $_SESSION["errormsg"] = $handleError;  
    header("Location:../notification/errormsg.php"); 
}

Since you're using prepared statement, the query cannot be injected, but the session yes, because you're using:

$username = trim($_POST['username']);

So, it's better to use the result of the query which should be already sanitized and filtered:

$_SESSION["username"] = $row['username'];

also, it's not a good idea to save the password in session, especially if this is not encrypted.

Docs:

• http://www.php.net/manual/en/pdostatement.execute.php

[Maideen]

hi cereal

Thanks. Now it is working..