SQLProxy doesn't parse queries correctly

Title: SQLProxy filter query parsing returns a false even after seemingly correctly parsing a query

Description: A simple envoy with SQLProxy filter does return SQL responses properly but stats show that the Query parser parsed with errors. Subsequently, no dynamic metadata seem to populate for further filters.

The SQLProxy should parse the query properly and emit relevent dynamic metadata.

Repro steps: Using latest(1.32) contrib binary from : https://github.com/envoyproxy/envoy/releases/download/v1.32.0/envoy-contrib-1.32.0-linux-x86_64 simple mysql docker setup: mysql.yaml

services:
  mysql:
    image: mysql:8.0   # MySQL version 8.0
    container_name: mysql_db
    environment:
      MYSQL_ROOT_PASSWORD: rootpassword      # Root password for MySQL
      MYSQL_DATABASE: mydatabase             # Database name to initialize
      MYSQL_USER: user                      # Custom user
      MYSQL_PASSWORD: password              # Password for custom user
    volumes:
      - ./mysql_data:/var/lib/mysql          # Persist database data
      - ./db_init:/docker-entrypoint-initdb.d  # Mount folder with SQL scripts
    ports:
      - "3306:3306"                          # Map MySQL port 3306 to host
    command: --skip-ssl
    networks:
      - mysql_net

networks:
  mysql_net:
    driver: bridge

sample db_init/init.sql script:

-- Table 1: Users (username, password, and user_id)
CREATE TABLE users (
            user_id INT AUTO_INCREMENT PRIMARY KEY,  -- user_id will be the unique key
            username VARCHAR(50) NOT NULL UNIQUE,    -- Unique username
            password VARCHAR(255) NOT NULL           -- User password
);

INSERT INTO users (username, password) VALUES
('user1', 'password1'),
('user2', 'password2'),
('user3', 'password3'),
('user4', 'password4');

