Escape closing quote character in table and column names

diff --git a/Changes b/Changes
index 25e79c2..48cc17a 100644 (file)
--- a/Changes
+++ b/Changes
@@ -1,5 +1,8 @@
 Revision history for SQL::Abstract
 
+    - New attribute 'escape_char' allowing for proper escape of quote_chars
+      present in an identifier
+
 revision 1.78  2014-05-28
 ----------------------------
     - Fix parsing of binary ops to correctly take up only a single LHS

diff --git a/lib/SQL/Abstract.pm b/lib/SQL/Abstract.pm
index c1ccbb2..85d5957 100644 (file)
--- a/lib/SQL/Abstract.pm
+++ b/lib/SQL/Abstract.pm
@@ -1262,10 +1262,11 @@ sub _quote {
   else {
     puke "Unsupported quote_char format: $_[0]->{quote_char}";
   }
+  my $esc = $_[0]->{escape_char} || $r;
 
   # parts containing * are naturally unquoted
   return join( $_[0]->{name_sep}||'', map
-    { $_ eq '*' ? $_ : $l . $_ . $r }
+    { $_ eq '*' ? $_ : do { (my $n = $_) =~ s/(\Q$esc\E|\Q$r\E)/$esc$1/g; $l . $n . $r } }
     ( $_[0]->{name_sep} ? split (/\Q$_[0]->{name_sep}\E/, $_[1] ) : $_[1] )
   );
 }
@@ -1850,6 +1851,21 @@ that generates SQL like this:
 Quoting is useful if you have tables or columns names that are reserved
 words in your database's SQL dialect.
 
+=item escape_char
+
+This is the character that will be used to escape L</quote_char>s appearing
+in an identifier before it has been quoted.
+
+The paramter default in case of a single L</quote_char> character is the quote
+character itself.
+
+When opening-closing-style quoting is used (L</quote_char> is an arrayref)
+this parameter defaults to the B<closing (right)> L</quote_char>. Occurences
+of the B<opening (left)> L</quote_char> within the identifier are currently left
+untouched. The default for opening-closing-style quotes may change in future
+versions, thus you are B<strongly encouraged> to specify the escape character
+explicitly.
+
 =item name_sep
 
 This is the character that separates a table and column name.  It is

diff --git a/t/01generate.t b/t/01generate.t
index 273d94d..2746b60 100644 (file)
--- a/t/01generate.t
+++ b/t/01generate.t
@@ -559,6 +559,21 @@ my @tests = (
               bind => [],
               warns => qr/\QSupplying an undefined argument to 'NOT LIKE' is deprecated/,
       },
+      {
+              func => 'select',
+              args => ['`test``table`', ['`test``column`']],
+              stmt => 'SELECT `test``column` FROM `test``table`',
+              stmt_q => 'SELECT ```test````column``` FROM ```test````table```',
+              bind => [],
+      },
+      {
+              func => 'select',
+              args => ['`test\\`table`', ['`test`\\column`']],
+              stmt => 'SELECT `test`\column` FROM `test\`table`',
+              stmt_q => 'SELECT `\`test\`\\\\column\`` FROM `\`test\\\\\`table\``',
+              esc  => '\\',
+              bind => [],
+      },
 );
 
 # check is( not) => undef
@@ -649,9 +664,15 @@ for my $t (@tests) {
 
   for my $quoted (0, 1) {
 
-    my $maker = SQL::Abstract->new(%$new, $quoted
-      ? (quote_char => '`', name_sep => '.')
-      : ()
+    my $maker = SQL::Abstract->new(
+      %$new,
+      ($quoted ? (
+        quote_char => '`',
+        name_sep => '.',
+        ( $t->{esc} ? (
+          escape_char => $t->{esc},
+        ) : ())
+      ) : ())
     );
 
     my($stmt, @bind);