sqlite/test/zipfile2.test

# 2018 January 30
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
#

if {$tcl_version<8.6} {
  puts "Requires TCL 8.6 or later"
  return
}

set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix zipfile2

ifcapable !vtab {
  finish_test; return
}
if {[catch {load_static_extension db zipfile} error]} {
  puts "Skipping zipfile2 tests, hit load error: $error"
  finish_test; return
}

proc blobliteral {str} {
  set concat [string map {" " "" "\n" ""} $str]
  return "X'$concat'"
}

proc blob {str} {
  binary decode hex $str
}

proc findall {needle haystack} {
  set L [list]
  set start 0
  while { [set idx [string first $needle $haystack $start]]>=0 } {
    lappend L $idx
    set start [expr $idx+1]
  }
  set L
}

do_execsql_test 1.0 {
  CREATE VIRTUAL TABLE aaa USING zipfile('testzip');
  CREATE VIRTUAL TABLE bbb USING zipfile("testzip");
  CREATE VIRTUAL TABLE ccc USING zipfile(`testzip`);
  CREATE VIRTUAL TABLE ddd USING zipfile([testzip]);
  CREATE VIRTUAL TABLE eee USING zipfile(testzip);
  CREATE VIRTUAL TABLE fff USING zipfile('test''zip');
}

do_test 2.0 {
  forcedelete testdir
  file mkdir testdir
  execsql { CREATE VIRTUAL TABLE hhh USING zipfile('testdir') }
  lindex [catchsql { 
    SELECT * FROM hhh;
    INSERT INTO hhh(name, data) VALUES('1.txt', 'file data');
  }] 0 
} 1


set archive {
  504B0304140000080000D4A52BEC09F3B6E0110000001100000005000900612E
  747874555405000140420F00636F6E74656E7473206F6620612E747874504B03
  04140000080000D4A52BECD98916A7110000001100000005000900622E747874
  555405000140420F00636F6E74656E7473206F6620622E747874504B01021E03
  140000080000D4A52BEC09F3B6E0110000001100000005000900000000000000
  0000A48100000000612E747874555405000140420F00504B01021E0314000008
  0000D4A52BECD98916A71100000011000000050009000000000000000000A481
  3D000000622E747874555405000140420F00504B050600000000020002007800
  00007A0000000000
}

if 0 {
  # This test is broken - the archive generated is slightly different
  # depending on the zlib version used.
  do_execsql_test 3.1 {
    WITH contents(name,mtime,data) AS (
        VALUES('a.txt', 1000000, 'contents of a.txt') UNION ALL
        VALUES('b.txt', 1000000, 'contents of b.txt')
    ) SELECT quote( zipfile(name,NULL,mtime,data) ) FROM contents;
  } [blobliteral $archive]
}


set blob [blob $archive]
do_execsql_test 3.2 {
  SELECT name,mtime,data FROM zipfile($blob)
} {
  a.txt 1000000 {contents of a.txt} 
  b.txt 1000000 {contents of b.txt}
}

# Corrupt each of the 0x50 0x4B (ascii "PK") headers in the file
# Test that in each case this causes an error.
#
set L [findall 504B $archive]
for {set i 0} {$i < [llength $L]} {incr i} {
  set idx [lindex $L $i]
  set a [string replace $archive $idx [expr $idx+3] 0000]
  set blob [blob $a]
  do_catchsql_test 3.3.$i {
    SELECT name,mtime,data FROM zipfile($blob)
  } {/1 .*/}
}

# Change the "extra info id" for all extended-timestamp fields.
set L [findall 5554 $archive]
for {set i 0} {$i < [llength $L]} {incr i} {
  set idx [lindex $L $i]
  set a [string replace $archive $idx [expr $idx+3] 1234]
  set blob [blob $a]
  do_execsql_test 3.4.$i {
    SELECT name,data FROM zipfile($blob)
  } {
    a.txt {contents of a.txt} 
    b.txt {contents of b.txt}
  }
}

