删除旧的DB函数和参数化我的SQL语句 - Removing old DB function and parameterising my SQL statements

我继承了一些不理想的PHP代码，我不确定纠正它的最有效方法是什么。

基本上所有的DB调用都是通过这样的自定义函数进行的:

function dbcommand($req)
{
        global $mysqli;
        $backtrace = debug_backtrace();
        $ret = array();
        $res = mysqli_query($mysqli, $req) or die('SQL Query Error: '. $mysqli->error .', query ['. $req .'] at '. $backtrace[0]['file'] .':'. $backtrace[0]['line']);
        if (strpos(strtoupper($req), 'SELECT') === 0)
        {
                if (mysqli_num_rows($res))
                {
                        while ($row = mysqli_fetch_assoc($res))
                                $ret[] = $row;
                }
                else $ret = array();
                mysqli_free_result($res);
                return $ret;
        }
        if (strpos($req, 'INSERT INTO') === 0)
            return $mysqli->insert_id;
        return $res;
}

现在我不认为我可以使用mysqli_real_escape_string，因为db-connector问题。所有的都经过这个函数。这意味着，在将变量覆盖到sql语句之前，避免sql注入的工作由filter_vars来完成。我想参数化我的SQL语句并正确地做到这一点。但我不确定在这种情况下最有效的方法是什么。删除此函数并将所有内容转换为PDO将非常耗时。有很多代码。

这是您的函数的pdo转换版本。它需要一些辅助类:

// This class is your connection class using a singleton (like a global)
// You would need to populate your credentials in the connect method
class   DatabaseConfig
    {
        private static  $singleton;
        public  function __construct()
            {
                if(empty(self::$singleton))
                    self::$singleton    =   $this->connect();
                return self::$singleton;
            }
        public  function connect($host = "localhost", $username = "username", $password = "password", $database = "database")
            {
                // Create connection
                $opts   =   array(  PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                                    PDO::ATTR_EMULATE_PREPARES => false,
                                    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC);
                $conn   =   new PDO('mysql:host='.$host.';dbname='.$database, $username, $password,$opts);
                return $conn;
            }
    }
// This is a query class that will run your sqls
class   QueryEngine
    {
        private $results;
        private static  $singleton;
        public  function __construct()
            {
                if(empty(self::$singleton))
                    self::$singleton    =   $this;
                return self::$singleton;
            }
        public  function query($sql = false,$bind = false)
            {
                $this->results  =   0;
                $db     =   new DatabaseConfig();
                try {
                        if(!empty($bind)) {
                                $query  =   $db ->connect()
                                                ->prepare($sql);
                                $query->execute($bind);
                            }
                        else {
                                $query  =   $db ->connect()
                                                ->query($sql);
                            }
                        $this->results  =   $query;
                    }
                catch (PDOException $e)
                    {
                        die($e->getMessage());
                    }
                return $this;
            }
        public  function fetch()
            {
                while($row = $this->results->fetch())
                    $result[]   =   $row;
                return (!empty($result))? $result : 0;
            }
    }
// This is your function down to the basics
// Error handling will be in the query class under try
function dbcommand($req,$bind = false)
    {   
        // Create query instance
        $qEngine    =   new QueryEngine();
        // Run the query
        $qEngine->query($req,$bind);
        // If select, fetch array
        if(strpos(strtoupper($req), 'SELECT') === 0)
            return $qEngine->fetch();
        // The query already ran, so you can return whatever you want
        // For ease I am just returning true
        elseif(strpos($req, 'INSERT INTO') === 0)
            return  true;
    }

使用

// Make sure it include the classes and function above
print_r(dbcommand("select * from `users` where `ID` = :0",array(":0"=>"1")));

这将给你类似的东西(在我的数据库中，显然，表和列对你来说是不同的):

Array
(
    [0] => Array
        (
            [ID] => 1
            [unique_id] => 20150203190700523616
            [username] => tester
            [password] => $2a$12$xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
            [first_name] => Ras
            [last_name] => Clatt
            [email] => ras@clatt.com
            [usergroup] => 3
            [user_status] => on
            [reset_password] => $2y$10$xxxxxxxxxxxxxxxxxxx
            [timestamp] => 2015-09-25 08:35:09
        )
)