From 8d61954ab2befe89b11d86f41eba4da6d578403a Mon Sep 17 00:00:00 2001
From: Tim Otten <totten@civicrm.org>
Date: Thu, 9 Apr 2020 14:38:45 -0700
Subject: [PATCH] CRM_Util_SQL - Add `interpolate()` helper

The `interpolate()` function is already available as the heart of
`CRM_Util_SQL_Insert` and `CRM_Utils_SQL_Select`, etc.  This just makes it
easier to use for random SQL statements that don't have a full OOP model.
---
 CRM/Utils/SQL.php                   | 32 ++++++++++++++++++++++++++
 tests/phpunit/CRM/Utils/SQLTest.php | 35 +++++++++++++++++++++++++++++
 2 files changed, 67 insertions(+)
 create mode 100644 tests/phpunit/CRM/Utils/SQLTest.php

diff --git a/CRM/Utils/SQL.php b/CRM/Utils/SQL.php
index 4e42f6eab0..70e1c5a26e 100644
--- a/CRM/Utils/SQL.php
+++ b/CRM/Utils/SQL.php
@@ -17,6 +17,38 @@
  */
 class CRM_Utils_SQL {
 
+  /**
+   * Given a string like "UPDATE some_table SET !field = @value", replace "!field" and "@value".
+   *
+   * This is syntactic sugar for using CRM_Utils_SQL_*::interpolate() without an OOP representation of the query.
+   *
+   * @param string $expr SQL expression
+   * @param null|array $args a list of values to insert into the SQL expression; keys are prefix-coded:
+   *   prefix '@' => escape SQL
+   *   prefix '#' => literal number, skip escaping but do validation
+   *   prefix '!' => literal, skip escaping and validation
+   *   if a value is an array, then it will be imploded
+   *
+   * PHP NULL's will be treated as SQL NULL's. The PHP string "null" will be treated as a string.
+   *
+   * @return string
+   */
+  public static function interpolate($expr, $args) {
+    if (!isset(Civi::$statics[__CLASS__][__FUNCTION__])) {
+      Civi::$statics[__CLASS__][__FUNCTION__] = new class extends CRM_Utils_SQL_BaseParamQuery {
+
+        public function __construct() {
+          $this->mode = CRM_Utils_SQL_BaseParamQuery::INTERPOLATE_INPUT;
+          $this->strict();
+        }
+
+      };
+    }
+    /** @var \CRM_Utils_SQL_BaseParamQuery $qb */
+    $qb = Civi::$statics[__CLASS__][__FUNCTION__];
+    return $qb->strict()->interpolate($expr, $args);
+  }
+
   /**
    * Helper function for adding the permissioned subquery from one entity onto another
    *
diff --git a/tests/phpunit/CRM/Utils/SQLTest.php b/tests/phpunit/CRM/Utils/SQLTest.php
new file mode 100644
index 0000000000..56f2416c49
--- /dev/null
+++ b/tests/phpunit/CRM/Utils/SQLTest.php
@@ -0,0 +1,35 @@
+<?php
+
+/**
+ * Class CRM_Utils_SQLTest
+ * @group headless
+ */
+class CRM_Utils_SQLTest extends CiviUnitTestCase {
+
+  public function testInterpolate() {
+    // This function is a thin wrapper for `CRM_Utils_SQL_BaseParamQuery::interpolate()`, which already has
+    // lots of coverage in other test classes. This test just checks the basic wiring.
+    $sql = CRM_Utils_SQL::interpolate('FROBNICATE some_table WITH MAX(!dynamicField) OVER #times USING (@list) OR (#ids) OR @item', [
+      '!dynamicField' => 'the(field)',
+      '#times' => 123,
+      '@list' => ['abc def', '45'],
+      '#ids' => [6, 7, 8],
+      '@item' => "it's text",
+    ]);
+    $this->assertEquals('FROBNICATE some_table WITH MAX(the(field)) OVER 123 USING ("abc def", "45") OR (6, 7, 8) OR "it\\\'s text"', $sql);
+  }
+
+  public function testInterpolateBad() {
+    try {
+      CRM_Utils_SQL::interpolate("UPDATE !the_table SET !the_field = @THE_VALUE", [
+        // MISSING: 'the_table'
+        'the_field' => 'my_field',
+        'the_value' => 'ny value',
+      ]);
+    }
+    catch (CRM_Core_Exception $e) {
+      $this->assertRegExp(';Cannot build query. Variable "!the_table" is unknown.;', $e->getMessage());
+    }
+  }
+
+}
-- 
2.25.1