[OSSTEST,v2,03/41] SQL: Fix incorrect LIKE pattern syntax (literals)

Commit Message

Ian Jackson July 31, 2020, 11:37 a.m. UTC

LIKE takes a weird SQLish glob pattern, where % is like a glob *
and (relevantly, here) _ is like a glob ?.

Every _ in one of these LIKE patterns needs to be escaped with \.

Do that for all the literal LIKE patterns.

This fixes bugs.  Generally, bugs where the wrong rows might be
returned (except that the data probably doesn't have any such rows).

Signed-off-by: Ian Jackson <[e-mail]>
---
New in v2.
---
 mg-force-push                | 2 +-
 mg-report-host-usage-collect | 2 +-
 sg-report-flight             | 2 +-
 sg-report-host-history       | 6 +++---
 ts-logs-capture              | 2 +-
 5 files changed, 7 insertions(+), 7 deletions(-)

Patch

diff --git a/mg-force-push b/mg-force-push
index 001e0c47..3a701a11 100755
--- a/mg-force-push
+++ b/mg-force-push
@@ -54,7 +54,7 @@  END
         FROM rv url
         JOIN rv built
              ON url.job    = built.job
-            AND url.name   LIKE 'tree_%'
+            AND url.name   LIKE 'tree\_%'
             AND built.name = 'built_revision_' || substring(url.name, 6)
        WHERE url.val = ?
 END
diff --git a/mg-report-host-usage-collect b/mg-report-host-usage-collect
index 3fab490a..1944c8d7 100755
--- a/mg-report-host-usage-collect
+++ b/mg-report-host-usage-collect
@@ -166,7 +166,7 @@  END
         SELECT val, synth
           FROM runvars
          WHERE flight=? AND job=?
-           AND (name LIKE '%_host' OR name='host')
+           AND (name LIKE '%\_host' OR name='host')
 END
 
     my $finishq = db_prepare(<<END);
diff --git a/sg-report-flight b/sg-report-flight
index 0edb6e1a..831917a9 100755
--- a/sg-report-flight
+++ b/sg-report-flight
@@ -513,7 +513,7 @@  END
         my $revh= db_prepare(<<END);
             SELECT * FROM runvars
                 WHERE flight=$flight AND job='$j->{job}'
-                  AND name LIKE 'built_revision_%'
+                  AND name LIKE 'built\_revision\_%'
                 ORDER BY name
 END
         # We report in jobtext revisions in non-main-revision jobs, too.
diff --git a/sg-report-host-history b/sg-report-host-history
index c22a1704..7505b18b 100755
--- a/sg-report-host-history
+++ b/sg-report-host-history
@@ -37,7 +37,7 @@  our @blessings;
 
 open DEBUG, ">/dev/null";
 
-my $namecond= "(name = 'host' OR name LIKE '%_host')";
+my $namecond= "(name = 'host' OR name LIKE '%\_host')";
 csreadconfig();
 
 while (@ARGV && $ARGV[0] =~ m/^-/) {
@@ -256,7 +256,7 @@  END
 	  FROM runvars
 	 WHERE flight=? AND job=?
            AND (
-               name LIKE (? || '_power_%')
+               name LIKE (? || '\_power\_%')
            )
 END
 
@@ -456,7 +456,7 @@  foreach my $host (@ARGV) {
 	        SELECT DISTINCT val
 		  FROM runvars
 		 WHERE flight=?
-		   AND (name = 'host' OR name LIKE '%_host')
+		   AND (name = 'host' OR name LIKE '%\_host')
 END
             $hostsinflightq->execute($flight);
 	    while (my $row = $hostsinflightq->fetchrow_hashref()) {
diff --git a/ts-logs-capture b/ts-logs-capture
index d75a2fda..62c281b8 100755
--- a/ts-logs-capture
+++ b/ts-logs-capture
@@ -44,7 +44,7 @@  our (@allguests, @guests);
 sub find_guests () {
     my $sth= $dbh_tests->prepare(<<END);
         SELECT name FROM runvars WHERE flight=? AND job=?
-            AND name LIKE '%_domname'
+            AND name LIKE '%\_domname'
             ORDER BY name
 END
     $sth->execute($flight, $job);