| Subject: | `IN` rewriting does not account for `NOT IN` syntax |
Try this:
print sql_interp('SELECT COUNT(*) FROM foo WHERE bar NOT IN', []);
It will print the following:
SELECT COUNT(*) FROM foo WHERE bar 1=0
This is malformed SQL and won’t run. What it *should* have printed is
either of the following:
SELECT COUNT(*) FROM foo WHERE NOT 1=0
SELECT COUNT(*) FROM foo WHERE 1=1
Instead, the code as it stands deals only with unnegated IN operators
and considers the NOT to be a column name. This is of course easy to
work around by swapping the column name and the NOT:
print sql_interp('SELECT COUNT(*) FROM foo WHERE NOT bar IN', []);
That leads to syntactically and semantically correct output:
SELECT COUNT(*) FROM foo WHERE NOT 1=0
However, this limitation is quite surprising, and it’s not hard to fix
(patch attached as well, in case the web interface mangles it):
--- Interp.pm.orig 2007-11-15 13:46:42.000000000 +0100
+++ Interp.pm 2007-11-15 13:54:16.000000000 +0100
@@ -165,3 +165,4 @@
elsif (ref $item) {
- if ($sql =~ /\bIN\s*$/si) {
+ if ($sql =~ /\b(NOT\s+)?IN\s*$/si) {
+ my $not = quotemeta($1) || '';
$item = [ $$item ] if ref $item eq 'SCALAR';
@@ -169,3 +170,4 @@
if (@$item == 0) {
- $sql =~ s/$id_match\s+IN\s*$/1=0/si or croak
'ASSERT';
+ my $dummy_expr = $not ? '1=1' : '1=0';
+ $sql =~ s/$id_match\s+IN\s*$/$dummy_expr/si or
croak 'ASSERT';
}
| Subject: | Interp.pm.diff |
--- Interp.pm.orig 2007-11-15 13:46:42.000000000 +0100
+++ Interp.pm 2007-11-15 13:54:16.000000000 +0100
@@ -165,3 +165,4 @@
elsif (ref $item) {
- if ($sql =~ /\bIN\s*$/si) {
+ if ($sql =~ /\b(NOT\s+)?IN\s*$/si) {
+ my $not = quotemeta($1) || '';
$item = [ $$item ] if ref $item eq 'SCALAR';
@@ -169,3 +170,4 @@
if (@$item == 0) {
- $sql =~ s/$id_match\s+IN\s*$/1=0/si or croak 'ASSERT';
+ my $dummy_expr = $not ? '1=1' : '1=0';
+ $sql =~ s/$id_match\s+IN\s*$/$dummy_expr/si or croak 'ASSERT';
}