php - 将 MySQL 转换为 PDO

Question

在这里我被多次建议开始将我的代码更改为 PDO，我终于抽出时间去做了。我的问题是我在转换现有登录脚本时遇到了难以置信的困难。对于下面代码的最后几行(在 $result = $query->fetchAll(); 行之后)，我无法在网上找到任何可以帮助我重写的资源它:

$username = $_POST['username'];
$password = $_POST['password'];

$db=getConnection();

$username = mysql_real_escape_string($username);

$query = $db->prepare( "SELECT password, salt, 'employer' as user_type
FROM JB_Employer
WHERE Username = '$username'

UNION
SELECT password, salt, 'jobseeker' as user_type
FROM JB_Jobseeker
WHERE User_Name = '$username'");

$result = $query->fetchAll();

$qData = mysql_fetch_array($result, MYSQL_ASSOC);
$hash = hash('sha256', $qData['salt'] . hash('sha256', $password) );

if ($result -> rowcount() <1 ;) { print “Fail, No such user”;}

if ($hash != $qData['password']) { header('Location: register.php?loginStatus=fail');  exit;}

else {$_SESSION['user'] = $username;
$_SESSION['permission'] = $qData['user_type'];}

谁能建议我如何实现这一目标？

Answer 1

看看这个，请再次考虑您的代码，尤其是关于 XSS 漏洞的代码!此外，为了好的开发人员，重构/重写您的数据库。这就是要走的路。

此外，代码未经测试。

<?php

$db = getConnection(); //assuming you are returning a PDO object here!

$username = getUsername(); //assuming you are NOT escaping the username!
$password = getPassword(); //assuming your hashed password here!

$query = "SELECT password, salt, 'emplyer' as user_type
FROM  JB_Employer
WHERE Username = :username

UNION

SELECT password, salt, 'jobseeker' as user_type
FROM JB_Jobseeker
WHERE User_Name = :username";

//$statement == PDOStatement
$statement = $db->prepare($query);

//bind the $username param to :username, this is the real power of PDO,
//no more SQL Injections. Don't use mysql_real_escape-esque things!
//they are not nececary with PDO
$statement->bindParam(":username", $username);

//execute the statement
if($statement->execute()){
    $result = $statement->fetchAll();

    $rowCount = count($result);

    if($rowCount < 1){
        // redirect?
        die("No Such user");
    }else{
        // more than one user can be possible, this is not the correct way, but it appears to be your way so let's continue
        $firstRow = $result[0];

        if( isPasswordEqual($firstRow['salt'], $password) ){
            $_SESSION['user'] = $username; //security risk here. Vulnerable for XXS
            $_SESSION['permission'] = $firstRow['user_type'];
        }else{
            //Don't tell them this! It will give them knowledge of which accounts do exist.
            //Just say some general message like "login failed"
            die("wrong information");
        }
    }
}