具有命名参数VS问号参数的PDO语句 [英] PDO statements with named parameters VS question mark parameters

问题描述

我有一个用于数据库管理的类，并且我的一个子类(定义查询的一个子类)的定义如下(只是一个示例，实际上剥离了许多其他功能以进行测试):

namespace Tests\SQL\Arguments {
    // SQL query
    class Query {
        public $attributes;

        // constructor for this object
        public function __construct() {
            if ($arguments = func_get_args()) {
                $this->attributes["query"] = current($arguments);

                if (sizeof($arguments) > 1) {
                    $this->attributes["parameters"] = array_slice($arguments, 1, sizeof($arguments));
                }

                return $this;
            }
        }
    }

    $query = new Query("INSERT INTO `clients/history` (`date`,`client`,`ammount`,`status`) VALUES (?,?,?,?);", date("Y-m-d H:i:s"), 57, 17852.25, "A");
    print_r($query);
}

如您所见，我自动获取函数参数，因此可以在构造时轻松地将查询与其参数分开.除了批量INSERT/UPDATE/DELETE动作外，我还想提供一些安全性，例如防止SQL注入和其他操作.

我的问题是...给定这种结构，当我通过这样的结构时(只是一个简单的示例，它将以不同的方式运行，但是该方法暂时仍然有效 ):

$this->queries["clients/history"]->execute($this->attributes["query"], $this->attributes["parameters"]);

使用诸如(:date,:client,:ammount,:status)之类的命名参数或诸如(?,?,?,?)之类的问号参数会有所不同吗?

编辑-更好的解释

很抱歉，我的问题造成的(明显)模糊性.我的目的是要拥有一种类似于sprintf的机制，但是，我没有以存储所有组成参数的字符串的方式存储，而是以一种单独的方式存储了查询和参数.

这只是Query类.还有QueryGroup类(用于在组中存储查询)，Manager类(用于存储和管理所有数据库连接)和Connection类(用于将给定数据库连接的所有查询和查询组保持在一起).

关于命名参数，我认为所使用的方法没有问题，因为就像我所说的那样，它就像sprintf函数一样有效.我将在查询字符串中提供问号或参数名称.

我想进行分离以提供其他过滤功能，例如转义或引用参数，以防止对给定数据库进行某种形式的注入或破坏.

我介绍的execute()方法只是PDO execute()方法的纸质副本.我试图确定的是使用命名参数或问号参数是否同样安全"(或者可能有一些区别，我在那儿看不到).

任何提示将不胜感激:)

解决方案

命名未命名参数之间的区别在于，使用未命名参数时，您必须注意将它们绑定到查询的顺序. /p>

尤其是在您的示例中，未命名的参数非常适合，因为它可以简化函数调用.

---

还要注意，您无需在构造函数方法中调用return $this;.

I have a class for database management, and one of my sub-classes (the one that defines a query) is defined like this (just a sample, many other functions are actually stripped for testing purposes):

namespace Tests\SQL\Arguments {
    // SQL query
    class Query {
        public $attributes;

        // constructor for this object
        public function __construct() {
            if ($arguments = func_get_args()) {
                $this->attributes["query"] = current($arguments);

                if (sizeof($arguments) > 1) {
                    $this->attributes["parameters"] = array_slice($arguments, 1, sizeof($arguments));
                }

                return $this;
            }
        }
    }

    $query = new Query("INSERT INTO `clients/history` (`date`,`client`,`ammount`,`status`) VALUES (?,?,?,?);", date("Y-m-d H:i:s"), 57, 17852.25, "A");
    print_r($query);
}

As you can see, I automatically pick up the function arguments, so I can separate the query from its paremeters with ease at construction time. Apart from bulk INSERT/UPDATE/DELETE actions, I would like to provide some security, like preventing SQL injections and other things.

My question is... given this structure, when I pass this structure like (just a simple example, it will be run in a different way, but this one is valid for the time being):

$this->queries["clients/history"]->execute($this->attributes["query"], $this->attributes["parameters"]);

Will there be any different in using named parameters like (:date,:client,:ammount,:status) or using question mark parameters like (?,?,?,?)?

EDIT - Better explanation

Sorry for the (apparent) obscureness my question poses. My intention is to have a mechanism similar to sprintf but, instead of storing a string with all parameters composed into it, I just store the query and the parameters in a separate fashion.

This is just the Query class. There's also the QueryGroup class (for storing queries in groups), the Manager class (which stores and manages all database connections) and the Connection class (which is responsible for holding together all queries and query groups for a given database connection.

About the named parameters, I see no problem with the method I'm using, as this works, like I said, like the sprintf function. I'll be providing either question marks or the parameters' names in the query string.

I want to make a separation to provide addition filtering capabilities like escaping or quoting parameters to prevent some forms of injection or sabotage against a given database.

The execute() method I've exposed is just a paper-copy of PDO's execute() method. What I try to determine is if it's equally 'safe' to use named parameters or question mark parameters (or maybe there's some differences I'm not seeing there).

Any hint would be greatly appreciated :)

解决方案

The difference between named an unamed parameters is that with unnamed parameters you'll have to take care about the order in which they will be bound to the query.

Especially in your example unnamed params will fit very good as it eases the function call.

---

Further note that you won't need to call return $this; in a constructor method.

这篇关于具有命名参数VS问号参数的PDO语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！