問題描述

我查看了其他幾個看起來(從標題中)與此相同的問題.但是，我的情況有點不同.

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

這篇關于如何使用sqlsrv和“?"在php中執行存儲過程風格參數的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！