envoy config yaml (picked from https://www.envoyproxy.io/docs/envoy/latest/configuration/listeners/network_filters/mysql_proxy_filter)

static_resources:
  listeners:
  - name: listener_0
    address:
      socket_address:
        address: 0.0.0.0
        port_value: 10000
    filter_chains:
    - filters:
      - name: envoy.filters.network.mysql_proxy
        typed_config:
          "@type": type.googleapis.com/envoy.extensions.filters.network.mysql_proxy.v3.MySQLProxy
          stat_prefix: sql_proxy
      - name: envoy.filters.network.tcp_proxy
        typed_config:
          "@type": type.googleapis.com/envoy.extensions.filters.network.tcp_proxy.v3.TcpProxy
          stat_prefix: tcp_proxy
          cluster: service_envoyproxy_io

  clusters:
  - name: service_envoyproxy_io
    connect_timeout: 0.25s
    type: STATIC
    # Comment out the following line to test on v6 networks
    dns_lookup_family: V4_ONLY
    load_assignment:
      cluster_name: service_envoyproxy_io
      endpoints:
      - lb_endpoints:
        - endpoint:
            address:
              socket_address:
                address: 0.0.0.0
                port_value: 3306

admin:
  access_log_path: "/tmp/admin_access.log"
  address:
    socket_address:
      address: 0.0.0.0  # Admin listens on all interfaces
      port_value: 9901

start sql server: docker compose -f mysql.yaml up -d run envoy proxy: ./envoy-contrib-1.32.0-linux-x86_64 -c envoy.yaml --log-level trace connect to sql using mysql client: mysql -h 127.0.0.1 -P 10000 -u root -prootpassword --ssl-mode=DISABLED -e "select * FROM mydatabase.users;"

Logs: Admin stats: mysql.sql_proxy.auth_switch_request: 2 mysql.sql_proxy.decoder_errors: 0 mysql.sql_proxy.login_attempts: 2 mysql.sql_proxy.login_failures: 0 mysql.sql_proxy.protocol_errors: 0 mysql.sql_proxy.queries_parse_error: 4 mysql.sql_proxy.queries_parsed: 0 mysql.sql_proxy.sessions: 2 mysql.sql_proxy.upgraded_to_ssl: 0

Trace logs:

...
[2024-10-16 12:28:32.823][33845][trace][filter] [contrib/mysql_proxy/filters/network/source/mysql_decoder_impl.cc:169] mysql_proxy: decoding 37 bytes                                                                                        
[2024-10-16 12:28:32.823][33845][trace][filter] [contrib/mysql_proxy/filters/network/source/mysql_utils.cc:215] mysql_proxy: MYSQL-hdrseq 0, len 33                                                                                          
[2024-10-16 12:28:32.823][33845][trace][filter] [contrib/mysql_proxy/filters/network/source/mysql_decoder_impl.cc:182] mysql_proxy: seq 0, len 33                                                                                            
[2024-10-16 12:28:32.823][33845][trace][filter] [contrib/mysql_proxy/filters/network/source/mysql_decoder_impl.cc:15] mysql_proxy: parsing message, seq 0, len 33                                                                            
[2024-10-16 12:28:32.823][33845][trace][filter] [contrib/mysql_proxy/filters/network/source/mysql_filter.cc:119] [Tags: "ConnectionId":"2"] mysql_proxy: query processed select * FROM mydatabase.users, result false, cmd type 3            
[2024-10-16 12:28:32.823][33845][trace][filter] [contrib/mysql_proxy/filters/network/source/mysql_decoder_impl.cc:165] mysql_proxy: msg parsed, session in state 9                                                                           
[2024-10-16 12:28:32.823][33845][trace][filter] [contrib/mysql_proxy/filters/network/source/mysql_decoder_impl.cc:212] mysql_proxy: 0 bytes remaining in buffer                                                                              
[2024-10-16 12:28:32.823][33845][trace][filter] [source/common/tcp_proxy/tcp_proxy.cc:754] [Tags: "ConnectionId":"2"] downstream connection received 37 bytes, end_stream=false, has upstream true                                           
[2024-10-16 12:28:32.823][33845][trace][connection] [source/common/network/connection_impl.cc:529] [Tags: "ConnectionId":"3"] writing 37 bytes, end_stream false                                                                             
[2024-10-16 12:28:32.823][33845][trace][connection] [source/common/network/connection_impl.cc:614] [Tags: "ConnectionId":"3"] socket event: 2                                                                                                
[2024-10-16 12:28:32.823][33845][trace][connection] [source/common/network/connection_impl.cc:737] [Tags: "ConnectionId":"3"] write ready           
...

Subsequent use of RBAC rules as given in examples:

    filter_chains:
    - filters:
      - name: envoy.filters.network.mysql_proxy
        typed_config:
          "@type": type.googleapis.com/envoy.extensions.filters.network.mysql_proxy.v3.MySQLProxy
          stat_prefix: sql_proxy

      - name: envoy.filters.network.rbac
        typed_config:
          "@type": type.googleapis.com/envoy.extensions.filters.network.rbac.v3.RBAC
          stat_prefix: rbac
          rules:
            action: DENY
            policies:
              "product-viewer":
                permissions:
                - metadata:
                    filter: envoy.filters.network.mysql_proxy
                    path:
                    - key: users.mydatabase
                    value:
                      list_match:
                        one_of:
                          string_match:
                            exact: select
                principals:
                - any: true
      - name: envoy.filters.network.tcp_proxy
        typed_config:
          "@type": type.googleapis.com/envoy.extensions.filters.network.tcp_proxy.v3.TcpProxy
          stat_prefix: tcp_proxy
          cluster: service_envoyproxy_io

does not block the queries as intended: Trace logs:

[2024-10-16 12:36:52.956][33976][trace][filter] [contrib/mysql_proxy/filters/network/source/mysql_decoder_impl.cc:169] mysql_proxy: decoding 37 bytes                                                                                        
[2024-10-16 12:36:52.956][33976][trace][filter] [contrib/mysql_proxy/filters/network/source/mysql_utils.cc:215] mysql_proxy: MYSQL-hdrseq 0, len 33                                                                                          
[2024-10-16 12:36:52.956][33976][trace][filter] [contrib/mysql_proxy/filters/network/source/mysql_decoder_impl.cc:182] mysql_proxy: seq 0, len 33                                                                                            
[2024-10-16 12:36:52.956][33976][trace][filter] [contrib/mysql_proxy/filters/network/source/mysql_decoder_impl.cc:15] mysql_proxy: parsing message, seq 0, len 33                                                                            
[2024-10-16 12:36:52.956][33976][trace][filter] [contrib/mysql_proxy/filters/network/source/mysql_filter.cc:119] [Tags: "ConnectionId":"0"] mysql_proxy: query processed select * FROM mydatabase.users, result false, cmd type 3            
[2024-10-16 12:36:52.956][33976][trace][filter] [contrib/mysql_proxy/filters/network/source/mysql_decoder_impl.cc:165] mysql_proxy: msg parsed, session in state 9                                                                           
[2024-10-16 12:36:52.956][33976][trace][filter] [contrib/mysql_proxy/filters/network/source/mysql_decoder_impl.cc:212] mysql_proxy: 0 bytes remaining in buffer                                                                              
[2024-10-16 12:36:52.956][33976][debug][rbac] [source/extensions/filters/network/rbac/rbac_filter.cc:90] checking connection: requestedServerName: , sourceIP: 127.0.0.1:46846, directRemoteIP: 127.0.0.1:46846,remoteIP: 127.0.0.1:46846, lo
calAddress: 127.0.0.1:10000, ssl: none, dynamicMetadata: filter_metadata {                                                                                                                                                                   
  key: "envoy.filters.network.mysql_proxy"                                                                                                                                                                                                   
  value {                                                                                                                                                                                                                                    
  }                                                                                                                                                                                                                                          
}                                                                                                                                                                                                                                            

[2024-10-16 12:36:52.956][33976][trace][filter] [source/common/tcp_proxy/tcp_proxy.cc:754] [Tags: "ConnectionId":"0"] downstream connection received 37 bytes, end_stream=false, has upstream true                                           
[2024-10-16 12:36:52.956][33976][trace][connection] [source/common/network/connection_impl.cc:529] [Tags: "ConnectionId":"1"] writing 37 bytes, end_stream false                                                                             
[2024-10-16 12:36:52.956][33976][trace][connection] [source/common/network/connection_impl.cc:614] [Tags: "ConnectionId":"1"] socket event: 2

I think that this because of the library which is used to parse SQL queries. The same problem has been observed in postgres filter. There is a proposal to use postgres library to parse SQL queries (it should also work for mysql), but requires some work and testing.

Thank you for the comment -- it saved me time since I was about to try my luck with postgres. Are there any fixes apart from manual rebuild with the postgres library? Also are you referring to include/sqlparser/SQLParser.h library in SQLUtils?

Also, @cpakulski , looking at past few issues around this, it seems that simple statements should still be able to pass the filter. Can you explain if "select * from table;" is already too complex or if there might be some config issue for me here? If it is, please help me with a simple query that would work with the existing parser. I am doing some benchmarking and do not necessarily need to run complex queries.

Are there any fixes apart from manual rebuild with the postgres library? Also are you referring to include/sqlparser/SQLParser.h library in SQLUtils?

It is not a matter of simple manual rebuild. The potential parser must tokenize query and metadata must be extracted from that query. The potential new parser must be modified a bit to create Envoy-compatible metadata.

Can you explain if "select * from table;" is already too complex or if there might be some config issue for me here?

Simple queries work. I remember that long time ago I used RBAC and simple queries like select, update, insert and was able to create metadata and feed it to rbac filter.

Simple queries work. I remember that long time ago I used RBAC and simple queries like select, update, insert and was able to create metadata and feed it to rbac filter.

@cpakulski Thanks! In the above example I am trying simple select queries but was seeing parser errors still. After a lot of attempts, I switched from using mysql cli client to a python script to talk to the server and somehow those queries do get parsed fine. I think it might be that the mysql client sends the query in a way that the current parser isn't able to parse it well.

However, the RBAC policy still doesnt work right: the emiited metadata is

[2024-10-17 17:36:25.302][164803][debug][rbac] [source/extensions/filters/network/rbac/rbac_filter.cc:90] checking connection: requestedServerName: , sourceIP: 127.0.0.1:43598, directRemoteIP: 127.0.0.1:43598,remoteIP: 127.0.0.1:43598, 
localAddress: 127.0.0.1:10000, ssl: none, dynamicMetadata: filter_metadata {                                                                                                                                                                
  key: "envoy.filters.network.mysql_proxy"                                                                                                                                                                                                  
  value {                                                                                                                                                                                                                                   
    fields {                                                                                                                                                                                                                                
      key: "users.mydatabase"                                                                                                                                                                                                               
      value {                                                                                                                                                                                                                               
        list_value {                                                                                                                                                                                                                        
          values {                                                                                                                                                                                                                          
            string_value: "select"                                                                                                                                                                                                          
          }                                                                                                                                                                                                                                 
        }                                                                                                                                                                                                                                   
      }                                                                                                                                                                                                                                     
    }                                                                                                                                                                                                                                       
  }                                                                                                                                                                                                                                         
}

and my configured RBAC is:

- name: envoy.filters.network.rbac
        typed_config:
          "@type": type.googleapis.com/envoy.extensions.filters.network.rbac.v3.RBAC
          stat_prefix: rbac
          rules:
            action: DENY
            policies:
              "product-viewer":
                permissions:
                - metadata:
                    filter: envoy.filters.network.mysql_proxy
                    path:
                    - key: users.mydatabase
                    value:
                      list_match:
                        one_of:
                          string_match:
                            exact: select
                principals:
                - any: true

Could you please help if I am configuring my RBAC incorrectly in any way?

nevermind, I realized I had not enforced the RBAC with enforcement_type: CONTINUOUS tag.

Thanks for the discussion, closing this -- Feel free to open it if you'd like to use it as tracker for the better parser.

I think it might be that the mysql client sends the query in a way that the current parser isn't able to parse it well.

It is also possible that mysql client by default sends encrypted traffic and mysql filter is not able to decode it.

It is also possible that mysql client by default sends encrypted traffic and mysql filter is not able to decode it.

Yeah, I did turn off TLS/SSL on both server and client, but yes, it might be mangling the query somehow.

envoyproxy / envoy

SQLProxy doesn't parse queries correctly #36655