A directory synchronisation integration loaded one table completely, delivered roughly half of another, and left a third almost empty, all in the same run and with no failed status on the integration side. The cause turned out to be a single character: an apostrophe in a person's surname. It took down the whole 1,000-row batch it sat in, because the receiving API built its SQL INSERT by concatenating strings. The apostrophe had been harmless until a correct fix elsewhere removed a protection nobody knew existed. This post traces how that happened, how we worked around it in the integration, and why the problem is bigger than lost rows.
The error
The integration side reported success. The receiving data service, on the server side, logged this:
DS Fault Message: Error in 'SQLQuery.processStoredProcQuery':
Incorrect syntax near '<surname>'
DS Code: DATABASE_ERROR
Current Request Name: dynamicOperation
Current Params: {query=INSERT INTO [Schema].[tbl_Contacts] ([CN], [DisplayName], ...)
VALUES ('...', ...The Current Params line is the important part. The entire multi-row INSERT arrives as one string parameter. A value such as O'Brien closes the SQL string literal early, and SQL Server rejects the whole statement.
Three consequences, one of them invisible
- One bad value rejects up to 1,000 rows. The integration sends one array per batch and the API turns it into a single
INSERT ... VALUES (...), (...), .... One apostrophe anywhere in the batch loses every row in it. - The table has already been truncated. The first batch tells the API to empty the table, so a rejected batch leaves an empty or partial table rather than yesterday's data.
- The caller cannot tell. The failure is logged at ERROR on the server, while the client still received a success response and an entries count that looked healthy.
That last point is what made it hard to see. Nothing on the integration side of the interface indicated a problem.
Why it started now
The integration's reader had previously applied XML entity escaping to every value. That was a mistake in itself, because the payload was JSON, and it caused real damage: surnames reached the database as O'Brien. The data owner spotted it in the user table and asked for the escaping to be removed. That request was correct, and we removed it.
What nobody knew was that the entity escaping had been acting as an accidental SQL guard. ' contains no apostrophe, so the concatenated INSERT never saw one. Turning the escaping off fixed the corrupted surnames and removed a protection that had been load-bearing all along.
JSON escaping does not fill the gap. It handles quotes, backslashes and control characters, and correctly leaves the apostrophe alone, because JSON does not require it to be escaped. Once entity escaping was off, nothing between the directory and the SQL statement touched an apostrophe.
The arithmetic that confirmed it
The groups table explained itself once the cause was known: the number of missing rows was an exact multiple of the batch size. Most of the batches sent contained at least one apostrophe, typically in a free-text description, and each of those was lost whole.
There was also a red herring. The delivered count happened to be divisible by three, and during the investigation that was read as a sign that a separate duplication defect had spread to this table. It was coincidence: after the fix the full row count was not divisible by three. A duplicated primary key is evidence of duplication. A number that divides neatly is not.
The workaround in the reader
The real fix belongs in the API: parameterise the INSERT. Until that happens, we added a third escaping option to the reader, named for what it does and marked for removal:
/**
* SQL string-literal escaping for the receiving API's concatenated INSERT.
* WORKAROUND. Remove when the API parameterises its insert, or values
* will be stored with doubled apostrophes.
*/
protected static String sqlEscape(String s) {
return s == null ? "" : s.replace("'", "''");
}Inside a correctly closed single-quoted T-SQL literal, the apostrophe is the only character that needs this treatment. Double quotes, brackets, semicolons and comment markers are inert there, and the JSON hop is already covered by JSON escaping.
This is better than restoring the entity escaping, which would knowingly have put ' back into the database. Because the reader is an OSGi bundle, the change reached each node only after a restart, and it had to go to every node that ran the reader.
Proving correctness, not just success
After deployment the contacts table went from empty to complete, and the groups table recovered every missing row. Row counts show that the load succeeded. They do not show that the values are right.
For that, we queried the specific record that had triggered the error. It came back with a single apostrophe in every field, not a doubled one and not an entity. SQL Server had consumed the doubling exactly as intended, which also means the workaround can be removed cleanly when the API is fixed, without leaving doubled apostrophes behind.
This is an injection surface
Every value in that statement originates in the directory. A group description containing '); DROP TABLE ...-- would be executed as SQL. Today the defect shows up as a real person's surname breaking a batch, which is the harmless version of the same flaw. It is worth saying so explicitly when reporting it, because it changes who needs to act and how quickly. And it applies to every source that loads through the same API, not just this one.
The changes we proposed for the API, in order of importance:
- Parameterise the insert. This is the only real fix; everything else, including the reader workaround, is mitigation.
- Return a non-2xx status when the insert fails.
- Do not truncate the target until the insert is known to succeed.
- Log validation failures at ERROR, not INFO.
Escaping at the layer that owns the value
The closest relative we have met was not in SQL at all. In our own energy management product, a home-made CSS minifier recognised quoted strings before it stripped comments. An apostrophe inside a comment, in a word such as "Bootstrap's" or "aren't", was read as a string delimiter and paired with a real quote further on, and the comment stripper then deleted the live CSS in between. On production it silently dropped 252 rules from the bundle, including the body font, the page width and a whole hero block. It had been reported as stale CSS, and it was not a cache problem at all. The fix handles comments, strings and url() in one left-to-right pass, so each stays in its own context. One quote character in the wrong context, and a whole block of input is read as something else.
We met a smaller relative of this on our own course platform. In July 2026 some quiz questions showed answer options with blank text. The answers were examples of HTML tags, stored raw and unencoded in a field rendered as HTML, so the browser parsed them as empty elements. We fixed it where the data was wrong, by entity-encoding those stored answers, rather than in the proxy that displays the pages, which would have broken legitimate formatting. The principle is the same as in the reader workaround above: fix escaping at the layer that owns the value, not wherever the symptom happens to show.
Conclusion
The defect here was not in the code that changed. Removing the entity escaping was correct, and it exposed a flaw on the other side of an interface that the escaping had been hiding by accident. Before removing an escaping or sanitising step, find out what else is depending on it. And when a workaround goes in, write down what removes it, so it does not outlive the fix it was waiting for.
Our WSO2 course covers building data pipelines like this one, including where values cross from data into code, taught by practitioners who do this work for a living.