for {set i 0} {$i < [llength $L]} {incr i} {
  set idx [lindex $L $i]
  set a [string replace $archive [expr $idx+8] [expr $idx+9] 00]
  set blob [blob $a]
  do_execsql_test 3.5.$i {
    SELECT name,data FROM zipfile($blob)
  } {
    a.txt {contents of a.txt} 
    b.txt {contents of b.txt}
  }
}

# set blob [db one {
#   WITH contents(name,mtime,data) AS (
#     VALUES('a.txt', 1000000, 'aaaaaaaaaaaaaaaaaaaaaaa')
#   ) SELECT quote( zipfile(name,NULL,mtime,data) ) FROM contents;
# }]
# set blob [string range $blob 2 end]
# set blob [string range $blob 0 end-1]
# while {[string length $blob]>0} {
#   puts [string range $blob 0 63]
#   set blob [string range $blob 64 end]
# }
# exit

set archive2 {
  504B0304140000080800D4A52BEC08F54C6E050000001700000005000900612E
  747874555405000140420F004B4CC40A00504B01021E03140000080800D4A52B
  EC08F54C6E0500000017000000050009000000000000000000A4810000000061
  2E747874555405000140420F00504B050600000000010001003C000000310000
  000000
}
set blob [blob $archive2]
do_execsql_test 4.0 {
  SELECT name,mtime,data,method FROM zipfile($blob)
} {
  a.txt 1000000 aaaaaaaaaaaaaaaaaaaaaaa 8
}

set L [findall 17000000 $archive2]
set a $archive2
foreach i $L { set a [string replace $a $i [expr $i+7] 16000000] }
set blob [blob $a]
do_catchsql_test 4.1 {
  SELECT name,mtime,data,method FROM zipfile($blob)
} {1 {inflate() failed (0)}}

# Check the response to an unknown compression method (set data to NULL).
set blob [blob [string map {0800 0900} $archive2]]
do_execsql_test 4.2 {
  SELECT name,mtime,data IS NULL,method FROM zipfile($blob)
} {a.txt 1000000 1 9}

# Corrupt the EOCDS signature bytes in various ways.
foreach {tn sub} {
  1 {504B0500}
  2 {504B0006}
  3 {50000506}
  4 {004B0506}
} {
  set blob [blob [string map [list 504B0506 $sub] $archive2]]
  do_catchsql_test 4.3.$tn {
    SELECT * FROM zipfile($blob)
  } {1 {cannot find end of central directory record}}
}

#-------------------------------------------------------------------------
# Test that a zero-length file with a '/' at the end is treated as
# a directory (data IS NULL). Even if the mode doesn't indicate
# that it is a directory.

do_test 5.0 {
  set blob [db one {
    WITH c(n, d) AS (
      SELECT 'notadir', ''
    )
    SELECT zipfile(n, d) FROM c
 }]

  set hex [binary encode hex $blob]
  set hex [string map {6e6f7461646972 6e6f746164692f} $hex] 
  set blob2 [binary decode hex $hex]

  execsql { SELECT name, data IS NULL FROM zipfile($blob2) }
} {notadi/ 1}

#-------------------------------------------------------------------------
# Test that duplicate entries may not be created using UPDATE
# statements.
#
forcedelete test.zip
do_execsql_test 6.0 {
  CREATE VIRTUAL TABLE temp.zip USING zipfile('test.zip'); 
  INSERT INTO temp.zip (name,data) VALUES ('test1','test'); 
  INSERT INTO temp.zip (name,data) VALUES ('test2','test'); 
}
do_catchsql_test 6.1 {
  UPDATE temp.zip SET name='test1' WHERE name='test2'
} {1 {duplicate name: "test1"}}

forcedelete test.zip
do_catchsql_test 6.2 {
  DROP TABLE zip;
  CREATE VIRTUAL TABLE temp.zip USING zipfile('test.zip'); 
  INSERT INTO temp.zip (name,data) VALUES ('test','test'); 
  UPDATE  temp.zip set name=name||'new' where name='test'; 
  INSERT INTO temp.zip (name,data) VALUES ('test','test'); 
  UPDATE  temp.zip set name=name||'new' where name='test'; 
} {1 {duplicate name: "testnew"}}

forcedelete test.zip
do_execsql_test 6.3 {
  INSERT INTO temp.zip (name,data) VALUES ('test1','test'); 
  INSERT INTO temp.zip (name,data) VALUES ('test2','test'); 
  UPDATE OR REPLACE zip SET name='test2' WHERE name='test1';
  SELECT name FROM zip;
} {test2}

#-------------------------------------------------------------------------
# Test a crafted corrupt file.
#
proc make_corrupt_file {fname} {
  set central_dir_offset 1000
  set lfh_offset 200
  set nFile 60000
  set nExtra 60000
  set nComment 0
  
  # Build local file header at lfh_offset
  set lfh ""
  append lfh [binary format isssssiiiss 0x04034b50 20 0 0 0 0 0 0 0 1 0]
  append lfh "A"
  
  # Build central directory structure (CDS) at central_dir_offset
  set cds ""
  append cds [binary format issssssiiisssssii 0x02014b50 0 20 0 0 0 0 0 0 0 $nFile $nExtra $nComment 0 0 0 $lfh_offset]
  
  # Payload following CDS: filename + extra + comment
  set payload ""
  append payload [string repeat "B" $nFile]
  append payload [string repeat "C" $nExtra]
  
  # EOCD at end
  set cdsize [expr {[string length $cds] + [string length $payload]}]
  set eocd ""
  append eocd [binary format issssiis 0x06054b50 0 0 1 1 $cdsize $central_dir_offset 0]
  
  # Assemble file
  set buf [string repeat "X" $lfh_offset]
  append buf $lfh
  
  set buflen [string length $buf]
  if {$central_dir_offset > $buflen} {
      append buf [string repeat "\x00" [expr {$central_dir_offset - $buflen}]]
  }
  
  append buf $cds
  append buf $payload
  append buf $eocd
  
  # Write to file
  set f [open $fname wb]
  fconfigure $f -translation binary
  puts -nonewline $f $buf
  close $f
}

forcedelete test.zip
make_corrupt_file test.zip
do_execsql_test 7.0 {
  DROP TABLE IF EXISTS t1;
  CREATE VIRTUAL TABLE t1 USING zipfile('test.zip');
}
do_execsql_test 7.1 {
  SELECT length(name) FROM t1;
} {60000}


# https://sqlite.org/forum/forumpost/721a05d2c5
#
if {[catch { load_static_extension db fileio }]==0} {
  forcedelete test.zip
  set fd [open test.zip wb]
  fconfigure $fd -translation binary
  puts -nonewline $fd [db one {SELECT X'504b0506000000000100010030000000160000000000504b01021400140000000000000000000000000000000000000000000100010000000000000000000000000000006100'}]
  close $fd

  do_catchsql_test 8.0 {
    SELECT name,sz FROM zipfile(readfile('test.zip'));
  } {1 {failed to read LFH at offset 0}}
}

# The EOCD record stores the number of entries as a 16-bit value, and
# zipfile does not support zip64. Check that attempts to create an
# archive with more than 65535 entries fail with an error, instead of
# writing a wrapped entry count that causes later writes to discard
# entries.
#
if {[catch { load_static_extension db fileio }]==0} {
  do_catchsql_test 9.0 {
    WITH c(i) AS (SELECT 1 UNION ALL SELECT i+1 FROM c WHERE i<65536)
    SELECT length(zipfile('f'||i, 'x')) FROM c;
  } {1 {too many entries for zipfile(); max: 65535}}

  forcedelete test.zip
  do_execsql_test 9.1 {
    WITH c(i) AS (SELECT 1 UNION ALL SELECT i+1 FROM c WHERE i<65535)
    SELECT writefile('test.zip', zipfile('f'||i, 'x'))>0 FROM c;
    SELECT count(*) FROM zipfile('test.zip');
  } {1 65535}

  do_execsql_test 9.2 {
    CREATE VIRTUAL TABLE temp.zz USING zipfile('test.zip');
    SELECT count(*) FROM zz;
  } {65535}

  do_catchsql_test 9.3 {
    INSERT INTO zz(name, data) VALUES('g', 'y');
  } {1 {too many entries; max: 65535}}
  do_execsql_test 9.3.1 {
    SELECT count(*) FROM zipfile(readfile('test.zip'));
  } {65535}

  # Operations that do not increase the number of entries still work.
  do_execsql_test 9.4 {
    INSERT OR REPLACE INTO zz(name, data) VALUES('f1', 'y');
    INSERT OR IGNORE INTO zz(name, data) VALUES('f2', 'z');
    UPDATE zz SET name='renamed' WHERE name='f3';
    SELECT count(*) FROM zipfile(readfile('test.zip'));
    SELECT name, data FROM zz WHERE name IN ('f1', 'f2', 'renamed');
  } {65535 f2 x renamed x f1 y}

  # After a DELETE, there is room for exactly one more entry.
  do_execsql_test 9.5 {
    DELETE FROM zz WHERE name='f4';
    INSERT INTO zz(name, data) VALUES('g', 'y');
    SELECT count(*) FROM zipfile(readfile('test.zip'));
  } {65535}
  do_catchsql_test 9.6 {
    INSERT INTO zz(name, data) VALUES('h', 'y');
  } {1 {too many entries; max: 65535}}

  do_execsql_test 9.7 {
    DROP TABLE temp.zz;
    SELECT count(*) FROM zipfile('test.zip');
  } {65535}
}

