From 16c28764d17543f5208e6da6365523d2628f3371 Mon Sep 17 00:00:00 2001
From: Tim Otten <totten@civicrm.org>
Date: Tue, 16 May 2023 19:10:59 -0700
Subject: [PATCH] CRM_Utils_SQL - Add option `allowLiterals()` for building
 queries with literal SQL snippets

---
 CRM/Utils/SQL/EscapeStringTrait.php        | 20 +++++++++++-
 CRM/Utils/SQL/Literal.php                  | 38 ++++++++++++++++++++++
 tests/phpunit/CRM/Utils/SQL/InsertTest.php | 14 ++++++++
 3 files changed, 71 insertions(+), 1 deletion(-)
 create mode 100644 CRM/Utils/SQL/Literal.php

diff --git a/CRM/Utils/SQL/EscapeStringTrait.php b/CRM/Utils/SQL/EscapeStringTrait.php
index 4f64da25bd..427f661217 100644
--- a/CRM/Utils/SQL/EscapeStringTrait.php
+++ b/CRM/Utils/SQL/EscapeStringTrait.php
@@ -2,13 +2,31 @@
 
 trait CRM_Utils_SQL_EscapeStringTrait {
 
+  protected $allowLiterals = FALSE;
+
+  public function allowLiterals(bool $allowLiterals = TRUE) {
+    $this->allowLiterals = $allowLiterals;
+    return $this;
+  }
+
   /**
    * @param string|null $value
    * @return string
    *   SQL expression, e.g. "it\'s great" (with-quotes) or NULL (without-quotes)
    */
   public function escapeString($value) {
-    return $value === NULL ? 'NULL' : '"' . CRM_Core_DAO::escapeString($value) . '"';
+    if ($value === NULL) {
+      return 'NULL';
+    }
+    if ($value instanceof CRM_Utils_SQL_Literal) {
+      if ($this->allowLiterals) {
+        return $value->getValue();
+      }
+      else {
+        throw new CRM_Core_Exception('SQL builder does not support literal expressions. Must call allowLiterals() first.');
+      }
+    }
+    return '"' . CRM_Core_DAO::escapeString($value) . '"';
   }
 
 }
diff --git a/CRM/Utils/SQL/Literal.php b/CRM/Utils/SQL/Literal.php
new file mode 100644
index 0000000000..d2306ef6bf
--- /dev/null
+++ b/CRM/Utils/SQL/Literal.php
@@ -0,0 +1,38 @@
+<?php
+/*
+ +--------------------------------------------------------------------+
+ | Copyright CiviCRM LLC. All rights reserved.                        |
+ |                                                                    |
+ | This work is published under the GNU AGPLv3 license with some      |
+ | permitted exceptions and without any warranty. For full license    |
+ | and copyright information, see https://civicrm.org/licensing       |
+ +--------------------------------------------------------------------+
+ */
+
+/**
+ * A literal fragment of SQL.
+ *
+ * Ex: new CRM_Utils_SQL_Literal('CONCAT(@foo, sysdate())')
+ */
+class CRM_Utils_SQL_Literal {
+
+  /**
+   * @var string
+   */
+  private $value;
+
+  /**
+   * @param string $value
+   */
+  public function __construct(string $value) {
+    $this->value = $value;
+  }
+
+  /**
+   * @return string
+   */
+  public function getValue() {
+    return $this->value;
+  }
+
+}
diff --git a/tests/phpunit/CRM/Utils/SQL/InsertTest.php b/tests/phpunit/CRM/Utils/SQL/InsertTest.php
index de3a801eb5..a7d781d55c 100644
--- a/tests/phpunit/CRM/Utils/SQL/InsertTest.php
+++ b/tests/phpunit/CRM/Utils/SQL/InsertTest.php
@@ -43,4 +43,18 @@ class CRM_Utils_SQL_InsertTest extends CiviUnitTestCase {
     $this->assertLike($expected, $insert->toSQL());
   }
 
+  public function testLiteral() {
+    $insert = CRM_Utils_SQL_Insert::into('foo')
+      ->allowLiterals()
+      ->row(['first' => new CRM_Utils_SQL_Literal('1+1'), 'second' => '2'])
+      ->row(['second' => '2b', 'first' => new CRM_Utils_SQL_Literal('CONCAT(@foo, @bar)')]);
+    $expected = '
+      INSERT INTO foo (`first`,`second`) VALUES
+      (1+1,"2"),
+      (CONCAT(@foo, @bar),"2b")
+    ';
+    $this->assertLike($expected, $insert->toSQL());
+
+  }
+
 }
-- 
2.25.1