Skip to content
Snippets Groups Projects
  • Sean Bright's avatar
    e93f2a51
    realtime: Fix LIKE escaping in SQL backends · e93f2a51
    Sean Bright authored
    The realtime framework allows for components to look up values using a
    LIKE clause with similar syntax to SQL's. pbx_realtime uses this
    functionality to search for pattern matching extensions that start with
    an underscore (_).
    
    When passing an underscore to SQL's LIKE clause, it will be interpreted
    as a wildcard matching a single character and therefore needs to be
    escaped. It is (for better or for worse) the responsibility of the
    component that is querying realtime to escape it with a backslash before
    passing it in. Some RDBMs support escape characters by default, but the
    SQL92 standard explicitly says that there are no escape characters
    unless they are specified with an ESCAPE clause, e.g.
    
    	SELECT * FROM table WHERE column LIKE '\_%' ESCAPE '\'
    
    This patch instructs 3 backends - res_config_mysql, res_config_pgsql,
    and res_config_sqlite3 - to use the ESCAPE clause where appropriate.
    
    Looking through documentation and source tarballs, I was able to
    determine that the ESCAPE clause is supported in:
    
    MySQL 5.0.15   (released 2005-10-22 - earliest version available from
                    archives)
    PostgreSQL 7.1 (released 2001-04-13)
    SQLite 3.1.0   (released 2005-01-21)
    
    The versions of the relevant libraries that we depend on to access MySQL
    and PostgreSQL will not work on versions that old, and I've added an
    explicit check in res_config_sqlite3 to only use the ESCAPE clause when
    we have a sufficiently new version of SQLite3.
    
    res_config_odbc already handles the escape characters appropriately, so
    no changes were required there.
    
    ASTERISK-15858 #close
    Reported by: Humberto Figuera
    
    ASTERISK-26057 #close
    Reported by: Stepan
    
    Change-Id: I93117fbb874189ae819f4a31222df7c82cd20efa
    e93f2a51
    History
    realtime: Fix LIKE escaping in SQL backends
    Sean Bright authored
    The realtime framework allows for components to look up values using a
    LIKE clause with similar syntax to SQL's. pbx_realtime uses this
    functionality to search for pattern matching extensions that start with
    an underscore (_).
    
    When passing an underscore to SQL's LIKE clause, it will be interpreted
    as a wildcard matching a single character and therefore needs to be
    escaped. It is (for better or for worse) the responsibility of the
    component that is querying realtime to escape it with a backslash before
    passing it in. Some RDBMs support escape characters by default, but the
    SQL92 standard explicitly says that there are no escape characters
    unless they are specified with an ESCAPE clause, e.g.
    
    	SELECT * FROM table WHERE column LIKE '\_%' ESCAPE '\'
    
    This patch instructs 3 backends - res_config_mysql, res_config_pgsql,
    and res_config_sqlite3 - to use the ESCAPE clause where appropriate.
    
    Looking through documentation and source tarballs, I was able to
    determine that the ESCAPE clause is supported in:
    
    MySQL 5.0.15   (released 2005-10-22 - earliest version available from
                    archives)
    PostgreSQL 7.1 (released 2001-04-13)
    SQLite 3.1.0   (released 2005-01-21)
    
    The versions of the relevant libraries that we depend on to access MySQL
    and PostgreSQL will not work on versions that old, and I've added an
    explicit check in res_config_sqlite3 to only use the ESCAPE clause when
    we have a sufficiently new version of SQLite3.
    
    res_config_odbc already handles the escape characters appropriately, so
    no changes were required there.
    
    ASTERISK-15858 #close
    Reported by: Humberto Figuera
    
    ASTERISK-26057 #close
    Reported by: Stepan
    
    Change-Id: I93117fbb874189ae819f4a31222df7c82cd20efa