Summary:	ASTERISK-10875: ESCAPE clause in first parameter not escaped properly
Reporter:	Michael L. Young (elguero)	Labels:
Date Opened:	2007-11-24 09:58:45.000-0600	Date Closed:	2007-11-25 11:47:38.000-0600
Priority:	Major	Regression?	No
Status:	Closed/Complete	Components:	Resources/res_config_odbc
Versions:		Frequency of Occurrence
Related Issues:
Environment:		Attachments:	( 0) 20071124__bug11364.diff.txt
Description:	Recent change to Trunk Revision 89535 causes queries sent to MySQL to fail due to the ESCAPE clause not having the appropriate syntax when the ESCAPE clause is used in the middle of a query statement. Change line 292 to the following fixes it (at least for MySQL): snprintf(sql, sizeof(sql), "SELECT * FROM %s WHERE %s%s ?%s", table, newparam, op, strcasestr(newparam, "LIKE") ? " ESCAPE '\\\\'" : ""); ****** ADDITIONAL INFORMATION ****** Quote from http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html: Note Because MySQL uses C escape syntax in strings (for example, “\n” to represent a newline character), you must double any “\” that you use in LIKE strings. For example, to search for “\n”, specify it as “\\n”. To search for “\”, specify it as “\\\\”; this is because the backslashes are stripped once by the parser and again when the pattern match is made, leaving a single backslash to be matched against. (Exception: At the end of the pattern string, backslash can be specified as “\\”. At the end of the string, backslash stands for itself because there is nothing following to escape.)
Comments:	By: Tilghman Lesher (tilghman) 2007-11-24 10:48:44.000-0600 Can't do that, as it's incompatible with MS SQL Server.  Please try the attached patch, instead (for 1.4). By: Michael L. Young (elguero) 2007-11-24 16:51:01.000-0600 I had a feeling that MS SQL wouldn't like that. The above patch works.  I did not try it with the 1.4 branch but rather with trunk. Thanks By: Digium Subversion (svnbot) 2007-11-25 11:14:44.000-0600 Repository: asterisk Revision: 89559 U   branches/1.4/configs/res_odbc.conf.sample U   branches/1.4/include/asterisk/res_odbc.h U   branches/1.4/res/res_config_odbc.c U   branches/1.4/res/res_odbc.c ------------------------------------------------------------------------ r89559 | tilghman | 2007-11-25 11:14:42 -0600 (Sun, 25 Nov 2007) | 14 lines We previously attempted to use the ESCAPE clause to set the escape delimiter to a backslash.  Unfortunately, this does not universally work on all databases, since on databases which natively use the backslash as a delimiter, the backslash itself needs to be delimited, but on other databases that have no delimiter, backslashing the backslash causes an error. So the only solution that I can come up with is to create an option in res_odbc that explicitly specifies whether or not backslash is a native delimiter.  If it is, we use it natively; if not, we use the ESCAPE clause to make it one. Reported by: elguero Patch by: tilghman (Closes issue ASTERISK-10875) ------------------------------------------------------------------------ By: Digium Subversion (svnbot) 2007-11-25 11:47:38.000-0600 Repository: asterisk Revision: 89561 _U  trunk/ U   trunk/configs/res_odbc.conf.sample U   trunk/include/asterisk/res_odbc.h U   trunk/res/res_config_odbc.c U   trunk/res/res_odbc.c ------------------------------------------------------------------------ r89561 | tilghman | 2007-11-25 11:47:38 -0600 (Sun, 25 Nov 2007) | 22 lines Merged revisions 89559 via svnmerge from https://origsvn.digium.com/svn/asterisk/branches/1.4 ........ r89559 | tilghman | 2007-11-25 11:17:10 -0600 (Sun, 25 Nov 2007) | 14 lines We previously attempted to use the ESCAPE clause to set the escape delimiter to a backslash.  Unfortunately, this does not universally work on all databases, since on databases which natively use the backslash as a delimiter, the backslash itself needs to be delimited, but on other databases that have no delimiter, backslashing the backslash causes an error. So the only solution that I can come up with is to create an option in res_odbc that explicitly specifies whether or not backslash is a native delimiter.  If it is, we use it natively; if not, we use the ESCAPE clause to make it one. Reported by: elguero Patch by: tilghman (Closes issue ASTERISK-10875) ........ ------------------------------------------------------------------------