How to Avoid MySQL Errors When Inserting Data Containing Single Quotes in PHP?

Escaping Single Quotes in PHP MySQL Inserts

Background:

MySQL queries involve using strings that may contain special characters, such as single quotes. These special characters must be escaped to prevent MySQL from misinterpreting them as part of the query.

Problem:

A developer encounters an issue where inserting data into a MySQL database fails due to a single quote trigger a MySQL error. The issue occurs in a second query where data is retrieved from the database and used in an email template.

Cause and Solution:

The culprit is the lack of proper escaping when inserting data into MySQL. The second query retrieves data from the database, which may include single quotes. These single quotes are not escaped when used in the email template, leading to the MySQL error.

To resolve this issue, use mysql_real_escape_string() to escape all strings before inserting them into MySQL. This function escapes special characters, including single quotes. By using this function, both queries will operate correctly, regardless of the presence of single quotes in the data.

<code class="php">$escaped_order_id = mysql_real_escape_string($order_id);
$escaped_message_content = mysql_real_escape_string($message_content);

$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
('$escaped_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 = mysql_query("INSERT INTO message_log
(order_id, timestamp, message_type, email_from, supplier_id, primary_contact, secondary_contact, subject, message_content, status)
VALUES
('$escaped_order_id', '".date('Y-m-d H:i:s', time())."', '$email', '$from', '$row->supplier_id', '$row->primary_email' ,'$row->secondary_email', '$subject', '$escaped_message_content', '1')");</code>

The above is the detailed content of How to Avoid MySQL Errors When Inserting Data Containing Single Quotes in PHP?. For more information, please follow other related articles on the PHP Chinese website!