fix(bug-3): backtick-escape database and table names in data diff SQL

jasdeepkhalsa · jasdeepkhalsa · commit 3f7f94d32820 · 2026-03-25T20:30:47.000Z
MySQL data diff queries in LocalTableData now use backtick-quoted `db`.`table` references instead of bare db.table. This prevents MySQL from interpreting hyphens in database names (e.g. 'my-db') as subtraction operators, which caused PDO syntax error 1064. Fixed in getOldNewDiff() (2 queries) and getChangeDiff() (1 query + key column references). Added unit test verifying MySQLDialect::quote() correctly handles hyphenated names, names with backticks, and names with spaces. Refs: bugs.md Bug #3, PR #92
diff --git a/src/DB/Data/LocalTableData.php b/src/DB/Data/LocalTableData.php
@@ -333,12 +333,12 @@ public function getOldNewDiff($table, $key) {
 
         $this->setFetchMode(\PDO::FETCH_NAMED);
         $result1 = $this->source->select(
-           "SELECT $columnsAUtf FROM {$db1}.{$table} as a
-            LEFT JOIN {$db2}.{$table} as b ON $keyCols WHERE $keyNulls2
+           "SELECT $columnsAUtf FROM `{$db1}`.`{$table}` as a
+            LEFT JOIN `{$db2}`.`{$table}` as b ON $keyCols WHERE $keyNulls2
         ");
         $result2 = $this->source->select(
-           "SELECT $columnsBUtf FROM {$db2}.{$table} as b
-            LEFT JOIN {$db1}.{$table} as a ON $keyCols WHERE $keyNulls1
+           "SELECT $columnsBUtf FROM `{$db2}`.`{$table}` as b
+            LEFT JOIN `{$db1}`.`{$table}` as a ON $keyCols WHERE $keyNulls1
         ");
         $this->setFetchMode(\PDO::FETCH_ASSOC);
 
@@ -392,15 +392,15 @@ public function getChangeDiff($table, $key) {
         $columnsB   = implode(',', $wrapCast($columns2, 'b'));
         
         $keyCols = implode(self::SQL_AND, array_map(function($el) {
-            return "a.{$el} = b.{$el}";
+            return "`a`.`{$el}` = `b`.`{$el}`";
         }, $key));
 
         $this->setFetchMode(\PDO::FETCH_NAMED);
         $result = $this->source->select(
            "SELECT * FROM (
                 SELECT $columnsAas, $columnsBas, SHA2(concat($columnsA), 256) AS hash1,
-                SHA2(concat($columnsB), 256) AS hash2 FROM {$db1}.{$table} as a 
-                INNER JOIN {$db2}.{$table} as b  
+                SHA2(concat($columnsB), 256) AS hash2 FROM `{$db1}`.`{$table}` as a 
+                INNER JOIN `{$db2}`.`{$table}` as b  
                 ON $keyCols
             ) t WHERE hash1 <> hash2");
         $this->setFetchMode(\PDO::FETCH_ASSOC);
diff --git a/tests/Unit/MySQLDialectQuoteTest.php b/tests/Unit/MySQLDialectQuoteTest.php
@@ -0,0 +1,33 @@
+<?php
+
+namespace DBDiff\Tests\Unit;
+
+use PHPUnit\Framework\TestCase;
+use DBDiff\SQLGen\Dialect\MySQLDialect;
+
+class MySQLDialectQuoteTest extends TestCase
+{
+    public function testQuoteSimpleName(): void
+    {
+        $dialect = new MySQLDialect();
+        $this->assertSame('`users`', $dialect->quote('users'));
+    }
+
+    public function testQuoteHyphenatedName(): void
+    {
+        $dialect = new MySQLDialect();
+        $this->assertSame('`my-database`', $dialect->quote('my-database'));
+    }
+
+    public function testQuoteNameWithBacktick(): void
+    {
+        $dialect = new MySQLDialect();
+        $this->assertSame('`name``with``ticks`', $dialect->quote('name`with`ticks'));
+    }
+
+    public function testQuoteNameWithSpaces(): void
+    {
+        $dialect = new MySQLDialect();
+        $this->assertSame('`table name`', $dialect->quote('table name'));
+    }
+}
