这足以在使用 SQL Server 时防止查询注入吗? [英] Is this sufficient to prevent query injection while using SQL Server?

问题描述

我最近接手了一个需要与 PHP/SQL Server 集成的项目.我正在寻找最快和最简单的功能来防止 SQL Server 上的 SQL 注入，因为我更喜欢 MySQL，并且不希望有更多与 SQL Server 相关的项目.

I have recently taken on a project in which I need to integrate with PHP/SQL Server. I am looking for the quickest and easiest function to prevent SQL injection on SQL Server as I prefer MySQL and do not anticipate many more SQL Server related projects.

这个功能够用吗?

$someVal = mssql_escape($_POST['someVal']);

$query = "INSERT INTO tblName SET field = $someVal";

mssql_execute($query);

function mssql_escape($str) {
    return str_replace("'", "''", $str);
}

如果没有，我应该采取哪些额外的步骤?

If not, what additional steps should I take?

我在 Linux 服务器上运行 - sqlsrv_query() 仅适用于您的托管环境是 windows

I am running on a Linux server - sqlsrv_query() only works if your hosting environment is windows

推荐答案

最好的选择:不要使用串联在一起的 SQL 语句 - 使用参数化查询.

The best option: do not use SQL statements that get concatenated together - use parametrized queries.

例如不要不要创建类似

string stmt = "INSERT INTO dbo.MyTable(field1,field2) VALUES(" + value1 + ", " + value2 + ")"

或类似的东西，然后尝试通过替换单引号或其他东西来清理"它 - 你永远不会抓住一切，总有人会找到绕过你的安全保护"的方法.

or something like that and then try to "sanitize" it by replacing single quotes or something - you'll never catch everything, someone will always find a way around your "safe guarding".

改为使用:

string stmt = "INSERT INTO dbo.MyTable(field1,field2) VALUES(@value1, @value2)";

然后在执行此 INSERT 语句之前设置参数值.这确实是避免 SQL 注入的唯一可靠方法 - 使用它！

and then set the parameter values before executing this INSERT statement. This is really the only reliable way to avoid SQL injection - use it!

更新:如何使用 PHP 中的参数化查询 - 我在这里找到了一些东西 - 这有帮助吗有吗?

UPDATE: how to use parametrized queries from PHP - I found something here - does that help at all?

$tsql = "INSERT INTO DateTimeTable (myDate, myTime,
                                    myDateTimeOffset, myDatetime2)
         VALUES (?, ?, ?, ?)";

$params = array(
            date("Y-m-d"), // Current date in Y-m-d format.
            "15:30:41.987", // Time as a string.
            date("c"), // Current date in ISO 8601 format.
            date("Y-m-d H:i:s.u") // Current date and time.
          );

$stmt = sqlsrv_query($conn, $tsql, $params);

所以看起来你不能使用像@value1、@value2 这样的命名"参数，而你只是使用问号?对于每个参数，您基本上只需创建一个参数数组，然后将其传递给查询.

So it seems you can't use "named" parameters like @value1, @value2, but instead you just use question marks ? for each parameter, and you basically just create a parameter array which you then pass into the query.

本文使用 PHP 访问 SQL Server 数据库 也可能有所帮助 - 它有一个关于如何使用参数化查询插入数据的类似示例.

This article Accessing SQL Server Databases with PHP might also help - it has a similar sample of how to insert data using the parametrized queries.

更新:在您透露您使用的是 Linux 之后，这种方法不再适用.相反，您需要使用 PHP 中的替代库来调用数据库 - 类似于 PDO.

UPDATE: after you've revealed that you're on Linux, this approach doesn't work anymore. Instead, you need to use an alternate library in PHP to call a database - something like PDO.

PDO 应该适用于任何 *nix 类型的操作系统，也适用于所有类型的数据库，包括 SQL Server，并且它也支持参数化查询:

PDO should work both on any *nix type operating system, and against all sorts of databases, including SQL Server, and it supports parametrized queries, too:

$db = new PDO('your-connection-string-here');
$stmt = $db->prepare("SELECT priv FROM testUsers WHERE username=:username AND password=:password");
$stmt->bindParam(':username', $user);
$stmt->bindParam(':password', $pass);
$stmt->execute();

这篇关于这足以在使用 SQL Server 时防止查询注入吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！