使用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