如何使用sqlsrv和“？”样式参数在php中执行存储过程

I've looked over several other questions that seem (from the titles) the same as this. However, my case is a bit different.

我已经查看了其他几个看起来与问题相同的问题(来自标题)。但是,我的情况有点不同。

The following works (i.e. I get "success" and my database performs what I expect when running the procedure with the given variables):

以下工作(即我得到“成功”,我的数据库执行我在运行给定变量的过程时所期望的):

$sql = "MyDB.dbo.myProcedure {$var1}, {$var2}, {$var3}";
$result = sqlsrv_query($myConn, $sql);
if (!$result) {
    echo 'Your code is fail.';
}
else {
    echo 'Success!';
}

I want to avoid (or lessen the possibility of) SQL injection by creating the SQL string using parameters. For example:

我想通过使用参数创建SQL字符串来避免(或减少)SQL注入的可能性。例如:

$sql = "select * from aTable where col1 = ? AND col2 = ?";
$result = sqlsrv_query($myConn, $sql, array($var1, $var2));
//please note. This code WILL work!

But when I do that with a stored procedure it fails. It fails with no errors reported via sqlsrv_errors(), no action taken in database, and $result === false.

但是,当我使用存储过程执行此操作时,它会失败。它失败,没有通过sqlsrv_errors()报告错误,数据库中没有采取任何操作,$ result === false。

To be clear, the following fails:

需要说明的是,以下内容失败:

$sql = "MyDB.dbo.myProcedure ?, ?, ?";
$result = sqlsrv_query($myConn, $sql, array($var1, $var2, $var3));

Likewise a prepare/execute statement created the same way will also fail:

同样,以相同方式创建的prepare / execute语句也将失败:

$sql = "MyDB.dbo.myProcedure ?, ?, ?";
$stmt = sqlsrv_prepare($myConn, $sql, array(&$var1, &$var2, &$var3));
foreach($someArray as $key => $var3) {
    if(sqlsrv_execute($stmt) === false) {
        echo 'mucho fail.';
    }
}
//this code also fails.

For completeness, I have confirmed that the stored procedure in question works directly within SQL Management Studio AND if called the way I mentioned above. Likewise, I have confirmed that I can use parameterized queries for any raw query (like an insert, select, update vs a stored procedure).

为了完整起见,我已经确认有问题的存储过程直接在SQL Management Studio中工作,如果按照我上面提到的方式调用的话。同样,我已经确认我可以对任何原始查询使用参数化查询(如插入,选择,更新与存储过程)。

So, my question is how can I call a stored procedure using the parameterized query vs embedding the variables in the query string?

所以,我的问题是如何使用参数化查询调用存储过程vs在查询字符串中嵌入变量?

More importantly, I am actually wanting to use a prepare/execute, so hopefully the answer will allow this to work as well.

更重要的是,我实际上想要使用准备/执行,所以希望答案将允许这也可以工作。

2 个解决方案

#1

The user contributions on the php.net have a write up on how to execute a stored procedure using the sqlsrv-prepare.

php.net上的用户贡献记录了如何使用sqlsrv-prepare执行存储过程。

In case that is removed from the php.net user contributions in the future here is what it had(has) listed:

如果从php.net用户贡献中删除将来这里是它已经列出的内容:

$procedure_params = array(
array(&$myparams['Item_ID'], SQLSRV_PARAM_OUT),
array(&$myparams['Item_Name'], SQLSRV_PARAM_OUT)
);
// EXEC the procedure, {call stp_Create_Item (@Item_ID = ?, @Item_Name = ?)} seems to fail with various errors in my experiments
$sql = "EXEC stp_Create_Item @Item_ID = ?, @Item_Name = ?";
$stmt = sqlsrv_prepare($conn, $sql, $procedure_params);

Here's the manual's page, http://php.net/manual/en/function.sqlsrv-prepare.php

这是手册的页面,http://php.net/manual/en/function.sqlsrv-prepare.php

#2

This is a follow up to the answer by @chris85.

这是@ chris85的答案的后续内容。

It's worth noting here that once the statement is prepared, you need to execute it:

值得注意的是,一旦语句准备好,您需要执行它:

$sql = "EXEC stp_Create_Item @Item_ID = ?, @Item_Name = ?";
$stmt = sqlsrv_prepare($conn, $sql, $procedure_params);
if (!sqlsrv_execute($stmt)) {
    echo "Your code is fail!";
    die;
}
while($row = sqlsrv_fetch_rows($stmt)){
    //Stuff
}

sqlsrv_execute() only returns true/false. If you want to parse the data returned by the stored procedure you can process it just like the result from sqlsrv_query().

sqlsrv_execute()仅返回true / false。如果要解析存储过程返回的数据,则可以像处理sqlsrv_query()的结果一样处理它。

If you forget the sqlsrv_execute() you'll get an error saying that the result has to be executed before it can be used.

如果你忘记了sqlsrv_execute(),你会收到一个错误,说明在使用结果之前必须执行结果。

#1