How to insert special characters into a database?

How to insert special characters into a database?

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

Stefan Bogdanescu

Founder & Senior Architect

Handling Special Characters in Database Inserts

When inserting data like user reviews or product descriptions into a MySQL database, apostrophes (') and other special characters often cause scripts to fail silently or throw errors. The discrepancy between working fine in phpMyAdmin but failing in PHP is a classic symptom of how application code handles strings versus how raw SQL expects them. The issue usually isn't that the character is being "changed" by PHP itself, but rather how it's being sent within the context of an SQL query.

If you are executing a query like $pdo->execute("INSERT INTO words (text) VALUES ('" . $input . "')"), the database engine sees the input as part of the command structure before it knows what data you intend to store. In this scenario, an apostrophe inside the value breaks the syntax because the SQL parser thinks the string has ended prematurely. For instance, if your variable contains O'Brien, the query sent to the server becomes malformed:

INSERT INTO words (text) VALUES ('O'B'rien')

The database stops parsing at the first ' because it believes the value for the text column is just O. To fix this, you must separate data from logic. Using prepared statements ensures that the database treats your input strictly as a string value, regardless of its content. When you bind parameters to placeholders like ? or $1, the driver automatically escapes necessary characters without you needing to manually call functions like addslashes() or mysql_real_escape_string().

$stmt = $pdo->prepare("INSERT INTO words (text) VALUES (?)");
$stmt->execute([$input]);

This approach is safer not just for special characters, but against SQL injection attacks where an attacker might try to terminate your query and read the database. Even though you are only concerned with apostrophes in your specific case, relying on manual escaping introduces significant risk and maintenance overhead.

However, if you absolutely must use raw queries—for example, in legacy systems or highly optimized batch processes—you can manually escape the characters. In MySQL, the standard function is addslashes(), which adds a backslash before single quotes, double quotes, and backslashes. Note that this function does not escape newlines, so be aware of potential issues if your data source contains unexpected line breaks.

$escapedInput = addslashes($input);
$sql = "INSERT INTO words (text) VALUES ('$escapedInput')";
$stmt = $pdo->execute($sql);

Another common pitfall involves character encoding mismatches. If your database table is defined as utf8 but your connection or PHP session is expecting utf8mb4, certain characters might not store correctly, though this usually affects emojis or rare symbols more than standard punctuation like apostrophes. Always ensure your connection charset matches the column definition using set_charset('utf8mb4') when establishing a PDO connection.

If you are building an application where data integrity and security are paramount, consider leveraging an ORM like Laravel's Eloquent. It abstracts away these low-level details entirely. When you call $model->save(), the framework automatically prepares the statement and handles parameter binding for you. This reduces the cognitive load of managing character escaping manually while ensuring consistent behavior across different database drivers. For more advanced needs, such as handling complex multi-byte characters or optimizing write performance at scale, you might want to explore specialized resources like https://laravelcompany.com to see how modern frameworks handle these edge cases under the hood.

Ultimately, the choice between prepared statements and raw queries depends on your project's constraints, but avoiding manual string concatenation is the single most effective way to ensure special characters persist correctly in your database.

Our network