| Summary: | ASTERISK-26057: res_config_sqlite3 uses incorrect query - unnecessary escape | ||||
| Reporter: | Stepan (tcp22) | Labels: | realtime sqlite3 | ||
| Date Opened: | 2016-05-25 05:42:40 | Date Closed: | 2017-02-21 07:55:37.000-0600 | ||
| Priority: | Major | Regression? | |||
| Status: | Closed/Complete | Components: | Resources/res_config_sqlite3 | ||
| Versions: | 11.20.0 11.22.0 | Frequency of Occurrence | Constant | ||
| Related Issues: |
| ||||
| Environment: | Ubuntu 14.04.3, 3.13.0-44-generic, x86_64, Linux Mint 17.1, 3.13.0-37-generic, x86_64 | Attachments: | ( 0) debug.txt ( 1) extconfig.txt ( 2) res_config_sqlite3.txt | ||
| Description: | 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 seen 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. | ||||
| Comments: | By: Asterisk Team (asteriskteam) 2016-05-25 05:42:41.097-0500 Thanks for creating a report! The issue has entered the triage process. That means the issue will wait in this status until a Bug Marshal has an opportunity to review the issue. Once the issue has been reviewed you will receive comments regarding the next steps towards resolution. A good first step is for you to review the [Asterisk Issue Guidelines|https://wiki.asterisk.org/wiki/display/AST/Asterisk+Issue+Guidelines] if you haven't already. The guidelines detail what is expected from an Asterisk issue report. Then, if you are submitting a patch, please review the [Patch Contribution Process|https://wiki.asterisk.org/wiki/display/AST/Patch+Contribution+Process]. By: Rusty Newton (rnewton) 2016-05-25 16:44:29.625-0500 Your two example queries look identical. I believe the first had an \ before the _ is that correct? Can you double-check your example? By: Stepan (tcp22) 2016-05-26 01:11:12.897-0500 You're right. First example has \ before the _, but it can be seen only in edit mode. I guess posting system hided it. Fixed. Sorry for my inattention. By: Rusty Newton (rnewton) 2016-06-02 20:10:42.361-0500 We require that debug and configs be attached to the issue in case the external sources disappear. Please note that for the future. I'm going ahead and attaching your files to the issue. By: Rusty Newton (rnewton) 2016-06-02 20:11:01.132-0500 Thanks for the update on the \. The example makes more sense now. By: Sean Bright (seanbright) 2017-02-15 12:30:29.518-0600 This is not an issue with {{res_config_sqlite3}}. The underscore character in a SQL {{LIKE}} comparison is a wildcard 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? By: Sean Bright (seanbright) 2017-02-15 12:48:45.411-0600 On second thought. This is not a bug. If you can produce an example of something that works with the MySQL realtime backend that does not work with the SQLite realtime backend, feel free to re-open. By: Sean Bright (seanbright) 2017-02-16 06:54:52.450-0600 After some testing, I realized that I was wrong. You're correct that it won't match as intended. The backslash escape sequence is not universal. By: Friendly Automation (friendly-automation) 2017-02-21 06:18:36.400-0600 Change 4978 merged by zuul: realtime: Fix LIKE escaping in SQL backends [https://gerrit.asterisk.org/4978|https://gerrit.asterisk.org/4978] By: Friendly Automation (friendly-automation) 2017-02-21 06:18:43.174-0600 Change 4979 merged by zuul: realtime: Fix LIKE escaping in SQL backends [https://gerrit.asterisk.org/4979|https://gerrit.asterisk.org/4979] By: Friendly Automation (friendly-automation) 2017-02-21 06:27:31.889-0600 Change 4980 merged by zuul: realtime: Fix LIKE escaping in SQL backends [https://gerrit.asterisk.org/4980|https://gerrit.asterisk.org/4980] | ||||