How to handle the param if there're single quotes in it?

[noahxzhu]

Describe the bug
An error is observed when a property parameter includes a single quote.

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

• I'm using the apache-age-python package in a python project.

What data setup do we need to do?

def upsert_triplet_test(subj: str, rel: str, obj: str) -> None:
    prepared_statement = f"""
        MERGE (n1:`Entity` {{id: %s}})
        MERGE (n2:`Entity` {{id: %s}})
        MERGE (n1)-[:`{rel.replace(" ", "_").upper()}`]->(n2)
    """

    ag.execCypher(
        prepared_statement,
        params=(
            subj,
            obj,
        ),
    )

    ag.commit()


upsert_triplet_test(
    "I", "Learned", "It's better for technology companies to be run by product people"
)

This is the error

    cursor.execute(stmt)
psycopg2.errors.SyntaxError: syntax error at or near "'s better for technology companies to be run by product people'"

What is the necessary configuration info needed?

• nothing

What is the command that caused the error?

SELECT * from ag_catalog.cypher('graph_store', $$
     MERGE (n1:`Entity` {id: 'I'})
     MERGE (n2:`Entity` {id: 'It's better for technology companies to be run by product people'})
     MERGE (n1)-[:`Learned`]->(n2)
$$) as (a ag_catalog.agtype);

Not sure how to handle this case

ERROR:  syntax error at or near "s"
LINE 3:      MERGE (n2:`Entity` {id: 'It's better for technology com...

Expected behavior
This merge operation should be succeed.

Environment (please complete the following information):

• Version: package version 0.0.7
• AGE Version: 1.5.0

Additional context
no

[noahxzhu]

When I use Neo4j, I can create like this, the edge label can also include single quote

MERGE (n1:`TEST`{id: 'a\'bb'})
MERGE (n2:`TEST`{id: 'bb \'s abc'})
MERGE (n1)-[:`ABC_I'S_a`]->(n2)

[jrgemignani]

@noahxzhu Did you try using the backslash for the cypher query? Your example above doesn't show it.

psql-16.1-5432-pgsql=# SELECT * from ag_catalog.cypher('test', $$
     MERGE (n1:`Entity` {id: 'I'})
     MERGE (n2:`Entity` {id: 'It\'s better for technology companies to be run by product people'})
     MERGE (n1)-[:`Learned`]->(n2)
$$) as (a ag_catalog.agtype);
 a
---
(0 rows)

psql-16.1-5432-pgsql=# SELECT * FROM cypher('test', $$ MATCH (v) RETURN v $$) AS (v agtype);
                                                                      v

------------------------------------------------------------------------------------------------------------------------------------
----------
 {"id": 844424930131969, "label": "Entity", "properties": {"id": "I"}}::vertex
 {"id": 844424930131970, "label": "Entity", "properties": {"id": "It's better for technology companies to be run by product people"}
}::vertex
(2 rows)

psql-16.1-5432-pgsql=#

[jrgemignani]

@noahxzhu Additionally, this sounds more like a question than a bug.

[markgomer]

Can you put double quotes around the string?

SELECT * from ag_catalog.cypher('graph_store', $$
     MERGE (n1:`Entity` {id: 'I'})
     MERGE (n2:`Entity` {id: "It's better for technology companies to be run by product people"})
     MERGE (n1)-[:`Learned`]->(n2)
$$) as (a ag_catalog.agtype);

This worked for me alright

[noahxzhu]

Can you put double quotes around the string?

SELECT * from ag_catalog.cypher('graph_store', $$
     MERGE (n1:`Entity` {id: 'I'})
     MERGE (n2:`Entity` {id: "It's better for technology companies to be run by product people"})
     MERGE (n1)-[:`Learned`]->(n2)
$$) as (a ag_catalog.agtype);

This worked for me alright

@markgomer
I just copy and paste yours, it seems not work

[noahxzhu]

@noahxzhu Did you try using the backslash for the cypher query? Your example above doesn't show it.

psql-16.1-5432-pgsql=# SELECT * from ag_catalog.cypher('test', $$
     MERGE (n1:`Entity` {id: 'I'})
     MERGE (n2:`Entity` {id: 'It\'s better for technology companies to be run by product people'})
     MERGE (n1)-[:`Learned`]->(n2)
$$) as (a ag_catalog.agtype);
 a
---
(0 rows)

psql-16.1-5432-pgsql=# SELECT * FROM cypher('test', $$ MATCH (v) RETURN v $$) AS (v agtype);
                                                                      v

------------------------------------------------------------------------------------------------------------------------------------
----------
 {"id": 844424930131969, "label": "Entity", "properties": {"id": "I"}}::vertex
 {"id": 844424930131970, "label": "Entity", "properties": {"id": "It's better for technology companies to be run by product people"}
}::vertex
(2 rows)

psql-16.1-5432-pgsql=#

@jrgemignani
not work on my side, I need to investigate the reason

[markgomer]

Can you put double quotes around the string?

SELECT * from ag_catalog.cypher('graph_store', $$
     MERGE (n1:`Entity` {id: 'I'})
     MERGE (n2:`Entity` {id: "It's better for technology companies to be run by product people"})
     MERGE (n1)-[:`Learned`]->(n2)
$$) as (a ag_catalog.agtype);

This worked for me alright

@markgomer I just copy and paste yours, it seems not work

Try setting the search_path:

SET search_path TO ag_catalog;

I'm not sure why it is not working without this, though.

[noahxzhu]

Can you put double quotes around the string?

SELECT * from ag_catalog.cypher('graph_store', $$
     MERGE (n1:`Entity` {id: 'I'})
     MERGE (n2:`Entity` {id: "It's better for technology companies to be run by product people"})
     MERGE (n1)-[:`Learned`]->(n2)
$$) as (a ag_catalog.agtype);

This worked for me alright

@markgomer I just copy and paste yours, it seems not work

Try setting the search_path:

SET search_path TO ag_catalog;

I'm not sure why it is not working without this, though.

After setting the search_path, now it's working, but why?

[noahxzhu]

Another question, should this work as it in Neo4j?

SELECT * from ag_catalog.cypher('graph_store', $$
	MERGE (n1:`TEST`{id: 'a\'bb'})
	MERGE (n2:`TEST`{id: 'bb \'s abc'})
	MERGE (n1)-[:`ABC_I'S_a`]->(n2)
$$) as (a ag_catalog.agtype);

[noahxzhu]

I still have the problem with the python driver, how to handle it with a param which contains single quotes, I digged into the python code, it do set the search_path, but not works

[jrgemignani]

@noahxzhu

After setting the search_path, now it's working, but why?

The search path is needed to see Cypher

[jrgemignani]

Another question, should this work as it in Neo4j?

SELECT * from ag_catalog.cypher('graph_store', $$
	MERGE (n1:`TEST`{id: 'a\'bb'})
	MERGE (n2:`TEST`{id: 'bb \'s abc'})
	MERGE (n1)-[:`ABC_I'S_a`]->(n2)
$$) as (a ag_catalog.agtype);

I'm not sure I understand what you are asking. Also, keep in mind that you will likely need to backslash all non wrapping quote characters.

[noahxzhu]

Another question, should this work as it in Neo4j?

SELECT * from ag_catalog.cypher('graph_store', $$
	MERGE (n1:`TEST`{id: 'a\'bb'})
	MERGE (n2:`TEST`{id: 'bb \'s abc'})
	MERGE (n1)-[:`ABC_I'S_a`]->(n2)
$$) as (a ag_catalog.agtype);

I'm not sure I understand what you are asking. Also, keep in mind that you will likely need to backslash all non wrapping quote characters.

I mean, that query works in Neo4j, but I tried it in pg, it doesn't work. Can a label include quotes?

SELECT * from cypher('graph_store', $$
	MERGE (n1:`TEST`{id: 'a\'bb'})
	MERGE (n2:`TEST`{id: 'bb \'s abc'})
	MERGE (n1)-[:`ABC_IS\'_a`]->(n2)
$$) as (a agtype);

[noahxzhu]

The behavior in the python driver

NOT WORK

    prepared_statement = """
        MERGE (n1:`TEST` {id: 'a\'bb'})
        MERGE (n2:`TEST` {id: 'bb\'s abc'})
        MERGE (n1)-[:`ABC_IS_a`]->(n2)
    """

    ag.execCypher(
        prepared_statement,
    )

WORK

    prepared_statement = """
        MERGE (n1:`TEST` {id: "a'bb"})
        MERGE (n2:`TEST` {id: "bb's abc"})
        MERGE (n1)-[:`ABC_IS_a`]->(n2)
    """

    ag.execCypher(
        prepared_statement,
    )

NOT WORK

    prepared_statement = """
        MERGE (n1:`TEST` {id: 'a"bb'})
        MERGE (n2:`TEST` {id: 'bb"s abc'})
        MERGE (n1)-[:`ABC_IS_a`]->(n2)
    """

    ag.execCypher(
        prepared_statement,
    )

NOT WORK

    prepared_statement = """
        MERGE (n1:`TEST` {id: 'a\"bb'})
        MERGE (n2:`TEST` {id: 'bb\"s abc'})
        MERGE (n1)-[:`ABC_IS_a`]->(n2)
    """

    ag.execCypher(
        prepared_statement,
    )

[markgomer]

I mean, that query works in Neo4j, but I tried it in pg, it doesn't work. Can a label include quotes?

SELECT * from cypher('graph_store', $$
	MERGE (n1:`TEST`{id: 'a\'bb'})
	MERGE (n2:`TEST`{id: 'bb \'s abc'})
	MERGE (n1)-[:`ABC_IS\'_a`]->(n2)
$$) as (a agtype);

I tested this on AGE-viewer and it seems to be working... what is the error message you're getting?

[noahxzhu]

I mean, that query works in Neo4j, but I tried it in pg, it doesn't work. Can a label include quotes?

SELECT * from cypher('graph_store', $$
	MERGE (n1:`TEST`{id: 'a\'bb'})
	MERGE (n2:`TEST`{id: 'bb \'s abc'})
	MERGE (n1)-[:`ABC_IS\'_a`]->(n2)
$$) as (a agtype);

I tested this on AGE-viewer and it seems to be working... what is the error message you're getting?

@markgomer Thank you for your reply
it shows label name is invalid

the same on age-viewer

[markgomer]

I had the same error showing up, but I worked around it by creating labels separately:

SELECT create_vlabel('graph_store', 'TEST');
SELECT create_elabel('graph_store', 'ABC_IS\'a');

Please check if this works for you

[noahxzhu]

I had the same error showing up, but I worked around it by creating labels separately:

SELECT create_vlabel('graph_store', 'TEST');
SELECT create_elabel('graph_store', 'ABC_IS\'a');

Please check if this works for you

unfortunately, it doesn't work on my side.

[noahxzhu]

I did some tests with the python driver, the behavior likes below

Case 1

def test(p: str):
    prepared_statement = """
        MERGE (n1:`TEST` {id: %s})
    """

    ag.execCypher(prepared_statement, params=(p,))
    ag.commit()


test("a's")

GOT

psycopg2.errors.SyntaxError: syntax error at or near "'s'"
LINE 1: SELECT * from cypher(NULL,NULL) as (v agtype);

Case 2

def test(p: str):
    prepared_statement = """
        MERGE (n1:`TEST` {id: %s})
    """

    ag.execCypher(prepared_statement, params=(p,))
    ag.commit()


test('a"s')

GOT

psycopg2.errors.SyntaxError: unexpected character at or near "\"
LINE 1: SELECT * from cypher(NULL,NULL) as (v agtype);

Case 3

I think we shouldn't add quotes for the params in the query template, right?

def test(p: str):
    prepared_statement = """
        MERGE (n1:`TEST` {id: '%s'})
    """

    ag.execCypher(prepared_statement, params=(p,))
    ag.commit()


test('a"s')

GOT

psycopg2.errors.SyntaxError: unexpected character at or near "\"
LINE 1: SELECT * from cypher(NULL,NULL) as (v agtype);

Case 4

def test(p: str):
    prepared_statement = """
        MERGE (n1:`TEST` {id: '%s'})
    """

    ag.execCypher(prepared_statement, params=(p,))
    ag.commit()


test("a's")

GOT

psycopg2.errors.SyntaxError: syntax error at or near "a"
LINE 1: SELECT * from cypher(NULL,NULL) as (v agtype);

Case 5

def test(p: str):
    prepared_statement = """
        MERGE (n1:`TEST` {id: "%s"})
    """

    ag.execCypher(prepared_statement, params=(p,))
    ag.commit()


test("a's")

GOT Succeed but the data like this

{"id": "'a''s'"}

Case 6

def test(p: str):
    prepared_statement = """
        MERGE (n1:`TEST` {id: "%s"})
    """

    ag.execCypher(prepared_statement, params=(p,))
    ag.commit()


test('a"s')

GOT

psycopg2.errors.SyntaxError: syntax error at or near "s"
LINE 1: SELECT * from cypher(NULL,NULL) as (v agtype);

[mannas006]

It appears that the upsert_triplet_test function's object parameter contains a single quotation, which is causing the error. 'Technology busin be run by product people' is one of the syntactic errors that the warning particularly addresses. This is probably because the Cypher query contains a syntax mistake because the single quote in the string is being read as the end of the string.

To handle this case, you can escape the single quote within the object parameter. In Python, you can escape a single quote by using another single quote. Here's how you can modify your function call to handle the single quote in the object parameter:

upsert_triplet_test(
    "I", "Learned", "It''s better for technology companies to be run by product people"
)

By replacing the single quote with two single quotes (''), you escape the single quote within the string, ensuring that it is interpreted correctly in the Cypher query.

Your modified Cypher query should look like this:

SELECT * FROM ag_catalog.cypher('graph_store', $$
    MERGE (n1:`Entity` {id: 'I'})
    MERGE (n2:`Entity` {id: 'It''s better for technology companies to be run by product people'})
    MERGE (n1)-[:`Learned`]->(n2)
$$) AS (a ag_catalog.agtype);

[noahxzhu]

Hi @mannas006
I just tried, it seems this doesn't work for me

<class 'psycopg2.errors.SyntaxError'> syntax error at or near "'s better for technology companies to be run by product people'"
LINE 4:     MERGE (n2:`Entity` {id: 'It''s better for technology com...

[mannas006]

Hello @noahxzhu
Thank you for informing me.