How to Safely Insert Single Quotes into MySQL Databases Using PHP?

Escaping Single Quotes in PHP for MySQL Insertion

When inserting data into a MySQL database using PHP, it is crucial to escape any single quotes (') in the data to prevent unexpected behavior. A common issue arises when using single quotes within a MySQL statement and encountering errors in the second insertion attempt.

Query 1

The first query, which inserts information from a form into the job_log table, works correctly because the single quote in the booking_name field is not explicitly escaped. However, this is not a recommended practice.

$result = mysql_query("INSERT INTO job_log
(order_id, ...)
VALUES
('$order_id', '$supplier_id', ..., '$booking_name', '$address', ...)");

Query 2

The second query, which inserts data from the database into the message_log table, fails when the primary_contact field contains a single quote. This is because the single quote in the row->primary_email string triggers a MySQL syntax error.

$query = mysql_query("INSERT INTO message_log
(order_id, ...)
VALUES
('$order_id', '".date('Y-m-d H:i:s', time())."', '$email', '$from', ..., '$row->primary_email' ,..., '$message_content', '1')");

Escaping Solution using mysql_real_escape_string

To resolve this issue, it is essential to properly escape any single quotes in the string values being inserted into the database. This can be achieved using the mysql_real_escape_string() function, which escapes the string to comply with MySQL syntax.

$esc_primary_email = mysql_real_escape_string($row->primary_email);

$query = mysql_query("INSERT INTO message_log
(order_id, ...)
VALUES
('$order_id', '".date('Y-m-d H:i:s', time())."', '$email', '$from', ..., '$esc_primary_email' ,..., '$message_content', '1')");

By escaping the single quote using mysql_real_escape_string(), the query will execute successfully without encountering errors, ensuring the correct insertion of the data into the database.

The above is the detailed content of How to Safely Insert Single Quotes into MySQL Databases Using PHP?. For more information, please follow other related articles on the PHP Chinese website!