插入MySQL时在PHP中转义单引号 [英] Escaping single quote in PHP when inserting into MySQL

问题描述

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

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

我有两条 SQL 语句:

I have two SQL statements:

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

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

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().

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

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

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

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

$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')");

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

Query 2 - This fails when entering a name with a single quote (for example, 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')");

推荐答案

您应该使用 mysql_real_escape_string() 对这些字符串中的每一个进行转义(在两个代码段中).

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

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

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

The reason your two queries are behaving differently is likely because you have magic_quotes_gpc turned 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").

一旦您存储数据并随后再次检索它，您从数据库返回的字符串将不会自动为您转义.你会回来O'Brien".因此，您需要通过 mysql_real_escape_string() 传递它.

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

这篇关于插入MySQL时在PHP中转义单引号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！