Common causes
- A reserved word such as order, group, key or rank used as a table or column name without backticks
- Missing or mismatched quotes, commas or parentheses
- Values inserted into SQL by string concatenation without escaping
- Old syntax like TYPE=MyISAM instead of ENGINE=MyISAM
- SQL written for PostgreSQL, SQL Server or SQLite (for example TOP, ILIKE, double-quoted identifiers)
- A dump made on a newer server version imported into an older one
How to fix it
- Look just before the 'near' text. MySQL quotes the text starting at the point it got confused. Check the word or symbol right before it. 'near ''' at the end usually means something is unfinished, like a missing closing parenthesis.
- Backtick reserved words. Wrap table and column names that are reserved words in backticks, for example INSERT INTO `order` (`id`, `total`). Renaming the column is a cleaner long-term fix.
- Use prepared statements. Never build SQL by joining strings with user input. Use PDO or mysqli prepared statements with placeholders, which also prevents SQL injection.
- Replace outdated syntax. Change TYPE=MyISAM to ENGINE=InnoDB, and remove MySQL 5-era options your server no longer supports. Check the manual for your exact version with SELECT VERSION();.
- Convert other dialects. Replace SELECT TOP 10 with LIMIT 10, use backticks instead of double quotes for identifiers, and replace ILIKE with LIKE.
- Format long queries. Put each clause on its own line to make the broken part easy to spot, then run the query in the mysql client to get a precise line number.
Reserved word quoted and values passed safely (PHP PDO)
$sql = 'INSERT INTO `order` (`id`, `total`) VALUES (:id, :total)';
$stmt = $pdo->prepare($sql);
$stmt->execute(['id' => 1, 'total' => 9.99]);How to stop it happening again
- Avoid reserved words as table and column names
- Always use prepared statements
- Export and import dumps between matching MySQL versions
- Format complex SQL so mistakes stand out