Text values containing an apostrophe produce Cypher that's rejected by the parser

[prrao87]

Severity: high. Silent, permanent data loss for any row with an apostrophe.

Environment

• PostgreSQL 18.4 (Homebrew), macOS arm64
• pg_ladybug main @ e5182153f1d4b588e7fd758d69c1406a6649a79f
• liblbug 0.19.1

Summary

cypher_literal() escapes single quotes by doubling them, SQL-style
(pg_ladybug.c:868-876). The Ladybug Cypher parser does not accept '' inside
a single-quoted string, so every affected statement fails at replay. The
Postgres write succeeded, so the row exists in Postgres and never reaches the
graph.

Apostrophes are very common in real-world data: place names, surnames, product titles tend
to regularly contain them.

Minimal reproduction

CREATE DATABASE bugrepro;
\c bugrepro

CREATE EXTENSION pg_ladybug;

CREATE TABLE node_city (
    id      bigint PRIMARY KEY,
    name    text NOT NULL,
    country text NOT NULL
);

SELECT ladybug.register_node('City', 'node_city', 'id', NULL, 'demo');
SELECT ladybug.enable_replication('demo', 'INSERT,UPDATE,DELETE');

INSERT INTO node_city VALUES
    (1, 'Toronto',        'CA'),
    (2, 'Coeur d''Alene', 'US');   -- literal value: Coeur d'Alene

SELECT operation, cypher FROM ladybug.replication_log('demo') ORDER BY id;

Generated Cypher

 operation |                             cypher
-----------+-----------------------------------------------------------------
 INSERT    | CREATE (n:City {id: 1, name: 'Toronto', country: 'CA'})
 INSERT    | CREATE (n:City {id: 2, name: 'Coeur d''Alene', country: 'US'})

Replay

SET ladybug.storage_path = '/tmp/bugrepro.lbdb';
SET ladybug.pg_connstr   = 'host=/path/to/socket port=5432 dbname=bugrepro user=me';

SELECT * FROM ladybug.cypher(
  'CREATE NODE TABLE City(id INT64, name STRING, country STRING, PRIMARY KEY(id))'
) AS t(ok text);

SELECT ladybug.replay_replication('demo');

NOTICE:  ladybug: replay skipped: CREATE (n:City {id: 2, name: 'Coeur d''Alene', country: 'US'})
DETAIL:  ladybug: query failed: Parser exception: Invalid input
         <CREATE (n:City {id: 2, name: 'Coeur d''Alene'>: expected rule
         oC_SingleQuery (line: 1, offset: 38)
"CREATE (n:City {id: 2, name: 'Coeur d''Alene', country: 'US'})"
                                       ^^^^^^^

Toronto replicates; Coeur d'Alene is dropped. In a larger run, 6 of 166
nodes were lost this way with no error surfaced to the writer.

Suggested fix

Call Ladybug's own string-literal escaping routine instead of maintaining a
second serializer inside the Postgres extension. Add test cases for
apostrophes, backslashes, newlines, non-ASCII text, NUL rejection, and property
names that themselves require escaping.

[adsharma]

This could be fixed by LadybugDB/ladybug#729

[prrao87]

Seems like that linked PR was closed without merging, though?

[adsharma]

Yes - I asked around in #query-languages on graph greeks and no responses. Cypher uses '\' to escape quotes. Not clear we need to support the SQL style double quote escape.

I was trying to say - yeah we need to auto escape regardless of the mechanism.