realtime: Fix LIKE escaping in SQL backends

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

1 files changed, 39 insertions, 11 deletions

diff --git a/res/res_config_pgsql.c b/res/res_config_pgsql.c
index 25a482705..f0859617d 100644
--- a/res/res_config_pgsql.c
+++ b/res/res_config_pgsql.c
@@ -382,6 +382,9 @@ static struct columns *find_column(struct tables *t, const char *colname)
 	return NULL;
 }
 
+#define IS_SQL_LIKE_CLAUSE(x) ((x) && ast_ends_with(x, " LIKE"))
+static char *ESCAPE_CLAUSE = " ESCAPE '\\'";
+
 static struct ast_variable *realtime_pgsql(const char *database, const char *tablename, const struct ast_variable *fields)
 {
 	RAII_VAR(PGresult *, result, NULL, PQclear);
@@ -391,6 +394,7 @@ static struct ast_variable *realtime_pgsql(const char *database, const char *tab
 	char *stringp;
 	char *chunk;
 	char *op;
+	char *escape = "";
 	const struct ast_variable *field = fields;
 	struct ast_variable *var = NULL, *prev = NULL;
 
@@ -418,7 +422,14 @@ static struct ast_variable *realtime_pgsql(const char *database, const char *tab
 
 	/* Create the first part of the query using the first parameter/value pairs we just extracted
 	   If there is only 1 set, then we have our query. Otherwise, loop thru the list and concat */
-	op = strchr(field->name, ' ') ? "" : " =";
+	if (!strchr(field->name, ' ')) {
+		op = " =";
+	} else {
+		op = "";
+		if (IS_SQL_LIKE_CLAUSE(field->name)) {
+			escape = ESCAPE_CLAUSE;
+		}
+	}
 
 	ESCAPE_STRING(escapebuf, field->value);
 	if (pgresult) {
@@ -426,12 +437,17 @@ static struct ast_variable *realtime_pgsql(const char *database, const char *tab
 		return NULL;
 	}
 
-	ast_str_set(&sql, 0, "SELECT * FROM %s WHERE %s%s '%s'", tablename, field->name, op, ast_str_buffer(escapebuf));
+	ast_str_set(&sql, 0, "SELECT * FROM %s WHERE %s%s '%s'%s", tablename, field->name, op, ast_str_buffer(escapebuf), escape);
 	while ((field = field->next)) {
-		if (!strchr(field->name, ' '))
+		escape = "";
+		if (!strchr(field->name, ' ')) {
 			op = " =";
-		else
+		} else {
 			op = "";
+			if (IS_SQL_LIKE_CLAUSE(field->name)) {
+				escape = ESCAPE_CLAUSE;
+			}
+		}
 
 		ESCAPE_STRING(escapebuf, field->value);
 		if (pgresult) {
@@ -439,7 +455,7 @@ static struct ast_variable *realtime_pgsql(const char *database, const char *tab
 			return NULL;
 		}
 
-		ast_str_append(&sql, 0, " AND %s%s '%s'", field->name, op, ast_str_buffer(escapebuf));
+		ast_str_append(&sql, 0, " AND %s%s '%s'%s", field->name, op, ast_str_buffer(escapebuf), escape);
 	}
 
 	/* We now have our complete statement; Lets connect to the server and execute it. */
@@ -505,6 +521,7 @@ static struct ast_config *realtime_multi_pgsql(const char *database, const char
 	char *stringp;
 	char *chunk;
 	char *op;
+	char *escape = "";
 	struct ast_variable *var = NULL;
 	struct ast_config *cfg = NULL;
 	struct ast_category *cat = NULL;
@@ -543,10 +560,15 @@ static struct ast_config *realtime_multi_pgsql(const char *database, const char
 	/* Create the first part of the query using the first parameter/value pairs we just extracted
 	   If there is only 1 set, then we have our query. Otherwise, loop thru the list and concat */
 
-	if (!strchr(field->name, ' '))
+	if (!strchr(field->name, ' ')) {
 		op = " =";
-	else
+		escape = "";
+	} else {
 		op = "";
+		if (IS_SQL_LIKE_CLAUSE(field->name)) {
+			escape = ESCAPE_CLAUSE;
+		}
+	}
 
 	ESCAPE_STRING(escapebuf, field->value);
 	if (pgresult) {
@@ -555,12 +577,18 @@ static struct ast_config *realtime_multi_pgsql(const char *database, const char
 		return NULL;
 	}
 
-	ast_str_set(&sql, 0, "SELECT * FROM %s WHERE %s%s '%s'", table, field->name, op, ast_str_buffer(escapebuf));
+	ast_str_set(&sql, 0, "SELECT * FROM %s WHERE %s%s '%s'%s", table, field->name, op, ast_str_buffer(escapebuf), escape);
 	while ((field = field->next)) {
-		if (!strchr(field->name, ' '))
+		escape = "";
+		if (!strchr(field->name, ' ')) {
 			op = " =";
-		else
+			escape = "";
+		} else {
 			op = "";
+			if (IS_SQL_LIKE_CLAUSE(field->name)) {
+				escape = ESCAPE_CLAUSE;
+			}
+		}
 
 		ESCAPE_STRING(escapebuf, field->value);
 		if (pgresult) {
@@ -569,7 +597,7 @@ static struct ast_config *realtime_multi_pgsql(const char *database, const char
 			return NULL;
 		}
 
-		ast_str_append(&sql, 0, " AND %s%s '%s'", field->name, op, ast_str_buffer(escapebuf));
+		ast_str_append(&sql, 0, " AND %s%s '%s'%s", field->name, op, ast_str_buffer(escapebuf), escape);
 	}
 
 	if (initfield) {