如何使用sqlsrv和“?"在php中执行存储过程风格参数

2021-12-20 00:00:00 sql sql-server-2005 php sqlsrv stored-procedures

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

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.

要清楚，以下失败:

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

$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

相关文章