Skip to content

PostgreSQL FTS filter on anytext has a stray quote and a shared bind name #1276

Open

Description

@ahmed-hassan19

Description

On PostgreSQL, an OGC API - Records items filter of the form anytext LIKE '<term>' (no %) uses the
full text search branch in pycsw/core/pygeofilter_evaluate.py:

return text("plainto_tsquery('english', :node_pattern') @@ anytext_tsvector").bindparams(node_pattern=node.pattern)  # noqa

This has two problems:

  1. There is a stray ' after :node_pattern, so the statement is invalid SQL and the request returns
    HTTP 500 (unterminated quoted string).
  2. Every term binds the same name, node_pattern. With
    anytext LIKE 'a' AND anytext LIKE 'b', SQLAlchemy keeps one value for both placeholders, so once the
    quote is fixed the filter would silently become b AND b.

Both ECQL (filter=) and CQL2-JSON (like op in a POST body) reach this code via to_filter() in
pycsw/ogc/api/records.py. CSW csw:AnyText is not affected (the FES code builds its own
plainto_tsquery), nor are q and the anytext query parameter (they always use ILIKE '%...%').

Both came in with #1247 (bind query parameters), which replaced the f-string
plainto_tsquery('english', '{node.pattern}') with a bound parameter but kept the closing quote.
CI runs SQLite only, so the branch is not exercised there. Related history: #760 (added this branch),
#901 (anytext ilike).

Environment

  • operating system: macOS 27
  • Python version: 3.12.15
  • pycsw version: master @ ed86544 (3.0-dev)
  • source/distribution
    • git clone
    • DebianGIS/UbuntuGIS
    • PyPI
    • zip/tar.gz
    • other (please specify):
  • web server
    • Apache/mod_wsgi
    • CGI
    • other (please specify): Flask development server (flask --app pycsw.wsgi_flask run)

PostgreSQL 16 / PostGIS 3.4 (postgis/postgis:16-3.4 Docker image), SQLAlchemy 1.4.54, psycopg2 2.9.13.

Steps to Reproduce

  1. Point repository.database at a PostgreSQL database, then load the CITE records:

    pycsw-admin.py setup-repository -c pycsw.yml
    pycsw-admin.py load-records -c pycsw.yml -p tests/functionaltests/suites/cite/data
    
  2. Start the server and query:

    B=http://localhost:8000/collections/metadata:main/items
    curl -sG "$B" --data-urlencode "filter=anytext LIKE 'lorem'" --data-urlencode f=json
    curl -s -X POST "$B?f=json" -H 'Content-Type: application/json' \
         -d '{"op":"like","args":[{"property":"anytext"},"lorem"]}'
    curl -sG "$B" --data-urlencode "filter=anytext LIKE 'lorem' AND anytext LIKE 'ipsum'" --data-urlencode f=json
    

    All three return 500 Internal Server Error. The log shows:

    sqlalchemy.exc.ProgrammingError: (psycopg2.errors.SyntaxError) unterminated quoted string at or near "'lorem'') @@ anytext_tsvector) AS anon_1"
    LINE 4: WHERE plainto_tsquery('english', 'lorem'') @@ anytext_tsvect...
    [parameters: {'node_pattern': 'lorem'}]
    
  3. The shared bind name, without a database:

    from pygeofilter.parsers.ecql import parse as parse_ecql
    from sqlalchemy import Column, Text
    from sqlalchemy.dialects import postgresql
    from sqlalchemy.orm import declarative_base
    from pycsw.core.pygeofilter_evaluate import to_filter
    
    class dataset(declarative_base()):
        __tablename__ = 'records'
        identifier = Column(Text, primary_key=True)
        anytext = Column(Text)
    
    f = to_filter(parse_ecql("anytext LIKE 'lorem' AND anytext LIKE 'ipsum'"),
                  'postgresql', {'anytext': dataset.anytext})
    c = f.compile(dialect=postgresql.dialect())
    print(c)
    print(c.params)
    plainto_tsquery('english', %(node_pattern)s') @@ anytext_tsvector AND plainto_tsquery('english', %(node_pattern)s') @@ anytext_tsvector
    {'node_pattern': 'ipsum'}
    

Additional Information

Expected: HTTP 200; lorem matches 5 CITE records, lorem AND ipsum matches 2
(urn:uuid:19887a8a-..., urn:uuid:a06af396-...). With only the quote fixed, the shared bind would
return the 3 ipsum records instead.

Suggested fix: drop the quote and use bindparam('node_pattern', node.pattern, unique=True) so each term
gets its own bind name. I can open a PR with that change and a unit test that compiles the filter
against the PostgreSQL dialect (so it runs in the SQLite-only CI).

Activity

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions