SQL Syntax Error in Conversation Filtering via API on Chatwoot"

[jrCleber]

Describe the bug

When using the Chatwoot API to filter conversations, a POST request resulted in an internal server error (500). The request aimed to filter conversations by status and assignee_id, using "OR" and "AND" operators. The log revealed an ActiveRecord::StatementInvalid (PG::SyntaxError) in the construction of the SQL query, specifically a syntax error near a parenthesis. This suggests a problem in the logic of building dynamic queries based on the given parameters.

Environment: Hetzner Cloud

To Reproduce

Request:

1. Send a POST request to /api/v1/accounts/1/conversations/filter?page=1 in Chatwoot, with the Content-Type header set to application/json.
2. In the body of the request, include a payload to filter conversations by status (with values "open" or "resolved") using the "OR" operator, and by assignee_id (with the value "1") using the "AND" operator.

POST /api/v1/accounts/1/conversations/filter?page=1 HTTP/1.1
Host: {{host}}
Content-Type: application/json; charset=utf-8
Accept: application/json; charset=utf-8
api_access_token: {{token}}
Content-Length: 312

{
  "payload": [
    {
      "attribute_key": "status",
      "filter_operator": "equal_to",
      "values": ["open", "resolved"],
      "query_operator": "OR"
    },
    {
      "attribute_key": "assignee_id",
      "filter_operator": "equal_to",
      "values": ["1"],
      "query_operator": "AND"
    }
  ]
}

Error:

1. Notice a 500 Internal Server Error error in the response.
2. Check the server logs to find an ActiveRecord::StatementInvalid (PG::SyntaxError) related to a SQL syntax error.

I, [2024-04-07T10:02:29.811085 #1]  INFO -- : source=rack-timeout id=738ab309-7380-456e-ba86-124da1e4af8f timeout=15000ms state=ready
I, [2024-04-07T10:02:29.811661 #1]  INFO -- : [738ab309-7380-456e-ba86-124da1e4af8f] Started POST "/api/v1/accounts/1/conversations/filter?page=1" for 172.71.234.200 at 2024-04-07 10:02:29 +0000
I, [2024-04-07T10:02:29.815786 #1]  INFO -- : [738ab309-7380-456e-ba86-124da1e4af8f] Processing by Api::V1::Accounts::ConversationsController#filter as JSON
I, [2024-04-07T10:02:29.815877 #1]  INFO -- : [738ab309-7380-456e-ba86-124da1e4af8f]   Parameters: {"payload"=>[{"attribute_key"=>"[FILTERED]", "filter_operator"=>"equal_to", "values"=>["open", "resolved"], "query_operator"=>"OR"}, {"attribute_key"=>"[FILTERED]", "filter_operator"=>"equal_to", "values"=>["1"], "query_operator"=>"AND"}], "page"=>"1", "account_id"=>"1", "conversation"=>{}}
I, [2024-04-07T10:02:29.823025 #1]  INFO -- : [738ab309-7380-456e-ba86-124da1e4af8f] Completed 500 Internal Server Error in 7ms (ActiveRecord: 2.0ms | Allocations: 1944)
F, [2024-04-07T10:02:29.824361 #1] FATAL -- : [738ab309-7380-456e-ba86-124da1e4af8f]   
[738ab309-7380-456e-ba86-124da1e4af8f] ActiveRecord::StatementInvalid (PG::SyntaxError: ERROR:  syntax error at or near ")"
LINE 1: ... (0,1) OR  conversations.assignee_id IN ('1') AND ) AND "con...
                                                             ^
):
[738ab309-7380-456e-ba86-124da1e4af8f]   
[738ab309-7380-456e-ba86-124da1e4af8f] app/services/filter_service.rb:103:in `set_count_for_all_conversations'
[738ab309-7380-456e-ba86-124da1e4af8f] app/services/conversations/filter_service.rb:11:in `perform'
[738ab309-7380-456e-ba86-124da1e4af8f] app/controllers/api/v1/accounts/conversations_controller.rb:44:in `filter'
[738ab309-7380-456e-ba86-124da1e4af8f] app/controllers/concerns/switch_locale.rb:24:in `set_locale'
[738ab309-7380-456e-ba86-124da1e4af8f] app/controllers/concerns/switch_locale.rb:16:in `switch_locale_using_account_locale'
[738ab309-7380-456e-ba86-124da1e4af8f] app/controllers/concerns/request_exception_handler.rb:11:in `handle_with_exception'
[738ab309-7380-456e-ba86-124da1e4af8f] app/controllers/concerns/switch_locale.rb:24:in `set_locale'
[738ab309-7380-456e-ba86-124da1e4af8f] app/controllers/concerns/switch_locale.rb:11:in `switch_locale'
I, [2024-04-07T10:02:29.825322 #1]  INFO -- : source=rack-timeout id=738ab309-7380-456e-ba86-124da1e4af8f timeout=15000ms service=14ms state=completed

Expected behavior

The expected behavior would be for the API to return a filtered list of conversations based on the criteria specified in the request (status "open" or "resolved" and assignee_id "1"), without errors, and with data corresponding to conversations that meet the applied filters.

Environment

Linux VM

Cloud Provider

Hetzner Cloud

Platform

Browser

Operating system

Debian GNU/Linux 12 (bookworm)

Browser and version

Edge

Docker (if applicable)

• docker version

Client: Docker Engine - Community
 Version:           24.0.7
 API version:       1.43
 Go version:        go1.20.10
 Git commit:        afdd53b
 Built:             Thu Oct 26 09:08:02 2023
 OS/Arch:           linux/amd64
 Context:           default

Server: Docker Engine - Community
 Engine:
  Version:          24.0.7
  API version:      1.43 (minimum version 1.12)
  Go version:       go1.20.10
  Git commit:       311b9ff
  Built:            Thu Oct 26 09:08:02 2023
  OS/Arch:          linux/amd64
  Experimental:     false
 containerd:
  Version:          1.6.26
  GitCommit:        3dd1e886e55dd695541fdcd67420c2888645a495
 runc:
  Version:          1.1.10
  GitCommit:        v1.1.10-0-g18a0cb0
 docker-init:
  Version:          0.19.0
  GitCommit:        de40ad0

• docker info

Client: Docker Engine - Community
 Version:    24.0.7
 Context:    default
 Debug Mode: false
 Plugins:
  buildx: Docker Buildx (Docker Inc.)
    Version:  v0.11.2
    Path:     /usr/libexec/docker/cli-plugins/docker-buildx
  compose: Docker Compose (Docker Inc.)
    Version:  v2.21.0
    Path:     /usr/libexec/docker/cli-plugins/docker-compose

Server:
 Containers: 9
  Running: 9
  Paused: 0
  Stopped: 0
 Images: 10
 Server Version: 24.0.7
 Storage Driver: overlay2
  Backing Filesystem: extfs
  Supports d_type: true
  Using metacopy: false
  Native Overlay Diff: true
  userxattr: false
 Logging Driver: json-file
 Cgroup Driver: systemd
 Cgroup Version: 2
 Plugins:
  Volume: local
  Network: bridge host ipvlan macvlan null overlay
  Log: awslogs fluentd gcplogs gelf journald json-file local logentries splunk syslog
 Swarm: active
  NodeID: 12uz35moidx4ju4oz5033ztxo
  Is Manager: true
  ClusterID: l9nodrd56vh05xj8mhwjpousy
  Managers: 1
  Nodes: 1
  Default Address Pool: 10.0.0.0/8  
  SubnetSize: 24
  Data Path Port: 4789
  Orchestration:
   Task History Retention Limit: 5
  Raft:
   Snapshot Interval: 10000
   Number of Old Snapshots to Retain: 0
   Heartbeat Tick: 1
   Election Tick: 10
  Dispatcher:
   Heartbeat Period: 5 seconds
  CA Configuration:
   Expiry Duration: 3 months
   Force Rotate: 0
  Autolock Managers: false
  Root Rotation In Progress: false
  Node Address: 95.216.197.253
  Manager Addresses:
   95.216.197.253:2377
 Runtimes: runc io.containerd.runc.v2
 Default Runtime: runc
 Init Binary: docker-init
 containerd version: 3dd1e886e55dd695541fdcd67420c2888645a495
 runc version: v1.1.10-0-g18a0cb0
 init version: de40ad0
 Security Options:
  apparmor
  seccomp
   Profile: builtin
  cgroupns
 Kernel Version: 6.1.0-16-amd64
 Operating System: Debian GNU/Linux 12 (bookworm)
 OSType: linux
 Architecture: x86_64
 CPUs: 3
 Total Memory: 3.729GiB
 Name: manager
 ID: 9075201d-4316-4c4b-ad6d-e36fe4d8db31
 Docker Root Dir: /var/lib/docker
 Debug Mode: false
 Experimental: false
 Insecure Registries:
  127.0.0.0/8
 Live Restore Enabled: false

• docker compose version
  Docker Compose version v2.21.0

Additional context

• Docker Swarm
• Postgres: psql (PostgreSQL) 16.2 (Debian 16.2-1.pgdg120+2)

[linear]

PR-1025 SQL Syntax Error in Conversation Filtering via API on Chatwoot"

[pranavrajs]

Thanks for reporting this. We will have a fix for this soon. Meanwhile you can omit the query_operator in the last object.

[NedHoltz]

@pranavrajs Any update on this?