PDO:将多余的参数传递给准备好的语句

问题描述

您能否向使用 PDO 的准备好的语句发送比需要更多的参数而不会产生不良副作用?

这似乎是一个奇怪的问题，但我问的是因为我连续有 4 个查询，它们都使用相似和不同的参数.查询的相关部分:

That mights seem like a strange question but I ask because I have 4 queries in a row which all use similar and different parameters. The relevant parts of the queries:

第一个(选择，与其他人不同的表):
WHERE threadID = :tid

1st (select, different table to others):
WHERE threadID = :tid

第二(选择):
WHERE user_ID = :u_ID AND thread_ID = :tid

第三个(如果第二个成功则更新):
SET time = :current_time WHERE user_ID = :u_ID AND thread_ID = :tid

3rd (update if 2nd was successful):
SET time = :current_time WHERE user_ID = :u_ID AND thread_ID = :tid

第四(如果第二不成功则插入):
VALUES (:u_ID, :tid, :current_time)

4th (insert if 2nd was unsuccessful):
VALUES (:u_ID, :tid, :current_time)

我可以用开头的三个参数声明一个数组并将其用于所有 4 个查询吗?

Can I declare one array with the three parameters at the beginning and use it for all 4 queries?

为了解决任何混淆，查询将单独执行.它是重用的参数变量，因此这意味着某些查询将接收他们不需要的参数.所以就像:

To sort out any confusion, the queries would be executed seperately. It is the parameters variable being reused and so that would mean some queries would receive parameters they don't need. So something like:

$parameters = array(':tid' => $tid, ':u_ID' => $u_ID, ':current_time' => $time);

$1st = $db->prepare($query1);
$1st->execute($parameters);

$2nd = $db->prepare($query2);
$2nd->execute($parameters);

$3rd = $db->prepare($query3);
$3rd->execute($parameters);

$4th = $db->prepare($query4);
$4th->execute($parameters);

如果可以，我应该这样做吗?这会减慢我的数据库或脚本的速度或导致安全漏洞吗?

If I can, should I? Will this slow down or cause security flaws to my database or scripts?

如果我能把这个问题说得更清楚一点，请提问.

If I can make this question a bit clearer, please ask.

谢谢！

推荐答案

我有机会测试我的问题，答案是发送的参数不能超过查询使用的参数.您收到以下错误:

I got a chance to test my question, and the answer is you cannot send more parameters than the query uses. You get the following error:

PDOException Object
(
    [message:protected] => SQLSTATE[HY093]: Invalid parameter number: parameter was not defined
    [string:Exception:private] =>
    [code:protected] => HY093
    [file:protected] => C:Destination	ofile.php
    [line:protected] => line number
    [trace:Exception:private] => Array
        (
            [0] => Array
                (
                    [file] => C:Destination	ofile.php
                    [line] => line number
                    [function] => execute
                    [class] => PDOStatement
                    [type] => ->
                    [args] => Array
                        (
                            [0] => Array
                                (
                                    [:u_ID] => 1
                                    [:tid] => 1
                                    [:current_time] => 1353524522
                                )

                        )

                )

            [1] => Array
                (
                    [file] => C:Destination	ofile.php
                    [line] => line number
                    [function] => function name
                    [class] => class name
                    [type] => ->
                    [args] => Array
                        (
                            [0] => SELECT
                                                column
                                            FROM
                                                table
                                            WHERE
                                                user_ID  = :u_ID AND
                                                thread_ID = :tid
                            [1] => Array
                                (
                                    [:u_ID] => 1
                                    [:tid] => 1
                                    [:current_time] => 1353524522
                                )

                        )

                )

        )

    [previous:Exception:private] =>
    [errorInfo] => Array
        (
            [0] => HY093
            [1] => 0
        )

)

我对 PDO 了解不多，因此我提出了问题，但我认为这是因为 :current_time 已发送但未使用，并且错误消息是参数编号无效:参数未定义"，您无法发送额外的参数哪些未被使用.

I don't know a huge amount about PDO, hence my question, but I think that because :current_time is sent but not used and the error message is "Invalid parameter number: parameter was not defined" you cannot send extra parameters which are not used.

另外生成错误代码 HY093.现在我似乎无法在任何地方找到任何解释 PDO 代码的文档，但是我遇到了以下两个专门关于 HY093 的链接:
什么是 PDO 错误 HY093
SQLSTATE[HY093]

Additionally the error code HY093 is generated. Now I can't seem to find any documentation explaining PDO codes anywhere, however I came across the following two links specifically about HY093:
What is PDO Error HY093
SQLSTATE[HY093]

HY093 好像是参数绑定不正确时生成的.这一定是在这里发生的，因为我绑定了太多参数.

It seems HY093 is generated when you incorrectly bind parameters. This must be happening here because I am binding too many parameters.

这篇关于PDO:将多余的参数传递给准备好的语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！