Escape _ and % in SQL LIKE queries.

Fixes #40.

11 files changed, 55 insertions, 36 deletions

diff --git a/web/lib/api/v4/josm.rb b/web/lib/api/v4/josm.rb
index babfa5f..c3b2c7e 100644
--- a/web/lib/api/v4/josm.rb
+++ b/web/lib/api/v4/josm.rb
@@ -44,12 +44,12 @@ class Taginfo < Sinatra::Base
 
         total = @db.count('josm_style_rules').
 #            condition('style = ?', style).
-            condition_if("k LIKE '%' || ? || '%' OR v LIKE '%' || ? || '%'", params[:query], params[:query]).
+            condition_if("k LIKE ? ESCAPE '@' OR v LIKE ? ESCAPE '@'", like_contains(params[:query]), like_contains(params[:query])).
             get_first_value().to_i
 
         res = @db.select('SELECT * FROM josm_style_rules').
 #            condition('style = ?', style).
-            condition_if("k LIKE '%' || ? || '%' OR v LIKE '%' || ? || '%'", params[:query], params[:query]).
+            condition_if("k LIKE ? ESCAPE '@' OR v LIKE ? ESCAPE '@'", like_contains(params[:query]), like_contains(params[:query])).
             order_by(@ap.sortname, @ap.sortorder) { |o|
                 o.key :k
                 o.key :v
diff --git a/web/lib/api/v4/key.rb b/web/lib/api/v4/key.rb
index 66fae48..eb3cb84 100644
--- a/web/lib/api/v4/key.rb
+++ b/web/lib/api/v4/key.rb
@@ -34,7 +34,7 @@ class Taginfo < Sinatra::Base
         end
 
         cq = @db.count('db.key_combinations')
-        total = (params[:query].to_s != '' ? cq.condition("(key1 = ? AND key2 LIKE '%' || ? || '%') OR (key2 = ? AND key1 LIKE '%' || ? || '%')", key, params[:query], key, params[:query]) : cq.condition('key1 = ? OR key2 = ?', key, key)).
+        total = (params[:query].to_s != '' ? cq.condition("(key1=? AND key2 LIKE ? ESCAPE '@') OR (key2=? AND key1 LIKE ? ESCAPE '@')", key, like_contains(params[:query]), key, like_contains(params[:query])) : cq.condition('key1 = ? OR key2 = ?', key, key)).
             condition("count_#{filter_type} > 0").
             get_first_value().to_i
 
@@ -43,8 +43,8 @@ class Taginfo < Sinatra::Base
             get_first_value()
 
         res = (params[:query].to_s != '' ?
-            @db.select("SELECT p.key1 AS other_key, p.count_#{filter_type} AS together_count, k.count_#{filter_type} AS other_count, CAST(p.count_#{filter_type} AS REAL) / k.count_#{filter_type} AS from_fraction FROM db.key_combinations p, db.keys k WHERE p.key1=k.key AND p.key2=? AND (p.key1 LIKE '%' || ? || '%') AND p.count_#{filter_type} > 0
-                    UNION SELECT p.key2 AS other_key, p.count_#{filter_type} AS together_count, k.count_#{filter_type} AS other_count, CAST(p.count_#{filter_type} AS REAL) / k.count_#{filter_type} AS from_fraction FROM db.key_combinations p, db.keys k WHERE p.key2=k.key AND p.key1=? AND (p.key2 LIKE '%' || ? || '%') AND p.count_#{filter_type} > 0", key, params[:query], key, params[:query]) :
+            @db.select("SELECT p.key1 AS other_key, p.count_#{filter_type} AS together_count, k.count_#{filter_type} AS other_count, CAST(p.count_#{filter_type} AS REAL) / k.count_#{filter_type} AS from_fraction FROM db.key_combinations p, db.keys k WHERE p.key1=k.key AND p.key2=? AND (p.key1 LIKE ? ESCAPE '@') AND p.count_#{filter_type} > 0
+                    UNION SELECT p.key2 AS other_key, p.count_#{filter_type} AS together_count, k.count_#{filter_type} AS other_count, CAST(p.count_#{filter_type} AS REAL) / k.count_#{filter_type} AS from_fraction FROM db.key_combinations p, db.keys k WHERE p.key2=k.key AND p.key1=? AND (p.key2 LIKE ? ESCAPE '@') AND p.count_#{filter_type} > 0", key, like_contains(params[:query]), key, like_contains(params[:query])) :
             @db.select("SELECT p.key1 AS other_key, p.count_#{filter_type} AS together_count, k.count_#{filter_type} AS other_count, CAST(p.count_#{filter_type} AS REAL) / k.count_#{filter_type} AS from_fraction FROM db.key_combinations p, db.keys k WHERE p.key1=k.key AND p.key2=? AND p.count_#{filter_type} > 0 
                     UNION SELECT p.key2 AS other_key, p.count_#{filter_type} AS together_count, k.count_#{filter_type} AS other_count, CAST(p.count_#{filter_type} AS REAL) / k.count_#{filter_type} AS from_fraction FROM db.key_combinations p, db.keys k WHERE p.key2=k.key AND p.key1=? AND p.count_#{filter_type} > 0", key, key)).
             order_by(@ap.sortname, @ap.sortorder) { |o|
@@ -132,13 +132,13 @@ class Taginfo < Sinatra::Base
         total = @db.count('josm_style_rules').
 #            condition('style = ?', style).
             condition('k = ?', key).
-            condition_if("v LIKE '%' || ? || '%'", params[:query]).
+            condition_if("v LIKE ? ESCAPE '@'", like_contains(params[:query])).
             get_first_value().to_i
 
         res = @db.select('SELECT * FROM josm_style_rules').
 #            condition('style = ?', style).
             condition('k = ?', key).
-            condition_if("v LIKE '%' || ? || '%'", params[:query]).
+            condition_if("v LIKE ? ESCAPE '@'", like_contains(params[:query])).
             order_by(@ap.sortname, @ap.sortorder) { |o|
                 o.value :v
                 o.value :b
@@ -231,14 +231,14 @@ class Taginfo < Sinatra::Base
             total = @db.count('db.tags').
                 condition("count_#{filter_type} > 0").
                 condition('key = ?', key).
-                condition_if("value LIKE '%' || ? || '%'", params[:query]).
+                condition_if("value LIKE ? ESCAPE '@'", like_contains(params[:query])).
                 get_first_value()
         end
 
         res = @db.select('SELECT * FROM db.tags').
             condition("count_#{filter_type} > 0").
             condition('key = ?', key).
-            condition_if("value LIKE '%' || ? || '%'", params[:query]).
+            condition_if("value LIKE ? ESCAPE '@'", like_contains(params[:query])).
             order_by(@ap.sortname, @ap.sortorder) { |o|
                 o.value
                 o.count_all
diff --git a/web/lib/api/v4/keys.rb b/web/lib/api/v4/keys.rb
index 6ea2ba8..39131c6 100644
--- a/web/lib/api/v4/keys.rb
+++ b/web/lib/api/v4/keys.rb
@@ -45,12 +45,12 @@ class Taginfo < Sinatra::Base
         end
 
         total = @db.count('db.keys').
-            condition_if("key LIKE '%' || ? || '%'", params[:query]).
+            condition_if("key LIKE ? ESCAPE '@'", like_contains(params[:query])).
             conditions(filters).
             get_first_value().to_i
         
         res = @db.select('SELECT * FROM db.keys').
-            condition_if("key LIKE '%' || ? || '%'", params[:query]).
+            condition_if("key LIKE ? ESCAPE '@'", like_contains(params[:query])).
             conditions(filters).
             order_by(@ap.sortname, @ap.sortorder) { |o|
                 o.key
@@ -160,11 +160,11 @@ class Taginfo < Sinatra::Base
         end
 
         total = @db.count('wiki.wikipages_keys').
-            condition_if("key LIKE '%' || ? || '%'", params[:query]).
+            condition_if("key LIKE ? ESCAPE '@'", like_contains(params[:query])).
             get_first_value().to_i
 
         res = @db.select("SELECT key, coalesce(langs, '') AS langs FROM wiki.wikipages_keys").
-            condition_if("key LIKE '%' || ? || '%'", params[:query]).
+            condition_if("key LIKE ? ESCAPE '@'", like_contains(params[:query])).
             order_by(@ap.sortname, @ap.sortorder){ |o|
                 o.key
             }.
@@ -222,13 +222,13 @@ class Taginfo < Sinatra::Base
         total = @db.count('db.keys').
             condition('count_all > ?', min_count).
             condition("in_wiki#{english} = 0").
-            condition_if("key LIKE '%' || ? || '%'", params[:query]).
+            condition_if("key LIKE ? ESCAPE '@'", like_contains(params[:query])).
             get_first_value().to_i
         
         res = @db.select('SELECT * FROM db.keys').
             condition('count_all > ?', min_count).
             condition("in_wiki#{english} = 0").
-            condition_if("key LIKE '%' || ? || '%'", params[:query]).
+            condition_if("key LIKE ? ESCAPE '@'", like_contains(params[:query])).
             order_by(@ap.sortname, @ap.sortorder) { |o|
                 o.key
                 o.count_all
diff --git a/web/lib/api/v4/relation.rb b/web/lib/api/v4/relation.rb
index 1f7cc0f..6388caf 100644
--- a/web/lib/api/v4/relation.rb
+++ b/web/lib/api/v4/relation.rb
@@ -33,12 +33,12 @@ class Taginfo < Sinatra::Base
 
         total = @db.count('relation_roles').
             condition("rtype=?", rtype).
-            condition_if("role LIKE '%' || ? || '%'", params[:query]).
+            condition_if("role LIKE ? ESCAPE '@'", like_contains(params[:query])).
             get_first_value().to_i
 
         res = @db.select('SELECT * FROM relation_roles').
             condition("rtype=?", rtype).
-            condition_if("role LIKE '%' || ? || '%'", params[:query]).
+            condition_if("role LIKE ? ESCAPE '@'", like_contains(params[:query])).
             order_by(@ap.sortname, @ap.sortorder) { |o|
                 o.role
                 o.count_all_members      :count_all
diff --git a/web/lib/api/v4/relations.rb b/web/lib/api/v4/relations.rb
index faa334b..3296239 100644
--- a/web/lib/api/v4/relations.rb
+++ b/web/lib/api/v4/relations.rb
@@ -24,11 +24,11 @@ class Taginfo < Sinatra::Base
         :ui => '/relations'
     }) do
         total = @db.count('relation_types').
-            condition_if("rtype LIKE '%' || ? || '%'", params[:query]).
+            condition_if("rtype LIKE ? ESCAPE '@'", like_contains(params[:query])).
             get_first_value().to_i
 
         res = @db.select('SELECT * FROM relation_types').
-            condition_if("rtype LIKE '%' || ? || '%'", params[:query]).
+            condition_if("rtype LIKE ? ESCAPE '@'", like_contains(params[:query])).
             order_by(@ap.sortname, @ap.sortorder) { |o|
                 o.rtype
                 o.count
diff --git a/web/lib/api/v4/search.rb b/web/lib/api/v4/search.rb
index c1a2012..7cc6e66 100644
--- a/web/lib/api/v4/search.rb
+++ b/web/lib/api/v4/search.rb
@@ -64,9 +64,9 @@ class Taginfo < Sinatra::Base
     }) do
         query = params[:query].downcase
 
-        total = @db.count('wiki.words').condition("words LIKE ('%' || ? || '%')", query).get_first_value().to_i
+        total = @db.count('wiki.words').condition("words LIKE ? ESCAPE '@'", like_contains(query)).get_first_i
 
-        res = @db.select("SELECT key, value FROM wiki.words WHERE words LIKE ('%' || ? || '%')", query).
+        res = @db.select("SELECT key, value FROM wiki.words WHERE words LIKE ? ESCAPE '@'", like_contains(query)).
             order_by(@ap.sortname, @ap.sortorder) { |o|
                 o.key
                 o.value
@@ -102,11 +102,11 @@ class Taginfo < Sinatra::Base
         query = params[:query]
 
         total = @db.count('db.relation_roles').
-            condition_if("role LIKE '%' || ? || '%'", query).
+            condition_if("role LIKE ? ESCAPE '@'", like_contains(query)).
             get_first_value().to_i
 
         res = @db.select('SELECT * FROM db.relation_roles').
-            condition_if("role LIKE '%' || ? || '%'", query).
+            condition_if("role LIKE ? ESCAPE '@'", like_contains(query)).
             order_by(@ap.sortname, @ap.sortorder) { |o|
                 o.count_all
                 o.rtype
diff --git a/web/lib/api/v4/tag.rb b/web/lib/api/v4/tag.rb
index d05f73f..4554cb6 100644
--- a/web/lib/api/v4/tag.rb
+++ b/web/lib/api/v4/tag.rb
@@ -29,6 +29,7 @@ class Taginfo < Sinatra::Base
         key = params[:key]
         value = params[:value]
         filter_type = get_filter()
+        query_substr = like_contains(params[:query])
 
         if @ap.sortname == 'to_count'
             @ap.sortname = ['together_count']
@@ -40,8 +41,8 @@ class Taginfo < Sinatra::Base
 
         cq = @db.count('db.tag_combinations')
         total = (params[:query].to_s != '' ?
-                cq.condition("(key1=? AND value1=? AND (key2 LIKE '%' || ? || '%' OR value2 LIKE '%' || ? || '%')) OR (key2=? AND value2=? AND (key1 LIKE '%' || ? || '%' OR value2 LIKE '%' || ? || '%'))",
-                    key, value, params[:query], params[:query], key, value, params[:query], params[:query]) :
+                cq.condition("(key1=? AND value1=? AND (key2 LIKE ? ESCAPE '@' OR value2 LIKE ? ESCAPE '@')) OR (key2=? AND value2=? AND (key1 LIKE ? ESCAPE '@' OR value2 LIKE ? ESCAPE '@'))",
+                                    key,         value,           query_substr,               query_substr,           key,         value,           query_substr,               query_substr) :
                 cq.condition('(key1=? AND value1=?) OR (key2=? AND value2=?)', key, value, key, value)).
             condition("count_#{filter_type} > 0").
             get_first_value().to_i
@@ -52,10 +53,10 @@ class Taginfo < Sinatra::Base
             get_first_value()
 
         res = (params[:query].to_s != '' ?
-            @db.select("SELECT p.key1 AS other_key, p.value1 AS other_value, p.count_#{filter_type} AS together_count, k.count_#{filter_type} AS other_count, CAST(p.count_#{filter_type} AS REAL) / k.count_#{filter_type} AS from_fraction FROM db.tag_combinations p, db.selected_tags k WHERE p.key1=k.skey AND p.value1=k.svalue AND k.svalue != '' AND p.key2=? AND p.value2=? AND ((p.key1 LIKE '%' || ? || '%') OR (p.value1 LIKE '%' || ? || '%')) AND p.count_#{filter_type} > 0
-                    UNION SELECT p.key1 AS other_key, p.value1 AS other_value, p.count_#{filter_type} AS together_count, k.count_#{filter_type} AS other_count, CAST(p.count_#{filter_type} AS REAL) / k.count_#{filter_type} AS from_fraction FROM db.tag_combinations p, db.keys k WHERE p.key1=k.key AND p.value1='' AND p.key2=? AND p.value2=? AND ((p.key1 LIKE '%' || ? || '%') OR (p.value1 LIKE '%' || ? || '%')) AND p.count_#{filter_type} > 0
-                    UNION SELECT p.key2 AS other_key, p.value2 AS other_value, p.count_#{filter_type} AS together_count, k.count_#{filter_type} AS other_count, CAST(p.count_#{filter_type} AS REAL) / k.count_#{filter_type} AS from_fraction FROM db.tag_combinations p, db.selected_tags k WHERE p.key2=k.skey AND p.value2=k.svalue AND k.svalue != '' AND p.key1=? AND p.value1=? AND ((p.key2 LIKE '%' || ? || '%') OR (p.value2 LIKE '%' || ? || '%')) AND p.count_#{filter_type} > 0
-                    UNION SELECT p.key2 AS other_key, p.value2 AS other_value, p.count_#{filter_type} AS together_count, k.count_#{filter_type} AS other_count, CAST(p.count_#{filter_type} AS REAL) / k.count_#{filter_type} AS from_fraction FROM db.tag_combinations p, db.keys k WHERE p.key2=k.key AND p.value2='' AND p.key1=? AND p.value1=? AND ((p.key2 LIKE '%' || ? || '%') OR (p.value2 LIKE '%' || ? || '%')) AND p.count_#{filter_type} > 0", key, value, params[:query], params[:query], key, value, params[:query], params[:query], key, value, params[:query], params[:query], key, value, params[:query], params[:query]) :
+            @db.select("SELECT p.key1 AS other_key, p.value1 AS other_value, p.count_#{filter_type} AS together_count, k.count_#{filter_type} AS other_count, CAST(p.count_#{filter_type} AS REAL) / k.count_#{filter_type} AS from_fraction FROM db.tag_combinations p, db.selected_tags k WHERE p.key1=k.skey AND p.value1=k.svalue AND k.svalue != '' AND p.key2=? AND p.value2=? AND ((p.key1 LIKE ? ESCAPE '@') OR (p.value1 LIKE ? ESCAPE '@')) AND p.count_#{filter_type} > 0
+                    UNION SELECT p.key1 AS other_key, p.value1 AS other_value, p.count_#{filter_type} AS together_count, k.count_#{filter_type} AS other_count, CAST(p.count_#{filter_type} AS REAL) / k.count_#{filter_type} AS from_fraction FROM db.tag_combinations p, db.keys k WHERE p.key1=k.key AND p.value1='' AND p.key2=? AND p.value2=? AND ((p.key1 LIKE ? ESCAPE '@') OR (p.value1 LIKE ? ESCAPE '@')) AND p.count_#{filter_type} > 0
+                    UNION SELECT p.key2 AS other_key, p.value2 AS other_value, p.count_#{filter_type} AS together_count, k.count_#{filter_type} AS other_count, CAST(p.count_#{filter_type} AS REAL) / k.count_#{filter_type} AS from_fraction FROM db.tag_combinations p, db.selected_tags k WHERE p.key2=k.skey AND p.value2=k.svalue AND k.svalue != '' AND p.key1=? AND p.value1=? AND ((p.key2 LIKE ? ESCAPE '@') OR (p.value2 LIKE ? ESCAPE '@')) AND p.count_#{filter_type} > 0
+                    UNION SELECT p.key2 AS other_key, p.value2 AS other_value, p.count_#{filter_type} AS together_count, k.count_#{filter_type} AS other_count, CAST(p.count_#{filter_type} AS REAL) / k.count_#{filter_type} AS from_fraction FROM db.tag_combinations p, db.keys k WHERE p.key2=k.key AND p.value2='' AND p.key1=? AND p.value1=? AND ((p.key2 LIKE ? ESCAPE '@') OR (p.value2 LIKE ? ESCAPE '@')) AND p.count_#{filter_type} > 0", key, value, query_substr, query_substr, key, value, query_substr, query_substr, key, value, query_substr, query_substr, key, value, query_substr, query_substr) :
             @db.select("SELECT p.key1 AS other_key, p.value1 AS other_value, p.count_#{filter_type} AS together_count, k.count_#{filter_type} AS other_count, CAST(p.count_#{filter_type} AS REAL) / k.count_#{filter_type} AS from_fraction FROM db.tag_combinations p, db.selected_tags k WHERE p.key1=k.skey AND p.value1=k.svalue AND k.svalue != '' AND p.key2=? AND p.value2=? AND p.count_#{filter_type} > 0 
                     UNION SELECT p.key1 AS other_key, '' AS other_value, p.count_#{filter_type} AS together_count, k.count_#{filter_type} AS other_count, CAST(p.count_#{filter_type} AS REAL) / k.count_#{filter_type} AS from_fraction FROM db.tag_combinations p, db.keys k WHERE p.key1=k.key AND p.value1 = '' AND p.key2=? AND p.value2=? AND p.count_#{filter_type} > 0 
                     UNION SELECT p.key2 AS other_key, p.value2 AS other_value, p.count_#{filter_type} AS together_count, k.count_#{filter_type} AS other_count, CAST(p.count_#{filter_type} AS REAL) / k.count_#{filter_type} AS from_fraction FROM db.tag_combinations p, db.selected_tags k WHERE p.key2=k.skey AND p.value2=k.svalue AND k.svalue != '' AND p.key1=? AND p.value1=? AND p.count_#{filter_type} > 0
diff --git a/web/lib/api/v4/tags.rb b/web/lib/api/v4/tags.rb
index 77863a8..a5e2c0f 100644
--- a/web/lib/api/v4/tags.rb
+++ b/web/lib/api/v4/tags.rb
@@ -129,11 +129,11 @@ class Taginfo < Sinatra::Base
     }) do
 
         total = @db.count('db.selected_tags').
-            condition_if("(skey LIKE '%' || ? || '%') OR (svalue LIKE '%' || ? || '%')", params[:query], params[:query]).
+            condition_if("(skey LIKE ? ESCAPE '@') OR (svalue LIKE ? ESCAPE '@')", like_contains(params[:query]), like_contains(params[:query])).
             get_first_value().to_i
         
         res = @db.select('SELECT * FROM db.selected_tags').
-            condition_if("(skey LIKE '%' || ? || '%') OR (svalue LIKE '%' || ? || '%')", params[:query], params[:query]).
+            condition_if("(skey LIKE ? ESCAPE '@') OR (svalue LIKE ? ESCAPE '@')", like_contains(params[:query]), like_contains(params[:query])).
             order_by(@ap.sortname, @ap.sortorder) { |o|
                 o.tag :skey
                 o.tag :svalue
diff --git a/web/lib/sql.rb b/web/lib/sql.rb
index 032573e..286355f 100644
--- a/web/lib/sql.rb
+++ b/web/lib/sql.rb
@@ -118,10 +118,11 @@ module SQL
 
         def is_null(attribute)
             condition("#{attribute} IS NULL")
+            self
         end
 
         def condition_if(expression, *param)
-            if param.first.to_s != ''
+            unless param.first.to_s.match(/^%?%?$/)
                 condition(expression, *param)
             end
             self
diff --git a/web/lib/ui/search.rb b/web/lib/ui/search.rb
index 7be38b9..1c6a71c 100644
--- a/web/lib/ui/search.rb
+++ b/web/lib/ui/search.rb
@@ -46,20 +46,20 @@ END_XML
         if query =~ /^=(.*)/
             value = $1
             res = sel.
-                condition_if("value LIKE ? || '%'", value).
+                condition_if("value LIKE ? ESCAPE '@'", like_prefix(value)).
                 execute().
                 map{ |row| row['key'] + '=' + row['value'].to_s }
         elsif query =~ /^([^=]+)=(.*)/
             key = $1
             value = $2
             res = sel.
-                condition_if("key LIKE ? || '%'", key).
-                condition_if("value LIKE ? || '%'", value).
+                condition_if("key LIKE ? ESCAPE '@'", like_prefix(key)).
+                condition_if("value LIKE ? ESCAPE '@'", like_prefix(value)).
                 execute().
                 map{ |row| row['key'] + '=' + row['value'].to_s }
         else
             res = sel.
-                condition_if("key LIKE ? || '%'", query).
+                condition_if("key LIKE ? ESCAPE '@'", like_prefix(query)).
                 is_null('value').
                 execute().
                 map{ |row| row['key'] }
diff --git a/web/lib/utils.rb b/web/lib/utils.rb
index 5d7f2d6..6d2d270 100644
--- a/web/lib/utils.rb
+++ b/web/lib/utils.rb
@@ -161,6 +161,23 @@ end
 
 # ------------------------------------------------------------------------------
 
+# Escape % and _ special characters with @.
+# The @ was chosen because it is not a special character in SQL, in Regexes,
+# and isn't seen often in OSM tags. You must use "ESCAPE '@'" clause with LIKE!
+def like_escape(param)
+    param.to_s.gsub(/[_%@]/, '@\0')
+end
+
+def like_prefix(param)
+    like_escape(param) + '%'
+end
+
+def like_contains(param)
+    '%' + like_escape(param) + '%'
+end
+
+# ------------------------------------------------------------------------------
+
 # Like the 'get' method but will add a redirect for the same path with trailing / added
 def get!(path, &block)
     get path, &block