/ Check-in [65a2e131]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:If an SQLITE_DELETE authorization callback returns SQLITE_IGNORE, proceed with the delete operation but disable the truncate optimization. (CVS 5845)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:65a2e131732399f0f14f982eb0689482fdb87b6c
User & Date: danielk1977 2008-10-27 15:34:33
Context
2008-10-28
17:52
Avoid exposing internal interfaces sqlite_attach() and sqlite_detach() as SQL scalar functions. Ticket #3466. (CVS 5846) check-in: 679c0b35 user: danielk1977 tags: trunk
2008-10-27
15:34
If an SQLITE_DELETE authorization callback returns SQLITE_IGNORE, proceed with the delete operation but disable the truncate optimization. (CVS 5845) check-in: 65a2e131 user: danielk1977 tags: trunk
13:59
Make sqlite3_count_changes() and total_changes() work with "DELETE FROM ". (CVS 5844)
check-in: e68e4282 user: danielk1977 tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/delete.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
230
231
232
233
234
235
236

237
238
239
240
241
242
243
...
277
278
279
280
281
282
283
284


285
286
287
288
289
290
291
...
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** in order to generate code for DELETE FROM statements.
**
** $Id: delete.c,v 1.184 2008/10/27 13:59:34 danielk1977 Exp $
*/
#include "sqliteInt.h"

/*
** Look up every table that is named in pSrc.  If any table is not found,
** add an error message to pParse->zErrMsg and return NULL.  If all tables
** are found, return a pointer to the last table.
................................................................................
  int iCur;              /* VDBE Cursor number for pTab */
  sqlite3 *db;           /* Main database structure */
  AuthContext sContext;  /* Authorization context */
  int oldIdx = -1;       /* Cursor for the OLD table of AFTER triggers */
  NameContext sNC;       /* Name context to resolve expressions in */
  int iDb;               /* Database number */
  int memCnt = -1;       /* Memory cell used for change counting */


#ifndef SQLITE_OMIT_TRIGGER
  int isView;                  /* True if attempting to delete from a view */
  int triggers_exist = 0;      /* True if any triggers exist */
#endif
  int iBeginAfterTrigger;      /* Address of after trigger program */
  int iEndAfterTrigger;        /* Exit of after trigger program */
................................................................................

  if( sqlite3IsReadOnly(pParse, pTab, triggers_exist) ){
    goto delete_from_cleanup;
  }
  iDb = sqlite3SchemaToIndex(db, pTab->pSchema);
  assert( iDb<db->nDb );
  zDb = db->aDb[iDb].zName;
  if( sqlite3AuthCheck(pParse, SQLITE_DELETE, pTab->zName, 0, zDb) ){


    goto delete_from_cleanup;
  }
  assert(!isView || triggers_exist);

  /* If pTab is really a view, make sure it has been initialized.
  */
  if( sqlite3ViewGetColumnNames(pParse, pTab) ){
................................................................................
  }

#ifndef SQLITE_OMIT_TRUNCATE_OPTIMIZATION
  /* Special case: A DELETE without a WHERE clause deletes everything.
  ** It is easier just to erase the whole table.  Note, however, that
  ** this means that the row change count will be incorrect.
  */
  if( pWhere==0 && !triggers_exist && !IsVirtual(pTab) ){
    assert( !isView );
    sqlite3VdbeAddOp3(v, OP_Clear, pTab->tnum, iDb, memCnt);
    if( !pParse->nested ){
      sqlite3VdbeChangeP4(v, -1, pTab->zName, P4_STATIC);
    }
    for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
      assert( pIdx->pSchema==pTab->pSchema );







|







 







>







 







|
>
>







 







|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
...
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
...
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** in order to generate code for DELETE FROM statements.
**
** $Id: delete.c,v 1.185 2008/10/27 15:34:33 danielk1977 Exp $
*/
#include "sqliteInt.h"

/*
** Look up every table that is named in pSrc.  If any table is not found,
** add an error message to pParse->zErrMsg and return NULL.  If all tables
** are found, return a pointer to the last table.
................................................................................
  int iCur;              /* VDBE Cursor number for pTab */
  sqlite3 *db;           /* Main database structure */
  AuthContext sContext;  /* Authorization context */
  int oldIdx = -1;       /* Cursor for the OLD table of AFTER triggers */
  NameContext sNC;       /* Name context to resolve expressions in */
  int iDb;               /* Database number */
  int memCnt = -1;       /* Memory cell used for change counting */
  int rcauth;            /* Value returned by authorization callback */

#ifndef SQLITE_OMIT_TRIGGER
  int isView;                  /* True if attempting to delete from a view */
  int triggers_exist = 0;      /* True if any triggers exist */
#endif
  int iBeginAfterTrigger;      /* Address of after trigger program */
  int iEndAfterTrigger;        /* Exit of after trigger program */
................................................................................

  if( sqlite3IsReadOnly(pParse, pTab, triggers_exist) ){
    goto delete_from_cleanup;
  }
  iDb = sqlite3SchemaToIndex(db, pTab->pSchema);
  assert( iDb<db->nDb );
  zDb = db->aDb[iDb].zName;
  rcauth = sqlite3AuthCheck(pParse, SQLITE_DELETE, pTab->zName, 0, zDb);
  assert( rcauth==SQLITE_OK || rcauth==SQLITE_DENY || rcauth==SQLITE_IGNORE );
  if( rcauth==SQLITE_DENY ){
    goto delete_from_cleanup;
  }
  assert(!isView || triggers_exist);

  /* If pTab is really a view, make sure it has been initialized.
  */
  if( sqlite3ViewGetColumnNames(pParse, pTab) ){
................................................................................
  }

#ifndef SQLITE_OMIT_TRUNCATE_OPTIMIZATION
  /* Special case: A DELETE without a WHERE clause deletes everything.
  ** It is easier just to erase the whole table.  Note, however, that
  ** this means that the row change count will be incorrect.
  */
  if( rcauth==SQLITE_OK && pWhere==0 && !triggers_exist && !IsVirtual(pTab) ){
    assert( !isView );
    sqlite3VdbeAddOp3(v, OP_Clear, pTab->tnum, iDb, memCnt);
    if( !pParse->nested ){
      sqlite3VdbeChangeP4(v, -1, pTab->zName, P4_STATIC);
    }
    for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
      assert( pIdx->pSchema==pTab->pSchema );

Changes to test/auth.test.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
414
415
416
417
418
419
420
421



422
423
424
425
426
427
428
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script is testing the sqlite3_set_authorizer() API
# and related functionality.
#
# $Id: auth.test,v 1.43 2008/07/02 13:13:52 danielk1977 Exp $
#

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

# disable this test if the SQLITE_OMIT_AUTHORIZATION macro is
# defined during compilation.
................................................................................
    }
    return SQLITE_OK
  }
  catchsql {DELETE FROM t2 WHERE a=11}
} {0 {}}
do_test auth-1.50 {
  execsql {SELECT * FROM t2}
} {11 2 33}