#-------------------------------------------------------------------------
# Bug 7faad02e21dced4d. The LEVEL constraint on fsdir() must give the
# same results as if it were not passed to xBestIndex, even for values
# that are out of range, negative, NULL or non-integer.
#
if {[catch { load_static_extension db fileio }]==0} {
  forcedelete test_fsdir
  file mkdir test_fsdir/a/b/c
  foreach f {test_fsdir/x test_fsdir/a/y test_fsdir/a/b/z test_fsdir/a/b/c/w} {
    close [open $f w]
  }

  set i 0
  foreach {w res} {
    {level<=2147483647}  8
    {level<=4294967296}  8
    {level<=4294967297}  8
    {level<0}            0
    {level<=-1}          0
    {level<=0}           0
    {level<1}            0
    {level<=1}           1
    {level<2}            1
    {level<=2}           3
    {level<2.5}          3
    {level<=2.5}         3
    {level<=NULL}        0
    {level<NULL}         0
    {level=NULL}         0
    {level<'x'}          8
    {level<x'00'}        8
    {level=3}            2
    {level=3.0}          2
    {level=3.5}          0
    {level=4294967299}   0
  } {
    incr i
    do_execsql_test 10.$i.1 "
      SELECT count(*) FROM fsdir('test_fsdir') WHERE $w
    " $res
    do_execsql_test 10.$i.2 "
      SELECT count(*) FROM fsdir('test_fsdir') WHERE +$w
    " $res
  }

  # Check that the LEVEL constraint still limits the depth of the
  # search. Make directory test_fsdir/a/b/c (itself at level 4) unreadable.
  # Then queries that need the entries within it (level 5) fail, and
  # those that do not succeed.
  #
  if {$::tcl_platform(platform)=="unix"} {
    file attributes test_fsdir/a/b/c -permissions 0000
    if {[catch {glob -nocomplain test_fsdir/a/b/c/*}]} {
      do_execsql_test 10.100 {
        SELECT count(*) FROM fsdir('test_fsdir') WHERE level<=4
      } {7}
      do_execsql_test 10.101 {
        SELECT count(*) FROM fsdir('test_fsdir') WHERE level<5
      } {7}
      do_execsql_test 10.102 {
        SELECT count(*) FROM fsdir('test_fsdir') WHERE level<4.5
      } {7}
      do_execsql_test 10.103 {
        SELECT count(*) FROM fsdir('test_fsdir') WHERE level=4
      } {2}
      do_catchsql_test 10.104 {
        SELECT count(*) FROM fsdir('test_fsdir') WHERE level<5.5
      } {1 {cannot read directory: test_fsdir/a/b/c}}
      do_catchsql_test 10.105 {
        SELECT count(*) FROM fsdir('test_fsdir') WHERE level<=5
      } {1 {cannot read directory: test_fsdir/a/b/c}}
    }
    file attributes test_fsdir/a/b/c -permissions 0755
  }
  forcedelete test_fsdir
}

finish_test