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

noahxzhu · 2024-04-05T12:51:35Z

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

The text was updated successfully, but these errors were encountered:

noahxzhu · 2024-04-05T14:00:40Z

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 · 2024-04-05T15:28:34Z

@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 · 2024-04-05T15:30:03Z

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

markgomer · 2024-04-05T15:41:18Z

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 · 2024-04-05T15:47:31Z

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 · 2024-04-05T15:49:30Z

@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 · 2024-04-05T16:00:46Z

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 · 2024-04-05T16:04:00Z

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 · 2024-04-05T16:08:48Z

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 · 2024-04-05T16:17:42Z

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 · 2024-04-05T16:21:40Z

@noahxzhu

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

The search path is needed to see Cypher

jrgemignani · 2024-04-05T16:24:54Z

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 · 2024-04-05T17:46:26Z

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 · 2024-04-05T17:58:49Z

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 · 2024-04-05T18:13:01Z

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 · 2024-04-05T18:23:15Z

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 · 2024-04-05T18:43:23Z

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 · 2024-04-06T05:33:59Z

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 · 2024-04-06T10:11:11Z

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 · 2024-04-12T14:22:27Z

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 · 2024-04-12T14:38:24Z

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 · 2024-04-12T14:51:59Z

Hello @noahxzhu
Thank you for informing me.

github-actions · 2024-06-12T00:07:16Z

This issue is stale because it has been open 60 days with no activity. Remove "Abondoned" label or comment or this will be closed in 14 days.

github-actions · 2024-06-26T00:07:23Z

This issue was closed because it has been stalled for further 14 days with no activity.

Mocuto · 2024-11-26T04:05:45Z

I'm posting to report that I'm experiencing this bug as well. I do not think that this is a question, as this bug only occurs while using the apache-age python driver. Nested quotation marks work fine when querying directly in psql. If I find a workaround I will report to this thread

noahxzhu added the bug Something isn't working label Apr 5, 2024

jrgemignani added question Further information is requested and removed bug Something isn't working labels Apr 5, 2024

noahxzhu mentioned this issue Apr 6, 2024

Why do you use Apache AGE? #1705

Closed

aked21 pinned this issue Apr 9, 2024

github-actions bot added the Stale Stale issues/PRs label Jun 12, 2024

github-actions bot closed this as not planned Won't fix, can't repro, duplicate, stale Jun 26, 2024

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

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

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

noahxzhu commented Apr 5, 2024

noahxzhu commented Apr 5, 2024 •

edited

Loading

jrgemignani commented Apr 5, 2024 •

edited

Loading

jrgemignani commented Apr 5, 2024

markgomer commented Apr 5, 2024

noahxzhu commented Apr 5, 2024

noahxzhu commented Apr 5, 2024 •

edited

Loading

markgomer commented Apr 5, 2024

noahxzhu commented Apr 5, 2024

noahxzhu commented Apr 5, 2024

noahxzhu commented Apr 5, 2024

jrgemignani commented Apr 5, 2024

jrgemignani commented Apr 5, 2024

noahxzhu commented Apr 5, 2024

noahxzhu commented Apr 5, 2024

markgomer commented Apr 5, 2024

noahxzhu commented Apr 5, 2024 •

edited

Loading

markgomer commented Apr 5, 2024

noahxzhu commented Apr 6, 2024

noahxzhu commented Apr 6, 2024 •

edited

Loading

mannas006 commented Apr 12, 2024

noahxzhu commented Apr 12, 2024 •

edited

Loading

mannas006 commented Apr 12, 2024

github-actions bot commented Jun 12, 2024

github-actions bot commented Jun 26, 2024

Mocuto commented Nov 26, 2024

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

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

Comments

noahxzhu commented Apr 5, 2024

noahxzhu commented Apr 5, 2024 • edited Loading

jrgemignani commented Apr 5, 2024 • edited Loading

jrgemignani commented Apr 5, 2024

markgomer commented Apr 5, 2024

noahxzhu commented Apr 5, 2024

noahxzhu commented Apr 5, 2024 • edited Loading

markgomer commented Apr 5, 2024

noahxzhu commented Apr 5, 2024

noahxzhu commented Apr 5, 2024

noahxzhu commented Apr 5, 2024

jrgemignani commented Apr 5, 2024

jrgemignani commented Apr 5, 2024

noahxzhu commented Apr 5, 2024

noahxzhu commented Apr 5, 2024

markgomer commented Apr 5, 2024

noahxzhu commented Apr 5, 2024 • edited Loading

markgomer commented Apr 5, 2024

noahxzhu commented Apr 6, 2024

noahxzhu commented Apr 6, 2024 • edited Loading

Case 1

Case 2

Case 3

Case 4

Case 5

Case 6

mannas006 commented Apr 12, 2024

noahxzhu commented Apr 12, 2024 • edited Loading

mannas006 commented Apr 12, 2024

github-actions bot commented Jun 12, 2024

github-actions bot commented Jun 26, 2024

Mocuto commented Nov 26, 2024

noahxzhu commented Apr 5, 2024 •

edited

Loading

jrgemignani commented Apr 5, 2024 •

edited

Loading

noahxzhu commented Apr 5, 2024 •

edited

Loading

noahxzhu commented Apr 5, 2024 •

edited

Loading

noahxzhu commented Apr 6, 2024 •

edited

Loading

noahxzhu commented Apr 12, 2024 •

edited

Loading