使用PHP delete函數刪除數據庫記錄時，如何有效防止SQL注入？安全指南

gitbox 2025-06-15

什麼是SQL注入？

SQL注入是一種攻擊手段，攻擊者通過向SQL查詢中插入惡意的SQL代碼，從而操控數據庫執行未經授權的操作。對於DELETE語句，攻擊者可以通過SQL注入刪除不該刪除的數據，甚至可能刪除整個數據庫表，帶來嚴重的安全風險。

1. 使用準備好的語句（Prepared Statements）

為了有效防止SQL注入，最推薦的做法是使用準備好的語句（Prepared Statements） 。 PHP提供了與MySQL數據庫交互時使用的mysqli和PDO擴展，它們都支持準備好的語句。通過這種方式，可以將SQL查詢和輸入的用戶數據分離，從而防止惡意輸入註入到SQL語句中。

使用mysqli擴展進行刪除操作

<span><span><span class="hljs-meta">&lt;?php</span></span><span>
</span><span><span class="hljs-comment">// 數據庫連接</span></span><span>
</span><span><span class="hljs-variable">$mysqli</span></span><span> = </span><span><span class="hljs-keyword">new</span></span><span> </span><span><span class="hljs-title function_ invoke__">mysqli</span></span><span>(</span><span><span class="hljs-string">"localhost"</span></span><span>, </span><span><span class="hljs-string">"username"</span></span><span>, </span><span><span class="hljs-string">"password"</span></span><span>, </span><span><span class="hljs-string">"database_name"</span></span><span>);

</span><span><span class="hljs-comment">// 檢查連接</span></span><span>
</span><span><span class="hljs-keyword">if</span></span><span> (</span><span><span class="hljs-variable">$mysqli</span></span><span>-&gt;connect_error) {
    </span><span><span class="hljs-keyword">die</span></span><span>(</span><span><span class="hljs-string">"Connection failed: "</span></span><span> . </span><span><span class="hljs-variable">$mysqli</span></span><span>-&gt;connect_error);
}

</span><span><span class="hljs-comment">// 假設我們要刪除ID為某個用戶提供的值的記錄</span></span><span>
</span><span><span class="hljs-variable">$user_id</span></span><span> = </span><span><span class="hljs-variable">$_GET</span></span><span>[</span><span><span class="hljs-string">'id'</span></span><span>];  </span><span><span class="hljs-comment">// 從URL中獲取用戶提供的ID</span></span><span>

</span><span><span class="hljs-comment">// 使用準備好的語句來防止SQL注入</span></span><span>
</span><span><span class="hljs-variable">$stmt</span></span><span> = </span><span><span class="hljs-variable">$mysqli</span></span><span>-&gt;</span><span><span class="hljs-title function_ invoke__">prepare</span></span><span>(</span><span><span class="hljs-string">"DELETE FROM users WHERE id = ?"</span></span><span>);
</span><span><span class="hljs-variable">$stmt</span></span><span>-&gt;</span><span><span class="hljs-title function_ invoke__">bind_param</span></span><span>(</span><span><span class="hljs-string">"i"</span></span><span>, </span><span><span class="hljs-variable">$user_id</span></span><span>);  </span><span><span class="hljs-comment">// "i" 表示參數是整數類型</span></span><span>

</span><span><span class="hljs-comment">// 執行語句</span></span><span>
</span><span><span class="hljs-variable">$stmt</span></span><span>-&gt;</span><span><span class="hljs-title function_ invoke__">execute</span></span><span>();

</span><span><span class="hljs-comment">// 檢查是否刪除成功</span></span><span>
</span><span><span class="hljs-keyword">if</span></span><span> (</span><span><span class="hljs-variable">$stmt</span></span><span>-&gt;affected_rows &gt; </span><span><span class="hljs-number">0</span></span><span>) {
    </span><span><span class="hljs-keyword">echo</span></span><span> </span><span><span class="hljs-string">"記錄已刪除。"</span></span><span>;
} </span><span><span class="hljs-keyword">else</span></span><span> {
    </span><span><span class="hljs-keyword">echo</span></span><span> </span><span><span class="hljs-string">"未找到該記錄或刪除失敗。"</span></span><span>;
}

</span><span><span class="hljs-comment">// 關閉連接</span></span><span>
</span><span><span class="hljs-variable">$stmt</span></span><span>-&gt;</span><span><span class="hljs-title function_ invoke__">close</span></span><span>();
</span><span><span class="hljs-variable">$mysqli</span></span><span>-&gt;</span><span><span class="hljs-title function_ invoke__">close</span></span><span>();
</span><span><span class="hljs-meta">?&gt;</span></span><span>
</span></span>

在上面的代碼中， $stmt->bind_param()用於將用戶輸入的id綁定為參數， i表示該參數是整數類型。這樣做能有效地防止SQL注入，因為用戶輸入的id會被安全地作為參數傳遞給SQL語句，而不會被直接嵌入SQL語句中。

使用PDO擴展進行刪除操作

