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

问题描述

我有一个令人困惑的问题，我似乎无法理解...我希望有人能够指向正确的方向...

我有两个SQL语句：
- 首先将数据从表单输入数据库。
- 第二个从上面输入的数据库中获取数据，发送一封电子邮件，然后记录事务的细节。

问题是，单引号只触发第二个条目的MySQL错误！第一个实例没有问题，但第二个实例触发mysql_error（）。

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

查询＃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，vicinity，postcode，state_id，region_id，email，phone ，$ phone $，$ _ $，$，$，$，$， $ category_id'，'{$ value ['id']}'，'{$ value ['qty']}'，'$ customer_id'，'$ user_id'，'$ salesperson_ref'，'$ booking_ref' booking_name'，'$ address'，'$郊区，'$ postcode'，'$ state_id'，'$ region_id'，'$ email'，'$ phone'，'$ phone2'，'$ mobile'，STR_TO_DATE（'$ delivery_date'，'％d /％m / ％$'），'$ stock_'，'$ cost_price'，' ''，''.date（'Ymd H：i：s'，time（））。''，'1'））; 
  

查询＃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（'Ymd H：i：s'，time（））'，'$ email' $ from'，'$ row-> supplier_id'，'$ row-> primary_email'，'$ row-> secondary_email'，'$ subject'，'$ message_content'，'1'））; 
  

解决方案

你应该转义这些字符串

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

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

一旦您存储数据，然后再次检索，您从数据库返回的字符串将不会自动转义为您。你会收回O'Brien。所以，你需要通过 mysql_real_escape_string（）。

I have a perplexing issue that I can't seem to comprehend... I'm hoping someone here might be able to point me in the right direction...

I have two SQL statements: - 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 a 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?

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

Query#2 - This fails when entering a name with a single quote (i.e. 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')");

解决方案

You should be escaping each of these strings (in both snippets) with 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_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").

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屋！