How Can I Properly Escape Single Quotes in PHP to Prevent MySQL Query Errors?

Escaping Single Quotes in PHP for MySQL Queries

Understanding the Issue

When attempting to insert data into MySQL using PHP, a single quote character can cause an error in subsequent queries that access the inserted data. Although the first query seems to work correctly, the second query results in a MySQL error if the data contains a single quote.

Resolving the Problem

The solution is to escape the single quote characters in the inserted strings using the mysql_real_escape_string() function. This function replaces single quotes with their escaped counterparts ('), ensuring that they are interpreted as literal characters instead of as part of the query syntax.

Reason for the Disparity

The reason the two queries behave differently is likely due to the setting of the magic_quotes_gpc configuration parameter. When this setting is enabled, strings obtained from $_GET, $_POST, and $_COOKIES are automatically escaped, including single quotes.

However, once the data is stored in the database and retrieved again, it will not be automatically escaped. Therefore, when the second query attempts to access the data, it encounters single quotes that are not escaped and triggers an error.

Revised Queries

To resolve the issue, escape the single quotes in both queries using mysql_real_escape_string():

$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', mysql_real_escape_string('$booking_name'), mysql_real_escape_string('$address'), mysql_real_escape_string('$suburb'), mysql_real_escape_string('$postcode'), '$state_id', '$region_id', mysql_real_escape_string('$email'), mysql_real_escape_string('$phone'), mysql_real_escape_string('$phone2'), mysql_real_escape_string('$mobile'), STR_TO_DATE('$delivery_date', '%d/%m/%Y'), '$stock_taken', mysql_real_escape_string('$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 = 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', mysql_real_escape_string('$from'), '$row->supplier_id', mysql_real_escape_string('$row->primary_email') ,mysql_real_escape_string('$row->secondary_email'), mysql_real_escape_string('$subject'), mysql_real_escape_string('$message_content'), '1')");

By escaping the single quotes, both queries will now process data containing single quotes correctly without causing MySQL errors.

The above is the detailed content of How Can I Properly Escape Single Quotes in PHP to Prevent MySQL Query Errors?. For more information, please follow other related articles on the PHP Chinese website!