[asterisk-bugs] [JIRA] (ASTERISK-26057) res_config_sqlite3 uses incorrect query - unnecessary escape

[Sean Bright (JIRA)]

    [ https://issues.asterisk.org/jira/browse/ASTERISK-26057?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=235228#comment-235228 ] 

Sean Bright edited comment on ASTERISK-26057 at 2/15/17 12:31 PM:
------------------------------------------------------------------

This is not an issue with {{res_config_sqlite3}}. The underscore character in a SQL {{LIKE}} operation is a wilcard character meaning "exactly one character." So once realtime tries for an exact match on the extension and doesn't find it, it looks for dialplan patterns (things that start with a literal {{_}}). The escape is needed and has been in the realtime code for at least 12 years.

Can you give an example of where MySQL matched something that SQLite is not matching?


was (Author: seanbright):
This is not an issue with {{res_config_sqlite3}}. The underscore ( {{_}} ) character in a SQL {{LIKE}} operation is a wilcard character meaning "exactly one character." So once realtime tries for an exact match on the extension and doesn't find it, it looks for dialplan patterns (things that start with a literal {{_}}). The escape is needed and has been in the realtime code for at least 12 years.

Can you give an example of where MySQL matched something that SQLite is not matching?

> res_config_sqlite3 uses incorrect query - unnecessary escape
> ------------------------------------------------------------
>
>                 Key: ASTERISK-26057
>                 URL: https://issues.asterisk.org/jira/browse/ASTERISK-26057
>             Project: Asterisk
>          Issue Type: Bug
>      Security Level: None
>          Components: Resources/res_config_sqlite3
>    Affects Versions: 11.20.0, 11.22.0
>         Environment: Ubuntu 14.04.3, 3.13.0-44-generic, x86_64, 
> Linux Mint 17.1, 3.13.0-37-generic, x86_64
>            Reporter: Stepan
>            Assignee: Stepan
>              Labels: realtime, sqlite3
>         Attachments: debug.txt, extconfig.txt, res_config_sqlite3.txt
>
>
> Hello,
> I try to configure realtime dialplan on sqlite3 engine (it has already perfectly work on mysql engine). But I got a problem that Asterisk can not find extensions on db file. I've see query in debug 
> {noformat}SELECT * FROM "sip_extensions" WHERE "exten" LIKE '\_%' AND "context" = 'statusprofi_107' AND "priority" = '1'{noformat}
> This query did not work directly on db file. But query like that 
> {noformat}SELECT * FROM "sip_extensions" WHERE "exten" LIKE '_%' AND "context" = 'statusprofi_107' AND "priority" = '1'{noformat}
> works fine. I think Asterisk does not need to escaping '_' symbol for sqlite. 
> [extconfig.conf|http://pastebin.com/yR9GMkpG]
> [res_config_sqlite3.conf|http://pastebin.com/Uj8UWHuq]
> [debug|http://pastebin.com/tv7NLggC]
> Please feel free if you'll need any additional information.



--
This message was sent by Atlassian JIRA
(v6.2#6252)