Escaping single quote in PHP when inserting into MySQL

Question

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

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

Answer 2

You should do something like this to help you debug

$sql = "insert into blah blah....";
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).

See http://php.net/manual/en/security.magicquotes.php

Answer 3

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

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.