php - 如何使用 php 类和对象与 mysql 数据库通信 - oop 初学者

Question

我开始从网上学习有关面向对象编程的资源不久。我正在开发一个应用程序，需要一定的编程能力来防止 SQL 注入(inject)、 session 劫持和暴力攻击，因此，在此过程中捕获有关谁使用或尝试访问该应用程序以及此类事件在何处的所有可能信息握住。我现在几乎已经用我自己学习的老式 php 编程完成了应用程序。但是，应用程序管理员可以使用一个工具在必要时检查上述信息的附加功能需要良好的 OOP 能力。

这是我的类文件代码；

class Database 
{
    private $host = DB_HOST;
    private $user = DB_USER;
    private $pass = DB_PASS;
    private $dbname = DB_NAME;
    private $dbh;
    private $error;
    private $stmt;


    public function __construct()
    {
        $dsn = 'mysql:host=' . $this->host . ';dbname=' . $this->dbname;
        $options = array(
            PDO::ATTR_PERSISTENT => true, 
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8",
            PDO::MYSQL_ATTR_INIT_COMMAND => "SET CHARACTER SET utf8"
        );

        try {
            $this->dbh = new PDO($dsn, $this->user, $this->pass, $options);
        } catch (PDOException $e) {
            $this->error = $e->getMessage();
        }
    }

    public function query($query)
    {
        $this->stmt = $this->dbh->prepare($query);
    }

    public function bind($param, $value, $type = null)
    {
        if (is_null($type)) {
            switch (true) {
                case is_int($value):
                    $type = PDO::PARAM_INT;
                    break;
                case is_bool($value):
                    $type = PDO::PARAM_BOOL;
                    break;
                case is_null($value):
                    $type = PDO::PARAM_NULL;
                    break;
                default:
                    $type = PDO::PARAM_STR;
            }
        }
        $this->stmt->bindValue($param, $value, $type);
    }

    public function execute()
    {
        return $this->stmt->execute();
    }

    public function resultset()
    {
        $this->execute();
        return $this->stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function single()
    {
        $this->execute();
        return $this->stmt->fetch(PDO::FETCH_ASSOC);
    }

    public function rowCount()
    {
        return $this->stmt->rowCount();
    }

    public function lastInsertId()
    {
        return $this->dbh->lastInsertId();
    }

    public function beginTransaction()
    {
        return $this->dbh->beginTransaction();
    }

    public function endTransaction()
    {
        return $this->dbh->commit();
    }

    public function cancelTransaction()
    {
        return $this->dbh->rollBack();
    }

    public function debugDumpParams()
    {
        return $this->stmt->debugDumpParams();
    }
}

然后我有一个数据库连接测试页，创建了数据库并附加了用户名和密码。现在，详细信息已正确输入到 php 页面中，其中包括下面的类；

require_once 'database.class.php';

define("DB_HOST", "localhost");
define("DB_USER", "username");
define("DB_PASS", "password");
define("DB_NAME", "dbname");

$database = new Database();

$database->query("CREATE TABLE mytable (
ID int(11) NOT NULL AUTO_INCREMENT,
FName varchar(50) NOT NULL,
LName varchar(50) NOT NULL,
Age int(11) NOT NULL,
Gender enum('male','female') NOT NULL,
PRIMARY KEY (ID)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ");

$database->query('INSERT INTO mytable (FName, LName, Age, Gender)      VALUES (:fname, :lname, :age, :gender)');

$database->bind(':fname', 'John');
$database->bind(':lname', 'Smith');
$database->bind(':age', '24');
$database->bind(':gender', 'male');

$database->execute();

echo $database->lastInsertId();

但它抛出以下错误；

Fatal error: Call to a member function prepare() on null in C:\xampp\htdocs\folder\folder1\folder2\database.class.php on line 38.

请问，我需要知道如何使用类在我的数据库中执行操作，到目前为止，我使用 MySQL_connect($dbserver, $dbroot, $dbpwd), MySQL_select_db($dbname,$query), MySQL_query("Statement") 方便地实现了数据库连接和通信。请我需要一个快速的解决方案，因为我目前正在对面向对象编程进行更多的学习和研究，但我的项目必须按时完成。谢谢

Answer 1

好吧，这应该是建立数据库连接的基类。将其保存为单独的文件。从这里您可以为您可能想要执行的特定交互编写自定义类。

这使用了 PDO 准备好的语句，因此它对于 SQL 注入(inject)来说几乎是防弹的。

<?php

/**
 * Class DatabaseFactory
 *
 * Use it like this:
 * $database = DatabaseFactory::getFactory()->getConnection();
 *
 * That's my personal favourite when creating a database connection.
 * It's a slightly modified version of Jon Raphaelson's excellent answer on StackOverflow:
 * http://stackoverflow.com/questions/130878/global-or-singleton-for-database-connection
 *
 * Full quote from the answer:
 *
 * "Then, in 6 months when your app is super famous and getting dugg and slashdotted and you decide you need more than
 * a single connection, all you have to do is implement some pooling in the getConnection() method. Or if you decide
 * that you want a wrapper that implements SQL logging, you can pass a PDO subclass. Or if you decide you want a new
 * connection on every invocation, you can do do that. It's flexible, instead of rigid."
 */

/**
 * Configuration for: Database
 * DB_TYPE The used database type. Note that other types than "mysql" might break the db construction currently.
 * DB_HOST The mysql hostname, usually localhost or 127.0.0.1
 * DB_NAME The database name
 * DB_USER The username
 * DB_PASS The password
 * DB_PORT The mysql port, 3306 by default (?), find out via phpinfo() and look for mysqli.default_port.
 * DB_CHARSET The charset, necessary for security reasons. Check Database.php class for more info.
 */
$DB_TYPE = 'mysql';
$DB_HOST = 'localhost';
$DB_NAME = 'db_name';
$DB_USER = 'user_name';
$DB_PASS = 'password';
$DB_PORT = '3306';
$DB_CHARSET = 'utf8';

class DatabaseFactory
{
    private static $factory;
    private $database;

    public static function getFactory()
    {
        if (!self::$factory) {
            self::$factory = new DatabaseFactory();
        }
        return self::$factory;
    }

    public function getConnection() {
        if (!$this->database) {
            $options = array(PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_OBJ, PDO::ATTR_ERRMODE => PDO::ERRMODE_WARNING);
            $this->database = new PDO(
                $DB_TYPE . ':host=' . $DB_HOST . ';dbname=' .
                $DB_NAME . ';port=' . $DB_PORT . ';charset=' . $DB_CHARSET,
                $DB_USER, $DB_PASS, $options
            );
        }
        return $this->database;
    }
}

?>

然后您可以像其他类/方法一样运行查询，例如 Users::doesUsernameAlreadyExist($user_name);

public static function doesUsernameAlreadyExist($user_name)
{

    // Establish Connection
    $database = DatabaseFactory::getFactory()->getConnection();

    // Prepare Query
    $query = $database->prepare("SELECT user_id FROM users WHERE user_name = :user_name LIMIT 1");

    // Execute
    $query->execute(array(':user_name' => $user_name));

    // If exists, return true
    if ($query->rowCount() == 1) { return true; }

    // Default, return false
    return false;

}