如何使用sqlsrv和“?"在php中执行存储过程?样式参数 [英] How to execute a stored procedure in php using sqlsrv and "?" style parameters

问题描述

我已经查看了其他几个看起来(从标题中)与此相同的问题.但是，我的情况有些不同.

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!';
}

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

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

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

但是当我使用存储过程执行此操作时，它将失败.它失败，没有通过sqlsrv_errors()报告的错误，没有在数据库中执行任何操作，并且$result === false.

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.

为清楚起见，以下操作失败:

To be clear, the following fails:

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

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

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

$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.

出于完整性考虑，我已经确认所讨论的存储过程可以直接在SQL Management Studio中运行，并且可以按照我上面提到的方式直接运行.同样，我已经确认我可以可以对任何原始查询(例如插入，选择，更新与存储过程)使用参数化查询.

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).

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

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

更重要的是，我实际上是想使用准备/执行，因此希望答案能够使它也能正常工作.

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

推荐答案

php.net上的用户提供了有关如何使用sqlsrv-prepare执行存储过程的文章.

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

如果将来从php.net用户贡献中删除，则此处列出的是:

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

$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);

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

这篇关于如何使用sqlsrv和“?"在php中执行存储过程?样式参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！