如何使用 PHP 在 SQL Server 中转义字符串？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/574805/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to escape strings in SQL Server using PHP?
提问by Click Upvote
I'm looking for the alternative of mysql_real_escape_string()for SQL Server. Is addslashes()my best option or there is another alternative function that can be used?
我正在寻找mysql_real_escape_string()SQL Server的替代方案。是addslashes()我最好的选择还是可以使用其他替代功能？
An alternative for mysql_error()would also be useful.
for 的替代方法mysql_error()也很有用。
采纳答案by chaos
addslashes()isn't fully adequate, but PHP's mssql package doesn't provide any decent alternative. The ugly but fully general solution is encoding the data as a hex bytestring, i.e.
addslashes()不完全足够，但 PHP 的 mssql 包没有提供任何体面的替代方案。丑陋但完全通用的解决方案是将数据编码为十六进制字节串，即
$unpacked = unpack('H*hex', $data);
mssql_query('
INSERT INTO sometable (somecolumn)
VALUES (0x' . $unpacked['hex'] . ')
');
Abstracted, that would be:
抽象，那将是：
function mssql_escape($data) {
if(is_numeric($data))
return $data;
$unpacked = unpack('H*hex', $data);
return '0x' . $unpacked['hex'];
}
mssql_query('
INSERT INTO sometable (somecolumn)
VALUES (' . mssql_escape($somevalue) . ')
');
mysql_error()equivalent is mssql_get_last_message().
mysql_error()等价的是mssql_get_last_message()。
回答by genio
function ms_escape_string($data) {
if ( !isset($data) or empty($data) ) return '';
if ( is_numeric($data) ) return $data;
$non_displayables = array(
'/%0[0-8bcef]/', // url encoded 00-08, 11, 12, 14, 15
'/%1[0-9a-f]/', // url encoded 16-31
'/[\x00-\x08]/', // 00-08
'/\x0b/', // 11
'/\x0c/', // 12
'/[\x0e-\x1f]/' // 14-31
);
foreach ( $non_displayables as $regex )
$data = preg_replace( $regex, '', $data );
$data = str_replace("'", "''", $data );
return $data;
}
Some of the code here was ripped off from CodeIgniter. Works well and is a clean solution.
这里的一些代码是从 CodeIgniter 中抄袭来的。效果很好，是一个干净的解决方案。
EDIT: There are plenty of issues with that code snippet above. Please don't use this without reading the comments to know what those are. Better yet, please don't use this at all. Parameterized queries are your friends: http://php.net/manual/en/pdo.prepared-statements.php
编辑：上面的代码片段有很多问题。请不要在没有阅读评论以了解它们是什么的情况下使用它。更好的是，请根本不要使用它。参数化查询是您的朋友：http: //php.net/manual/en/pdo.prepared-statements.php
回答by Konstantin
Why would you bother escaping anything when you can use parameters in your query?!
当您可以在查询中使用参数时，为什么还要费心转义？！
sqlsrv_query(
$connection,
'UPDATE some_table SET some_field = ? WHERE other_field = ?',
array($_REQUEST['some_field'], $_REQUEST['id'])
)
It works right in selects, deletes, updates regardless whether your values parameters are nullor not.
Make a matter of principle - Don't concatenate SQL and you are always safe and your queries read much better.
无论您的值参数是否存在，它都可以在选择、删除、更新中null正常工作。做一个原则问题 - 不要连接 SQL，你总是安全的，你的查询读得更好。
回答by alex
You could look into the PDO Library. You can use prepared statements with PDO, which will automatically escape any bad characters in your strings if you do the prepared statements correctly. This is for PHP 5 only I think.
您可以查看PDO 库。您可以将准备好的语句与 PDO 一起使用，如果您正确地执行准备好的语句，它将自动转义字符串中的任何错误字符。我认为这仅适用于 PHP 5。
回答by Raja Bilal Ahmed
Another way to handle single and double quotes is:
处理单引号和双引号的另一种方法是：
function mssql_escape($str)
{
if(get_magic_quotes_gpc())
{
$str = stripslashes($str);
}
return str_replace("'", "''", $str);
}
回答by Alejandro García Iglesias
After struggling with this for hours, I've come up with a solution that feels almost the best.
在为此挣扎了几个小时之后，我想出了一个感觉几乎是最好的解决方案。
Chaos' answer of converting values to hexstring doesn't work with every datatype, specifically with datetime columns.
Chaos 将值转换为 hexstring 的答案不适用于每种数据类型，特别是日期时间列。
I use PHP's PDO::quote(), but as it comes with PHP, PDO::quote()is not supported for MS SQL Server and returns FALSE. The solution for it to work was to download some Microsoft bundles:
我使用 PHP 的PDO::quote()，但由于它与 PHP 一起PDO::quote()提供，MS SQL Server 不支持并返回FALSE. 它工作的解决方案是下载一些 Microsoft 捆绑包：
- Microsoft Drivers 3.0 for PHP for SQL Server(SQLSRV30.EXE): Download and follow the instructions to install.
- Microsoft? SQL Server? 2012 Native Client: Search through the extensive page for the Native Client. Even though it's 2012, I'm using it to connect to SQL Server 2008 (installing the 2008 Native Client didn't worked).Download and install.
- Microsoft Drivers 3.0 for PHP for SQL Server(SQLSRV30.EXE)：下载并按照说明进行安装。
- 微软？SQL服务器？2012 Native Client：搜索大量的 Native Client 页面。即使是 2012 年，我也使用它连接到 SQL Server 2008（安装 2008 Native Client 不起作用）。下载并安装。
After that you can connect in PHP with PDO using a DSN like the following example:
之后，您可以使用 DSN 使用 PDO 在 PHP 中连接，如下例所示：
sqlsrv:Server=192.168.0.25; Database=My_Database;
Using the UIDand PWDparameters in the DSN didn't worked, so username and password are passed as the second and third parameters on the PDO constructor when creating the connection.
Now you can use PHP's PDO::quote(). Enjoy.
在 DSN 中使用UID和PWD参数不起作用，因此在创建连接时，用户名和密码作为 PDO 构造函数的第二个和第三个参数传递。现在您可以使用 PHP 的PDO::quote(). 享受。
回答by marklark
In order to escape single- and double-quotes, you have to double them up:
为了逃避单引号和双引号，您必须将它们加倍：
$value = 'This is a quote, "I said, 'Hi'"';
$value = str_replace( "'", "''", $value );
$value = str_replace( '"', '""', $value );
$value = str_replace( '"', '""', $value );
$query = "INSERT INTO TableName ( TextFieldName ) VALUES ( '$value' ) ";
etc...
等等...
and attribution: Escape Character In Microsoft SQL Server 2000
回答by danechkin
An answer from 2009-02-22T121000 by user chaosdoesn't fit all queries.
用户混乱2009-02-22T121000 的答案并不适合所有查询。
For example, "CREATE LOGIN [0x6f6c6f6c6f] FROM WINDOWS" will give you an exception.
例如，“CREATE LOGIN [0x6f6c6f6c6f] FROM WINDOWS”会给你一个例外。
PS: look at the SQL Server driver for PHP, http://msdn.microsoft.com/library/cc296181%28v=sql.90%29.aspxand the sqlsrv_prepare function, which can binds parameters.
PS：看PHP的SQL Server驱动，http://msdn.microsoft.com/library/cc296181%28v=sql.90%29.aspx和sqlsrv_prepare函数，可以绑定参数。
PSS: Which also didn't help you with the query above ;)
PSS：上面的查询也没有帮助你;)
回答by Alex360
It is better to also escape SQL reserved words. For example:
最好也转义 SQL 保留字。例如：
function ms_escape_string($data) {
if (!isset($data) or empty($data))
return '';
if (is_numeric($data))
return $data;
$non_displayables = array(
'/%0[0-8bcef]/', // URL encoded 00-08, 11, 12, 14, 15
'/%1[0-9a-f]/', // url encoded 16-31
'/[\x00-\x08]/', // 00-08
'/\x0b/', // 11
'/\x0c/', // 12
'/[\x0e-\x1f]/', // 14-31
'//'
);
foreach ($non_displayables as $regex)
$data = preg_replace( $regex, '', $data);
$reemplazar = array('"', "'", '=');
$data = str_replace($reemplazar, "*", $data);
return $data;
}
回答by jjwdesign
Warning: This function was REMOVED in PHP 7.0.0.
警告：该函数在 PHP 7.0.0 中被移除。
http://php.net/manual/en/function.mssql-query.php
http://php.net/manual/en/function.mssql-query.php
For anyone still using these mssql_* functions, keep in mind that they have been removed from PHP as of v7.0.0. So, that means you eventually have to rewrite your model code to either use the PDO library, sqlsrv_* etc. If you're looking for something with a "quoting/escaping" method, I would recommend PDO.
对于仍在使用这些 mssql_* 函数的任何人，请记住，从 v7.0.0 开始，它们已从 PHP 中删除。因此，这意味着您最终必须重写模型代码以使用 PDO 库、sqlsrv_* 等。如果您正在寻找具有“引用/转义”方法的东西，我会推荐 PDO。
Alternatives to this function include: PDO::query(), sqlsrv_query() and odbc_exec()
此函数的替代方法包括：PDO::query()、sqlsrv_query() 和 odbc_exec()
