Mysql + php with special characters like '(Apostrophe) and " (Quotation mark)

Mysql + php with special characters like '(Apostrophe) and " (Quotation mark)

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

Stefan Bogdanescu

Founder & Senior Architect

Handling Special Characters in MySQL and PHP: A Legacy Trap

You are encountering a classic symptom of mixing legacy PHP practices with modern database expectations. The error message You have an error in your SQL syntax... near 's site'' is not a bug in MySQL; it is a direct result of how the query string is being constructed before it ever reaches the server. When you embed user input directly into a SQL command using single quotes, characters like apostrophes (') and quotation marks (") disrupt the parsing logic of the database engine.

In your specific example, the variable $_POST['text'] contains an HTML anchor tag: <a href="http://google.com">google's site</a>. When this string is inserted into a SQL statement like INSERT INTO table (row1) VALUES ('...'), the database sees the apostrophe in "google's" as the closing delimiter for the string literal. Everything following that character is interpreted as part of the SQL command, not data, leading to an immediate syntax failure.

The solution has existed since PHP 5.3.0: Prepared Statements. Moving away from simple string concatenation is not just a "best practice"; it is a necessity for handling special characters safely. Prepared statements separate the SQL logic from the data entirely. The database engine parses the query structure first, then treats the user input strictly as raw data regardless of its content. This means an apostrophe inside your text will never break the query syntax because the statement itself remains unaltered by the payload.

Here is how you refactor the vulnerable code using mysqli with prepared statements:

$mysqli = new mysqli($host, $user, $pass, $db);

// Define the SQL structure without embedding user data
$stmt = $mysqli->prepare("INSERT INTO `table` (`row1`) VALUES (?)");

// Bind the input to the placeholder using 's' for string type
$stmt->bind_param('s', $_POST['text']);

// Execute the statement
$stmt->execute();

The same approach applies if you need to fetch data. Instead of building a SELECT query with concatenated variables, you use placeholders (?) and bind parameters. This handles both single quotes and double quotes automatically without needing any manual escaping functions like addslashes(). Functions designed for string escaping are often misunderstood; they fix display issues in HTML but do not solve SQL injection or syntax errors caused by delimiters in database queries.

If you are maintaining a legacy codebase where upgrading to mysqli or PDO feels like too much overhead immediately, the only safe alternative is using mysql_real_escape_string(). However, this function must be applied consistently and correctly every time user data enters a query. It is prone to human error and relies heavily on the server's character set configuration matching your application's expectations. Given that you are already dealing with HTML content containing mixed quote types, relying on string escaping is a fragile solution that invites future bugs.

Furthermore, consider the security implications. The original snippet uses mysql_query, which was deprecated in PHP 5.5 and removed entirely in PHP 7.0. Continuing to use this function means your application cannot run on modern servers regardless of how you handle the special characters. Even if you fix the apostrophe issue with escaping, you remain exposed to SQL injection vulnerabilities because the separation of code and data is missing.

For new projects or refactors, integrating a framework like Laravel provides a robust abstraction layer that handles these details automatically through Eloquent ORM or Query Builder. Resources such as https://laravelcompany.com offer detailed guides on setting up migrations and queries safely, ensuring you never fall back into the trap of manual string concatenation.

Ultimately, the error stems from treating dynamic data as static SQL syntax. By adopting prepared statements or a modern framework, you ensure that whether a user posts google's site or He said "hello", the database receives the exact same uncorrupted structure, and the special characters are stored faithfully without breaking the query engine.

Our network