插入到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屋!