php 插入MySQL时在PHP中转义单引号

Escaping single quote in PHP when inserting into MySQL

提问by sjw

I have a perplexing issue that I can't seem to comprehend...

我有一个令人困惑的问题，我似乎无法理解...

I have two SQL statements:

我有两个 SQL 语句：

• The first enters information from a form into the database.
• The second takes data from the database entered above, sends an email, and then logs the details of the transaction

• 第一个将信息从表单输入到数据库中。
• 第二个从上面输入的数据库中获取数据，发送电子邮件，然后记录交易的详细信息

The problem is that it appears that a single quote is triggering a MySQL error on the second entry only! The first instance works without issue, but the second instance triggers the mysql_error().

问题是单引号似乎仅在第二个条目上触发了 MySQL 错误！第一个实例正常工作，但第二个实例触发mysql_error().

Does the data from a form get handled differently from the data captured in a form?

表单中的数据与表单中捕获的数据的处理方式是否不同？

Query 1 - This works without issue (and without escaping the single quote)

查询 1 - 这没有问题（并且没有转义单引号）

$result = mysql_query("INSERT INTO job_log
(order_id, supplier_id, category_id, service_id, qty_ordered, customer_id, user_id, salesperson_ref, booking_ref, booking_name, address, suburb, postcode, state_id, region_id, email, phone, phone2, mobile, delivery_date, stock_taken, special_instructions, cost_price, cost_price_gst, sell_price, sell_price_gst, ext_sell_price, retail_customer, created, modified, log_status_id)
VALUES
('$order_id', '$supplier_id', '$category_id', '{$value['id']}', '{$value['qty']}', '$customer_id', '$user_id', '$salesperson_ref', '$booking_ref', '$booking_name', '$address', '$suburb', '$postcode', '$state_id', '$region_id', '$email', '$phone', '$phone2', '$mobile', STR_TO_DATE('$delivery_date', '%d/%m/%Y'), '$stock_taken', '$special_instructions', '$cost_price', '$cost_price_gst', '$sell_price', '$sell_price_gst', '$ext_sell_price', '$retail_customer', '".date('Y-m-d H:i:s', time())."', '".date('Y-m-d H:i:s', time())."', '1')");

Query 2 - This fails when entering a name with a single quote (for example, O'Brien)

查询 2 - 输入带有单引号的名称时失败（例如，O'Brien）

$query = mysql_query("INSERT INTO message_log
(order_id, timestamp, message_type, email_from, supplier_id, primary_contact, secondary_contact, subject, message_content, status)
VALUES
('$order_id', '".date('Y-m-d H:i:s', time())."', '$email', '$from', '$row->supplier_id', '$row->primary_email' ,'$row->secondary_email', '$subject', '$message_content', '1')");

采纳答案by awgy

You should be escaping each of these strings (in both snippets) with mysql_real_escape_string().

您应该使用mysql_real_escape_string().

http://us3.php.net/mysql-real-escape-string

http://us3.php.net/mysql-real-escape-string

The reason your two queries are behaving differently is likely because you have magic_quotes_gpcturned on (which you should know is a bad idea). This means that strings gathered from $_GET, $_POST and $_COOKIES are escaped for you (i.e., "O'Brien" -> "O\'Brien").

您的两个查询行为不同的原因可能是因为您已magic_quotes_gpc打开（您应该知道这是一个坏主意）。这意味着从 $_GET、$_POST 和 $_COOKIES 收集的字符串将为您转义（即，"O'Brien" -> "O\'Brien"）。

Once you store the data, and subsequently retrieve it again, the string you get back from the database will notbe automatically escaped for you. You'll get back "O'Brien". So, you will need to pass it through mysql_real_escape_string().

一旦您存储了数据并随后再次检索它，您从数据库中取回的字符串将不会自动为您转义。你会回来的"O'Brien"。所以，你需要通过它mysql_real_escape_string()。

回答by Amy McCrobie

For anyone finding this solution in 2015 and moving forward...

对于在 2015 年找到此解决方案并继续前进的任何人...

The mysql_real_escape_string()function is deprecated as of PHP 5.5.0.

该mysql_real_escape_string()函数自 PHP 5.5.0 起已弃用。

See: php.net

参见：php.net

Warning

警告

This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information. Alternatives to this function include:

这个扩展从 PHP 5.5.0 开始被弃用，将来会被删除。相反，应使用 MySQLi 或 PDO_MySQL 扩展。另请参阅 MySQL：选择 API 指南和相关常见问题以了解更多信息。此功能的替代方案包括：

mysqli_real_escape_string()

mysqli_real_escape_string()

PDO::quote()

PDO::quote()

回答by goat

You should do something like this to help you debug

你应该做这样的事情来帮助你调试

$sql = "insert into blah values ('$myVar')";
echo $sql;

You will probably find that the single quote is escaped with a backslash in the working query. This might have been done automatically by PHP via the magic_quotes_gpc setting, or maybe you did it yourself in some other part of the code (addslashes and stripslashes might be functions to look for).

您可能会发现在工作查询中单引号用反斜杠转义。这可能是 PHP 通过 magic_quotes_gpc 设置自动完成的，或者您可能在代码的其他部分自己完成（addslashes 和 stripslashes 可能是要查找的函数）。

See Magic Quotes

见魔术行情

回答by staticsan

You have a couple of things fighting in your strings.

你有几件事情在你的弦上挣扎。

• lack of correct MySQL quoting (mysql_real_escape_string())
• potential automatic 'magic quote' -- check your gpc_magic_quotes setting
• embedded string variables, which means you have to know how PHP correctly finds variables

• 缺少正确的 MySQL 引用 ( mysql_real_escape_string())
• 潜在的自动“魔术报价”——检查您的 gpc_magic_quotes 设置
• 嵌入字符串变量，这意味着您必须知道 PHP 如何正确查找变量

It's also possible that the single-quoted value is not present in the parameters to the first query. Your example is a proper name, after all, and only the second query seems to be dealing with names.

单引号值也可能不存在于第一个查询的参数中。毕竟，您的示例是一个适当的名称，并且只有第二个查询似乎在处理名称。

回答by Error404

You can do the following which escapes both PHP and MySQL.

您可以执行以下操作来转义 PHP 和 MySQL。

<?
$text = '<a href="javascript:window.open(\\'http://www.google.com\\');"></a>';
?> 

This will reflect MySQL as

这将反映 MySQL 作为

<a href="javascript:window.open('http://www.google.com');"></a>

How does it work?

它是如何工作的？

We know that both PHP and MySQL apostrophes can be escaped with backslash and then apostrophe.

我们知道 PHP 和 MySQL 的撇号都可以用反斜杠和撇号转义。

\'

Because we are using PHP to insert into MySQL, we need PHP to still write the backslash to MySQL so it too can escape it. So we use the PHP escape character of backslash-backslash together with backslash-apostrophe to achieve this.

因为我们使用 PHP 插入 MySQL，所以我们需要 PHP 仍然将反斜杠写入 MySQL，以便它也可以转义它。所以我们使用反斜杠-反斜杠的 PHP 转义字符和反斜杠-撇号来实现这一点。

\\'

回答by user3272729

You should just pass the variable (or data) inside "mysql_real_escape_string(trim($val))"

您应该只在“mysql_real_escape_string(trim($val))”中传递变量（或数据）

where $valis the data which is troubling you.

$val困扰您的数据在哪里。

回答by user2521037

I had the same problem and I solved it like this:

我遇到了同样的问题，我是这样解决的：

$text = str_replace("'", "\'", $YourContent);

There is probably a better way to do this, but it worked for me and it should work for you too.

可能有更好的方法来做到这一点，但它对我有用，也应该对你有用。

回答by user3255636

mysql_real_escape_string() or str_replace() function will help you to solve your problem.

mysql_real_escape_string() 或 str_replace() 函数将帮助您解决问题。

http://phptutorial.co.in/php-echo-print/

http://phptutorial.co.in/php-echo-print/