Repository navigation
Description
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:
- There is a stray
'after:node_pattern, so the statement is invalid SQL and the request returns
HTTP 500 (unterminated quoted string). - 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 becomeb 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
-
Point
repository.databaseat 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 -
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=jsonAll 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'}] -
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