Escaping single quote in PHP when inserting into MySQL

Escaping single quote in PHP when inserting into MySQL

  1. Blog
  2. 15 November 2026
  3. 3 min read

Stefan Bogdanescu

Founder & Senior Architect

Escaping single quote in PHP when inserting into MySQL

It is a common misconception that data entering a database behaves differently depending on its origin. Whether a string comes from $_POST, a hardcoded array, or a previous query result, the underlying bytes are identical. If Query 1 works without explicit escaping but Query 2 fails with a single quote in a name like "O'Brien," the issue is not about data provenance; it is strictly about how the application handles variable interpolation within SQL strings.

The root cause here is unescaped string concatenation. In both examples provided, variables are directly inserted into the query string using double quotes in PHP: "$email" or '$message_content'. This approach assumes that the content of these variables is "safe." It is not. A single quote character (') inside a variable breaks the SQL syntax immediately, terminating the current string literal and treating everything following it as part of the SQL statement until the next closing quote or semicolon is found.

When you run Query 1 with data containing an apostrophe, the database engine likely defaults to a Warning level for the error if your PHP configuration allows it, or the specific context of that insert might tolerate the syntax break in a way that doesn't stop execution immediately (perhaps due to how the mysql_query function handles errors in older versions). However, Query 2 hits a hard stop because the parser encounters the unclosed string literal at the exact moment it needs to process the data. The database sees:

INSERT INTO message_log (primary_contact) VALUES ('O'Brien', ...)

It interprets this as primary_contact being set to 'O followed immediately by a syntax error because of the unexpected 'Brien', ,.

To fix this, you must separate the data from the structure. The standard function for this in legacy MySQL extensions is mysql_real_escape_string(). You should wrap every variable that originates from user input or external sources with this function before concatenating it into the query string.

Here is how Query 2 should look to handle single quotes correctly:

$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
    ('" . $db->real_escape_string($order_id) . "', 
     '" . $db->real_escape_string(date('Y-m-d H:i:s', time())) . "', 
     '" . $db->real_escape_string($message_type) . "', 
     '" . $db->real_escape_string($email) . "', 
     '" . $db->real_escape_string($row->supplier_id) . "', 
     '" . $db->real_escape_string($row->primary_email) . "', 
     '" . $db->real_escape_string($row->secondary_email) . "', 
     '" . $db->real_escape_string($subject) . "', 
     '" . $db->real_escape_string($message_content) . "', 
     '1'
    )");

Note the usage of real_escape_string(). This function adds backslashes before single quotes, double quotes, and newlines within the variable content. When $row->primary_email contains "O'Brien", the string becomes O\'Brien, which the SQL parser correctly interprets as a single character within a quoted string.

However, relying on mysql_query and manual escaping is a practice that introduces significant security risks and maintenance overhead. The legacy MySQL extension (mysql_*) was deprecated in PHP 5.5 and removed entirely in PHP 7.0. Using it means you are building applications with known vulnerabilities regarding SQL injection if the escaping logic ever fails or is bypassed.

The modern, secure approach is to use prepared statements. This method keeps your data completely separate from your SQL code. You define placeholders (?) in the query structure, and the database driver handles the escaping internally based on the type of data you provide. This eliminates the need for mysql_real_escape_string entirely and closes off the injection vector automatically.

If you are maintaining existing codebases or migrating legacy systems, understanding that "it works sometimes but not always" is a red flag indicating fragile logic. It implies your application is currently vulnerable to SQL injection attacks in any scenario where the input does not happen to contain a single quote. Transitioning to PDO or MySQLi prepared statements is the only sustainable path forward for securing your data layer.

Our network