你如何在使用 php 的 sql 查询中转义引号？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3252651/
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 do you escape quotes in a sql query using php?
提问by Linto P D
I have a query
我有一个查询
$sql ="SELECT CustomerID FROM tblCustomer
WHERE EmailAddress = '".addslashes($_POST['username']) ."' AND Password = '".addslashes($_POST['password']) ."'";
// while printing, it will be
SELECT CustomerID FROM tblCustomer WHERE EmailAddress = 'test@ab\'c.com' AND Password = '123'
if we executing this in a mysql server it works, but not in a sql server
如果我们在 mysql 服务器中执行它，它可以工作，但不能在 sql server 中执行
what is the solution for this? . Iam using sql server
解决这个问题的方法是什么？. 我正在使用 sql server
回答by Asaph
addslashes()will escape single quotes with a leading backslash which is valid syntax in MySQL but not in MS SQL Server. The correct way to escape a single quote in MS SQL Server is with another single quote. Use mysql_real_escape_string()for MySQL (mysql_escape_string()has been deprecated). Unfortunately, no analogous mssql_function exists so you'll have to roll your own using str_replace(), preg_replace()or something similar. Better yet, use a database neutral abstraction layer such as PDO that supports parameterized queries.
addslashes()将使用前导反斜杠转义单引号，这在 MySQL 中是有效的语法，但在 MS SQL Server 中无效。在 MS SQL Server 中转义单引号的正确方法是使用另一个单引号。使用mysql_real_escape_string()MySQL的（mysql_escape_string()已弃用）。不幸的是，没有类似的mssql_函数存在，所以你必须使用推出自己的str_replace()，preg_replace()或类似的东西。更好的是，使用支持参数化查询的数据库中立抽象层，例如 PDO。
回答by cHao
For MySQL, you want to use mysql_real_escape_string. addslashesdoes almost the same thing and has fewer letters, but apparently it gets some stuff wrong -- don't use it.
对于 MySQL，您希望使用mysql_real_escape_string. addslashes做几乎相同的事情并且有更少的字母，但显然它弄错了一些东西——不要使用它。
For SQL Server, it's a bit more complicated, as (1) MySQL quotes stuff non-standardly, and (2) i don't see a function made to quote stuff for SQL Server. However, the following should work for you...
对于 SQL Server，它有点复杂，因为 (1) MySQL 非标准地引用内容，并且 (2) 我没有看到为 SQL Server 引用内容的函数。但是，以下内容应该适合您...
$escaped_str = str_replace("'", "''", $unsafe_str);
回答by Haim Evgi
for mysql
用于mysql
USE mysql_real_escape_string
用 mysql_real_escape_string
http://php.net/manual/en/function.mysql-real-escape-string.php
http://php.net/manual/en/function.mysql-real-escape-string.php
like :
喜欢 ：
// Query
$query = sprintf("SELECT * FROM tblCustomer WHERE user='%s' AND password='%s'",
mysql_real_escape_string($user),
mysql_real_escape_string($password));
for mssql
对于mssql
look on the answers here :
看看这里的答案：
回答by Joel Mansford
You shouldn't really be building the SQL statement dynamically as it's dangerous (and unnecessary). The correct thing to do is to use a paramerised query see http://msdn.microsoft.com/en-us/library/cc296201%28SQL.90%29.aspx
您不应该真正动态地构建 SQL 语句，因为它很危险（而且没有必要）。正确的做法是使用参数化查询，请参阅http://msdn.microsoft.com/en-us/library/cc296201%28SQL.90%29.aspx
$sql ="SELECT CustomerID FROM tblCustomer WHERE EmailAddress = ? AND Password = ?";
$stmt = sqlsrv_query( $conn, $sql, array($_POST['username'], $_POST['password']));
This is much safer and means you don't have to worry about escaping characters. Another thing is beware of case sensitive / insensitve comparisons. For example if you wanted email address to be case insensitive but password case sensitive use something like:
这更安全，意味着您不必担心转义字符。另一件事是注意区分大小写/不敏感的比较。例如，如果您希望电子邮件地址不区分大小写但密码区分大小写，请使用以下内容：
$sql ="SELECT CustomerID FROM tblCustomer WHERE EmailAddress = ? COLLATE SQL_Latin1_General_CP1_CIAI AND Password = ? COLLATE SQL_Latin1_General_CP1_CSAS";
