Error passing strings containing apostrophes as parameters

[adegboyegaFAU]

Describe the bug Passing strings containing apostrophes/single quotes (') as parameters throws an error. There is a work around to get this working, however its unsustainable.

How are you accessing AGE (Command line, driver, etc.)?

• python driver (psycopg2)

What data setup do we need to do?

import psycopg2 
import age
import json

GRAPH_NAME = "test_graph"
conn = psycopg2.connect(host="localhost", port="5432", dbname="postgres", user="postgres", password="password")

age.setUpAge(conn, GRAPH_NAME)

with conn.cursor() as cursor:
    try :
        cursor.execute(
            """
            SELECT * from cypher(
                %s, 
                $$ CREATE (c:testnode {node_id: %s, node_text: %s}) RETURN c $$
            ) as (c agtype); 
            """, 
            (GRAPH_NAME , "test_id", "This isn't working!") )
    except Exception as ex:
        print(type(ex), ex)
        # if exception occurs, you must rollback all transaction. 
        conn.rollback()
    else:
        conn.commit()
        result = cursor.fetchall()
        print(result)

What is the command that caused the error?

 cursor.execute(
            """
            SELECT * from cypher(
                %s, 
                $$ CREATE (c:testnode {node_id: %s, node_text: %s}) RETURN c $$
            ) as (c agtype); 
            """, 
            (GRAPH_NAME , "test_id", "This isn't working!") )

ERROR:  <class 'psycopg2.errors.SyntaxError'> syntax error at or near "'t working!'"
LINE 4: ...ATE (c:testnode {node_id: 'test_id', node_text: 'This isn''t working...

Expected behavior The expected output should be:

[({label:testnode, id:1688849860263941, properties:{node_id: test_id, node_text: This isn't working!, }}::VERTEX,)]

Environment (please complete the following information):

• Age Version: 1.5.0
• Postgresql Version: 1.6.0

Additional context Enclosing the string in single quotes rather than double quotes and escaping the apostrophe within the string by adding another apostrophe seems to work, although it removes the apostrophe entirely.

with conn.cursor() as cursor:
    try :
        cursor.execute(
            """
            SELECT * from cypher(
                %s, 
                $$ CREATE (c:testnode {node_id: %s, node_text: %s}) RETURN c $$
            ) as (c agtype); 
            """, 
            GRAPH_NAME , "test_id", 'This isn''t working!') )
    except Exception as ex:
        print(type(ex), ex)
        # if exception occurs, you must rollback all transaction. 
        conn.rollback()
    else:
        conn.commit()
        result = cursor.fetchall()
        print(result)

OUTPUT: [({label:testnode, id:1688849860263941, properties:{node_id: test_id, node_text: This isnt working!, }}::VERTEX,)]

Notice how the output doesn't contain the apostrophe in the word: isn't. This doesn't appear sustainable in an application because while its possible to replace every occurrence of an apostrophe with two apostrophes, I'm not sure its possible to explicitly declare a string with single quotes rather than double quotes.

[jrgemignani]

@adegboyegaFAU Have you tried to escape it?

psql-17.4-5432-pgsql=# select * from cypher('graph', $$create (u {text: "isn\'t"})  return u $$) as (result agtype);
                                    result
-------------------------------------------------------------------------------
 {"id": 281474976710659, "label": "", "properties": {"text": "isn't"}}::vertex
(1 row)

psql-17.4-5432-pgsql=#

Otherwise, this is likely a python issue with escaping characters.

[adegboyegaFAU]

@jrgemignani Yes. I've tried all sorts of attempts at escaping but only one seemed to work but it includes quotes in the resulting string. So for the given example, I'd end up with something like:

"'isn't'"

I actually tried it with the JS driver as well and got the sane outcome so I concluded its something to do with the layer between age and its respective drivers. It did work direct from SQL and age-viewer though.