<span><span><span class="hljs-meta">&lt;?php</span></span><span>
</span><span><span class="hljs-keyword">try</span></span><span> {
    </span><span><span class="hljs-comment">// 數據庫連接</span></span><span>
    </span><span><span class="hljs-variable">$pdo</span></span><span> = </span><span><span class="hljs-keyword">new</span></span><span> </span><span><span class="hljs-title function_ invoke__">PDO</span></span><span>(</span><span><span class="hljs-string">'mysql:host=localhost;dbname=database_name'</span></span><span>, </span><span><span class="hljs-string">'username'</span></span><span>, </span><span><span class="hljs-string">'password'</span></span><span>);
    </span><span><span class="hljs-variable">$pdo</span></span><span>-&gt;</span><span><span class="hljs-title function_ invoke__">setAttribute</span></span><span>(PDO::</span><span><span class="hljs-variable constant_">ATTR_ERRMODE</span></span><span>, PDO::</span><span><span class="hljs-variable constant_">ERRMODE_EXCEPTION</span></span><span>);

    </span><span><span class="hljs-comment">// 假設我們要刪除ID為某個用戶提供的值的記錄</span></span><span>
    </span><span><span class="hljs-variable">$user_id</span></span><span> = </span><span><span class="hljs-variable">$_GET</span></span><span>[</span><span><span class="hljs-string">'id'</span></span><span>];  </span><span><span class="hljs-comment">// 從URL中獲取用戶提供的ID</span></span><span>

    </span><span><span class="hljs-comment">// 使用準備好的語句</span></span><span>
    </span><span><span class="hljs-variable">$stmt</span></span><span> = </span><span><span class="hljs-variable">$pdo</span></span><span>-&gt;</span><span><span class="hljs-title function_ invoke__">prepare</span></span><span>(</span><span><span class="hljs-string">"DELETE FROM users WHERE id = :id"</span></span><span>);
    </span><span><span class="hljs-variable">$stmt</span></span><span>-&gt;</span><span><span class="hljs-title function_ invoke__">bindParam</span></span><span>(</span><span><span class="hljs-string">':id'</span></span><span>, </span><span><span class="hljs-variable">$user_id</span></span><span>, PDO::</span><span><span class="hljs-variable constant_">PARAM_INT</span></span><span>);

    </span><span><span class="hljs-comment">// 執行語句</span></span><span>
    </span><span><span class="hljs-variable">$stmt</span></span><span>-&gt;</span><span><span class="hljs-title function_ invoke__">execute</span></span><span>();

    </span><span><span class="hljs-comment">// 檢查是否刪除成功</span></span><span>
    </span><span><span class="hljs-keyword">if</span></span><span> (</span><span><span class="hljs-variable">$stmt</span></span><span>-&gt;</span><span><span class="hljs-title function_ invoke__">rowCount</span></span><span>() &gt; </span><span><span class="hljs-number">0</span></span><span>) {
        </span><span><span class="hljs-keyword">echo</span></span><span> </span><span><span class="hljs-string">"記錄已刪除。"</span></span><span>;
    } </span><span><span class="hljs-keyword">else</span></span><span> {
        </span><span><span class="hljs-keyword">echo</span></span><span> </span><span><span class="hljs-string">"未找到該記錄或刪除失敗。"</span></span><span>;
    }

} </span><span><span class="hljs-keyword">catch</span></span><span> (PDOException </span><span><span class="hljs-variable">$e</span></span><span>) {
    </span><span><span class="hljs-keyword">echo</span></span><span> </span><span><span class="hljs-string">"錯誤: "</span></span><span> . </span><span><span class="hljs-variable">$e</span></span><span>-&gt;</span><span><span class="hljs-title function_ invoke__">getMessage</span></span><span>();
}
</span><span><span class="hljs-meta">?&gt;</span></span><span>
</span></span>

在PDO中， bindParam()用於將用戶輸入的參數綁定到SQL語句中，並通過PDO::PARAM_INT指定其類型為整數類型。這樣也可以防止SQL注入。

2. 使用正則表達式或過濾器驗證用戶輸入

除了使用準備好的語句，另一個有效的安全防護措施是對用戶輸入進行驗證和過濾。在刪除操作中，尤其是對於ID字段，必須確保其為合法的整數。

 <span><span><span class="hljs-meta">&lt;?php</span></span><span>
</span><span><span class="hljs-variable">$user_id</span></span><span> = </span><span><span class="hljs-variable">$_GET</span></span><span>[</span><span><span class="hljs-string">'id'</span></span><span>];

</span><span><span class="hljs-comment">// 驗證輸入是否為合法整數</span></span><span>
</span><span><span class="hljs-keyword">if</span></span><span> (!</span><span><span class="hljs-title function_ invoke__">filter_var</span></span><span>(</span><span><span class="hljs-variable">$user_id</span></span><span>, FILTER_VALIDATE_INT)) {
    </span><span><span class="hljs-keyword">die</span></span><span>(</span><span><span class="hljs-string">"無效的用戶ID"</span></span><span>);
}

</span><span><span class="hljs-comment">// 進行數據庫刪除操作</span></span><span>
</span><span><span class="hljs-meta">?&gt;</span></span><span>
</span></span>

使用filter_var()函數驗證用戶提供的ID是否為合法的整數，如果不是，就直接停止執行。這種額外的驗證可以提高安全性。

3. 使用最小權限原則

在與數據庫交互時，始終遵循最小權限原則。數據庫賬戶應僅具有執行必要操作的權限。例如，刪除操作應該只允許特定的數據庫賬戶進行，而不是賦予數據庫賬戶全權限。這樣，即便出現了SQL注入漏洞，攻擊者能執行的操作也將受到限制。

4. 使用SQL日誌和監控

為了能夠及時發現潛在的SQL注入攻擊，建議啟用數據庫的SQL日誌功能，並對數據庫進行監控。這有助於在攻擊發生時追踪異常活動，快速採取防範措施。

5. 定期更新和補丁

最後，保持PHP、數據庫和所有相關組件的更新是防止安全漏洞的關鍵。經常檢查安全公告，確保所有系統都打上了最新的安全補丁。

相關標籤:
SQL