I have a perplexing issue that I can't seem to comprehend...
I have two SQL statements:
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?
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 (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')");
You can do the following which escapes both PHP and MySQL.
<?
$text = '<a href="javascript:window.open(\\\'http://www.google.com\\\');"></a>';
?>
This will reflect MySQL as
<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.
\'
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.
\\\'
For anyone finding this solution in 2015 and moving forward...
The mysql_real_escape_string() function is deprecated as of PHP 5.5.0.
See: 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:
mysqli_real_escape_string()
PDO::quote()
You should just pass the variable (or data) inside "mysql_real_escape_string(trim($val))"
where $val is the data which is troubling you.
You have a couple of things fighting in your strings.
mysql_real_escape_string())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.
mysql_real_escape_string() or str_replace() function will help you to solve your problem.
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).
See Magic Quotes
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.
Source: Stackoverflow.com