PDO 连接和抽象类

2021-12-26 00:00:00 oop php pdo

我在 PDO 中使用抽象类.我想知道是否有必要每次都将 $conn 变量置空，或者它是否在脚本结束时自行置空?

I am using abstract class with PDO. I want to know if it is necessary to null the $conn variable every time, or if it does so itself when the scripts ends?

您能否也告诉我，对于这种结构，取消 $conn 的最佳方法是什么?

Can you also tell me, with this type of structure, what would be the best way to nullify $conn?

abstract class DB_Connection
{
    protected static $tbl_admin = "prof_admin";
    //protected static $tbl_admin = "prof_admin";

    protected static function obj_db()
    {
        $servername = "localhost";
        $username = "root";
        $password = "";
        $dbname = "salmanshahid";
        $conn = null;

        try 
        {
            $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
            // set the PDO error mode to exception
            $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            return $conn;
        }
        catch(PDOException $e)
        {
            echo $sql . "<br>" . $e->getMessage();
        } 
    }
    protected static function select($query)
    {
        try 
        {
            $conn = self::obj_db();
            $stmt = $conn->prepare($query);
            $stmt->execute();

            // set the resulting array to associative
            $stmt->setFetchMode(PDO::FETCH_ASSOC); 
            return $stmt->fetchAll();
        }
        catch(PDOException $e) 
        {
            throw new Exception("Error In SELECT STATMENT: " . $e->getMessage());
        }
    }
    protected static function insert($query)
    {
        try 
        {
            $conn = self::obj_db();
            $stmt = $conn->prepare($query);
            $stmt->execute();
        }
        catch(PDOException $e) 
        {
            throw new Exception("Error In INSERT STATMENT: " . $e->getMessage());
        }
    }

}

推荐答案

或者如果脚本结束时它自己这样做?

or if it does so itself when the scripts ends?

是的，当然，PHP 会自动关闭并清理脚本执行过程中打开的所有资源，因此，不必担心手动关闭它.

Yes, sure, PHP automatically close and clean up all the resources that has been opened during script execution, so, don't worry to close it manually.

无论如何，要使 conn 无效，只需将其无效:$this->conn = NULL;

Anyway, to nullify conn, just nullify it: $this->conn = NULL;

但与您班级的其他问题相比，所有这些都完全可以忽略不计，这些问题不安全、效率低下且无法使用.

But all that stuff is utterly negligible compared to the other problems of your class, which is insecure, inefficient and unusable.

  • 首先，我不知道你为什么要把这个类抽象化.抽象类是原型类，曾经是其他类的来源.但是数据库包装器是一个随时可用的最终类.我认为将其抽象化没有任何意义.
  • 错误报告也是多余且不一致的.在错误消息中添加Error In SELECT STATMENT"是非常无用的.而连接错误处理显然是错误的.相反，让 PDO 抛出异常并让它过去.它将以与您网站中的任何其他错误相同的方式处理.
  • 下一个问题是安全性.出于某种原因，select() 和 insert() 函数都不支持 准备好的语句，这使得它们毫无用处:你可以使用 PDO::query() 相反，结果完全相同.但是你真正需要的是正确使用准备/执行，通过在查询中使用占位符，同时将实际变量发送到 execute();
  • 另一个问题是代码重复:两个函数几乎相同.
  • 同时这两个函数都非常不可靠:select() 函数仅限于一种类型的结果集，而 insert() 不返回什么都没有.相反，您可以只使用单个函数来运行所有查询，并使其返回语句，这将非常有用.它可以让您获得 PDO 支持的几十种不同格式的返回数据，甚至可以让您从 DML 查询中获得受影响的行数.
  • First of all, I have no idea why would you want to make this class abstract. Abstract classes are prototype classes, used to be source of other classes. But a database wrapper is rather a ready to use final class. I see no point in making it abstract.
  • Error reporting is also superfluous and inconsistent. Adding "Error In SELECT STATMENT" to the error message is quite useless. While connection error handling is plainly wrong. Instead, let PDO to throw an exception and just let it go. It will be handled the same way as any other error in your site.
  • Next problem is security. For some reason neither select() not insert() function supports prepared statements, which renders them quite useless: you can use PDO::query() instead, with exactly the same outcome. But what you really have to is to use prepare/execute properly, by using placeholders in the query while sending actual variables to execute();
  • Another problem is duplicated code: both functions are pretty much the same.
  • And at the same time both function are quite unreliable: select() function is limited to only one type of result set, while insert() doesn't return anything at all. Instead, you can use just single function to run all your queries, and make it return the statement, which will be extremely useful. It will let you to get the returned data in dozens different formats supported by PDO, and even let you to get the number of affected rows from DML queries.

让我向您推荐另一种方法，一个简单的 PDO 包装器，可以让您以最简单和安全的方式使用 PDO:

Let me suggest you another approach, a simple PDO wrapper that can let you to use PDO most simple and secure way:

<?php
define('DB_HOST', 'localhost');
define('DB_NAME', 'test');
define('DB_USER', 'root');
define('DB_PASS', '');
define('DB_CHAR', 'utf8');

class DB
{
    protected static $instance = null;

    public function __construct() {}
    public function __clone() {}

    public static function instance()
    {
        if (self::$instance === null)
        {
            $opt  = array(
                PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                PDO::ATTR_EMULATE_PREPARES   => TRUE,
            );
            $dsn = 'mysql:host='.DB_HOST.';dbname='.DB_NAME.';charset='.DB_CHAR;
            self::$instance = new PDO($dsn, DB_USER, DB_PASS, $opt);
        }
        return self::$instance;
    }

    public static function __callStatic($method, $args)
    {
        return call_user_func_array(array(self::instance(), $method), $args);
    }

    public static function run($sql, $args = [])
    {
        $stmt = self::instance()->prepare($sql);
        $stmt->execute($args);
        return $stmt;
    }
}

它非常强大、安全且易于使用.

It's extremely powerful, secure, and easy to use.

你可以使用任何 PDO 函数，只需在 DB:: 前缀后添加它的调用:

You can use any PDO function by simply adding it's call after DB:: prefix:

$stmt = DB::query("SELECT * FROM table WHERE foo='bar'");

所以，首先，它是一个PDO 包装器，它能够通过使用神奇的__call() 方法来运行任何PDO 方法.我添加的唯一函数是 run().

So, in the first place, it's a PDO wrapper, which is able to run any PDO method by means of using magic __call() method. The only function I added is run().

代替你自己不安全和不可靠的 select() 和 insert() 方法，让我建议你使用一个通用的 run()方法，无非是这三行的简写:

Instead of your own insecure and unreliable select() and insert() methods let me suggest you to use one universal run() method, which is nothing more than a shorthand to these three lines:

$stmt = DB::prepare($query);
$stmt->execute($params);
$data = $stmt->fetch();

所以，你可以把它写成一个简洁的单行:

So, instead you can write it as a neat one-liner:

$data = DB::run($query, $params)->fetch();

请注意，它可以运行任何类型的查询，并以 PDO 支持的任何格式返回结果.

Note that it can run a query of any kind and return the result in any format that PDO supports.

我写了一篇关于这个简单包装器的文章，您可以在其中找到一些使用示例.所有示例代码都可以按原样运行，只需将其复制并粘贴到您的脚本中并设置凭据:http://phpdelusions.net/pdo/pdo_wrapper#samples

I wrote an article about this simple wrapper, where you can find some usage examples. All the example code can be run as is, just copy and paste it in your script and set up credentials: http://phpdelusions.net/pdo/pdo_wrapper#samples

相关文章