do_test auth-1.51 {
  proc auth {code arg1 arg2 arg3 arg4} {
    if {$code=="SQLITE_SELECT"} {
      return SQLITE_DENY
    }
    return SQLITE_OK







|







 







|
>
>
>







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script is testing the sqlite3_set_authorizer() API
# and related functionality.
#
# $Id: auth.test,v 1.44 2008/10/27 15:34:33 danielk1977 Exp $
#

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

# disable this test if the SQLITE_OMIT_AUTHORIZATION macro is
# defined during compilation.
................................................................................
    }
    return SQLITE_OK
  }
  catchsql {DELETE FROM t2 WHERE a=11}
} {0 {}}
do_test auth-1.50 {
  execsql {SELECT * FROM t2}
} {}
do_test auth-1.50.2 {
  execsql {INSERT INTO t2 VALUES(11, 2, 33)}
} {}

do_test auth-1.51 {
  proc auth {code arg1 arg2 arg3 arg4} {
    if {$code=="SQLITE_SELECT"} {
      return SQLITE_DENY
    }
    return SQLITE_OK

Added test/auth3.test.































































































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
# 2008 October 27
#
# 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.
#
#***********************************************************************
#
# Test that the truncate optimization is disabled if the SQLITE_DELETE
# authorization callback returns SQLITE_IGNORE.
#
# $Id: auth3.test,v 1.1 2008/10/27 15:34:33 danielk1977 Exp $
#

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

# disable this test if the SQLITE_OMIT_AUTHORIZATION macro is
# defined during compilation.
if {[catch {db auth {}} msg]} {
  finish_test
  return
}

# Disable the statement cache for these tests.
# 
db cache size 0

db authorizer ::auth
proc auth {code arg1 arg2 arg3 arg4} {
  if {$code=="SQLITE_DELETE"} {
    return $::authcode
  }
  return SQLITE_OK
}

#--------------------------------------------------------------------------
# The following tests - auth3-1.* - test that return values of SQLITE_DENY,
# SQLITE_IGNORE, SQLITE_OK and <invalid> are correctly handled when returned
# by an SQLITE_DELETE authorization callback triggered by a 
# "DELETE FROM <table-name>" statement.
#
do_test auth3-1.1 {
  execsql {
    CREATE TABLE t1(a,b,c);
    INSERT INTO t1 VALUES(1, 2, 3);
    INSERT INTO t1 VALUES(4, 5, 6);
  }
} {}
do_test auth3.1.2 {
  set ::authcode SQLITE_DENY
  catchsql { DELETE FROM t1 }
} {1 {not authorized}}
do_test auth3.1.3 {
  set ::authcode SQLITE_INVALID
  catchsql { DELETE FROM t1 }
} {1 {illegal return value (1) from the authorization function - should be SQLITE_OK, SQLITE_IGNORE, or SQLITE_DENY}}
do_test auth3.1.4 {
  execsql { SELECT * FROM t1 }
} {1 2 3 4 5 6}
do_test auth3-1.5 {
  set ::authcode SQLITE_IGNORE
  execsql { 
    DELETE FROM t1;
    SELECT * FROM t1;
  }
} {}
do_test auth3-1.6 {
  set ::authcode SQLITE_OK
  execsql {
    INSERT INTO t1 VALUES(1, 2, 3);
    INSERT INTO t1 VALUES(4, 5, 6);
    DELETE FROM t1;
    SELECT * FROM t1;
  }
} {}

#--------------------------------------------------------------------------
# These tests - auth3-2.* - test that returning SQLITE_IGNORE really does
# disable the truncate optimization.
#
do_test auth3-2.1 {
  set ::authcode SQLITE_OK
  execsql {
    INSERT INTO t1 VALUES(1, 2, 3);
    INSERT INTO t1 VALUES(4, 5, 6);
  }
  set sqlite_search_count 0
  execsql {
    DELETE FROM t1;
  }
  set sqlite_search_count
} {0}

do_test auth3-2.2 {
  set ::authcode SQLITE_IGNORE
  execsql {
    INSERT INTO t1 VALUES(1, 2, 3);
    INSERT INTO t1 VALUES(4, 5, 6);
  }
  set sqlite_search_count 0
  execsql {
    DELETE FROM t1;
  }
  set sqlite_search_count
} {1}

finish_test