Integrity Checker 'Fix Inconsistencies' fails with 'Unterminated identifier' when content JSON contains apostrophes

Summary

Running Integrity Checker → Fix Inconsistencies (HTML Pages) fails with a DotDataException when any conflicted content's JSON contains a single quote (apostrophe). Because the contentlet_as_json value is inlined into the generated SQL via String.format(..., '%s', ...), an apostrophe inside user content terminates the SQL string literal early, producing a PostgreSQL Unterminated identifier parse error.

Error

[ERROR] rest.IntegrityResource: Error fixing htmlPages conflicts for End Point server: [...]
com.dotmarketing.exception.DotDataException: Unterminated identifier started at position 5,814 in SQL 
INSERT INTO contentlet(inode, show_on_menu, title, ... ) 
SELECT ?, show_on_menu, title, ... , ?, ?, '{ "title" : "BROKEN Security & Compliance", ... 
"ogDescription" : { "value" : "Ensure your data's security and compliance with dotCMS. ..." ...
...
Caused by: org.postgresql.util.PSQLException: Unterminated identifier started at position 5,814 in SQL ... Expected " char
	at org.postgresql.core.Parser.checkParsePosition(Parser.java:1435)
	...
	at com.dotcms.integritycheckers.ContentPageIntegrityChecker.fixContentPageConflicts(ContentPageIntegrityChecker.java:741)
	at com.dotcms.integritycheckers.ContentPageIntegrityChecker.updateConflictingPageIds(ContentPageIntegrityChecker.java:482)
	at com.dotcms.integritycheckers.ContentPageIntegrityChecker.checkAndFixIdentifierConflicts(ContentPageIntegrityChecker.java:369)
	at com.dotcms.integritycheckers.ContentPageIntegrityChecker.executeFix(ContentPageIntegrityChecker.java:171)

The '{...}' literal is closed early by the apostrophe in data's, and PostgreSQL then interprets the rest of the JSON as SQL — the first " it encounters starts an unterminated quoted identifier.

Root cause

The integrity checkers build INSERT INTO contentlet ... SELECT ... , '{<json>}', ... FROM contentlet ... statements by inlining the serialized contentlet JSON with String.format instead of binding it as a JDBC parameter. Any ' (apostrophe) in any field value of the contentlet (titles, descriptions, meta tags, friendly names, etc.) breaks the statement. This is effectively SQL injection of our own content into our own SQL.

Affected statements (7 total, 3 files):

How to reproduce

1. Push content between environments where a page (or file asset / host) with an apostrophe in any field (e.g. ogDescription: "Ensure your data's security...") ends up in the integrity conflict tables (htmlpages_ir, fileassets_ir, hosts_ir).
2. Run Integrity Checker → Fix Inconsistencies for HTML Pages.
3. executeFix → fixContentPageConflicts throws DotDataException: Unterminated identifier ... and the fix aborts for that endpoint.

Note: this is easy to hit in practice — any marketing copy containing contractions ("don't", "user's", "world's") triggers it.

Additional latent bug (MySQL)

The same code path applies contentletQuery.replaceAll("\"", "")for MySQL to escape reserved-word column names — which also rewrites the double quotes **inside the inlined JSON**, corrupting the storedcontentlet_as_json` even when no apostrophes are present.

Proposed fix

Bind the JSON as a JDBC parameter using the existing DotConnect.addJSONParam() (already the established pattern for this column — see PopulateContentletAsJSONUtil, ContentTypeFactoryImpl, ExperimentsFactoryImpl):

// Before
String contentletQuery = String.format("INSERT INTO contentlet(...) SELECT ?, ..., ?, ?, '%s', ... FROM contentlet c ...", workingCopyJson);
dc.setSQL(contentletQuery);
dc.addParam(remoteWorkingInode);
dc.addParam(newHtmlPageIdentifier);
dc.addParam(languageId);
dc.addParam(oldHtmlPageIdentifier);   // ❌ JSON inlined into SQL text

// After
String contentletQuery = "INSERT INTO contentlet(...) SELECT ?, ..., ?, ?, ?, ... FROM contentlet c ...";
dc.setSQL(contentletQuery);
dc.addParam(remoteWorkingInode);
dc.addParam(newHtmlPageIdentifier);
dc.addParam(languageId);
dc.addJSONParam(workingCopyJson);    // ✅ bound as PGobject(type=json) / string
dc.addParam(oldHtmlPageIdentifier);

Side benefits:

• Prepared statements become cacheable (CACHE_PREPARED_STATEMENTS) since the SQL text is now stable.
• Fixes the MySQL quote-corruption bug above.
• Regression tests: TestFixConflictWhenContentContainsSingleQuotes in ContentPageIntegrityCheckerTest and ContentFileAssetIntegrityCheckerTest (dotcms-integration) exercise executeFix with apostrophe-laden content.

Environment

• Branch: main @ be94fd0a7f
• DB: PostgreSQL (json contentlet_as_json column is jsonb; the fix relies on the implicit json → jsonb cast, same as existing addJSONParam usages)

Start by reading the seven affected statements in ContentPageIntegrityChecker, ContentFileAssetIntegrityChecker, and HostIntegrityChecker, then compare their parameter handling with existing addJSONParam usages. Run TestFixConflictWhenContentContainsSingleQuotes in ContentPageIntegrityCheckerTest and ContentFileAssetIntegrityCheckerTest; done means all listed JSON values are safely bound and the regression tests pass.