/ Check-in [5707b3d5]
Login

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

Overview
Comment:The initial round of tests for the sqlite_set_authorizer() API. More are needed before release. Ticket #215. (CVS 829)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 5707b3d56efb6e988f816abefb1836f2f3254117
User & Date: drh 2003-01-12 19:33:53
Context
2003-01-13
23:27
Revise the sqlite_set_authorizer API to provide more detailed information about the SQL statement being authorized. Only partially tested so far. (CVS 830) check-in: 45de93f9 user: drh tags: trunk
2003-01-12
19:33
The initial round of tests for the sqlite_set_authorizer() API. More are needed before release. Ticket #215. (CVS 829) check-in: 5707b3d5 user: drh tags: trunk
18:07
Forgot to include the new "auth.c" source file in the previous checkin. (CVS 828) check-in: 76019164 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/auth.c.

10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
..
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
...
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
...
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
**
*************************************************************************
** This file contains code used to implement the sqlite_set_authorizer()
** API.  This facility is an optional feature of the library.  Embedded
** systems that do not need this facility may omit it by recompiling
** the library with -DSQLITE_OMIT_AUTHORIZATION=1
**
** $Id: auth.c,v 1.1 2003/01/12 18:07:49 drh Exp $
*/
#include "sqliteInt.h"

/*
** All of the code in this file may be omitted by defining a single
** macro.
*/
................................................................................
** Write an error message into pParse->zErrMsg that explains that the
** user-supplied authorization function returned an illegal value.
*/
static void sqliteAuthBadReturnCode(Parse *pParse, int rc){
  char zBuf[20];
  sprintf(zBuf, "(%d)", rc);
  sqliteSetString(&pParse->zErrMsg, "illegal return value ", zBuf,
    " from the authorization function - should to be SQLITE_OK, "
    "SQLITE_IGNORE, or SQLITE_DENY", 0);
  pParse->nErr++;
}

/*
** The pExpr should be a TK_COLUMN expression.  The table referred to
** is in pTabList with an offset of base.  Check to see if it is OK to read
................................................................................
  sqlite *db = pParse->db;
  int rc;
  if( db->xAuth==0 ){
    return SQLITE_OK;
  }
  rc = db->xAuth(db->pAuthArg, SQLITE_DELETE_ROW, zName, "");
  if( rc==SQLITE_DENY  || (rc==SQLITE_IGNORE && forceError) ){
    sqliteSetString(&pParse->zErrMsg,"deletion of rows from table ",
        zName, " is prohibited", 0);
    pParse->nErr++;
  }else if( rc!=SQLITE_OK && rc!=SQLITE_IGNORE ){
    rc = SQLITE_DENY;
    sqliteAuthBadReturnCode(pParse, rc);
  }
  return rc;
................................................................................
  sqlite *db = pParse->db;
  int rc;
  if( db->xAuth==0 ){
    return SQLITE_OK;
  }
  rc = db->xAuth(db->pAuthArg, SQLITE_INSERT_ROW, zName, "");
  if( rc==SQLITE_DENY || (rc==SQLITE_IGNORE && forceError) ){
    sqliteSetString(&pParse->zErrMsg,"insertion of rows from table ",
        zName, " is prohibited", 0);
    pParse->nErr++;
  }else if( rc!=SQLITE_OK && rc!=SQLITE_IGNORE ){
    rc = SQLITE_DENY;
    sqliteAuthBadReturnCode(pParse, rc);
  }
  return rc;







|







 







|







 







|







 







|







10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
..
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
...
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
...
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
**
*************************************************************************
** This file contains code used to implement the sqlite_set_authorizer()
** API.  This facility is an optional feature of the library.  Embedded
** systems that do not need this facility may omit it by recompiling
** the library with -DSQLITE_OMIT_AUTHORIZATION=1
**
** $Id: auth.c,v 1.2 2003/01/12 19:33:53 drh Exp $
*/
#include "sqliteInt.h"

/*
** All of the code in this file may be omitted by defining a single
** macro.
*/
................................................................................
** Write an error message into pParse->zErrMsg that explains that the
** user-supplied authorization function returned an illegal value.
*/
static void sqliteAuthBadReturnCode(Parse *pParse, int rc){
  char zBuf[20];
  sprintf(zBuf, "(%d)", rc);
  sqliteSetString(&pParse->zErrMsg, "illegal return value ", zBuf,
    " from the authorization function - should be SQLITE_OK, "
    "SQLITE_IGNORE, or SQLITE_DENY", 0);
  pParse->nErr++;
}

/*
** The pExpr should be a TK_COLUMN expression.  The table referred to
** is in pTabList with an offset of base.  Check to see if it is OK to read
................................................................................
  sqlite *db = pParse->db;
  int rc;
  if( db->xAuth==0 ){
    return SQLITE_OK;
  }
  rc = db->xAuth(db->pAuthArg, SQLITE_DELETE_ROW, zName, "");
  if( rc==SQLITE_DENY  || (rc==SQLITE_IGNORE && forceError) ){
    sqliteSetString(&pParse->zErrMsg,"deletion from table ",
        zName, " is prohibited", 0);
    pParse->nErr++;
  }else if( rc!=SQLITE_OK && rc!=SQLITE_IGNORE ){
    rc = SQLITE_DENY;
    sqliteAuthBadReturnCode(pParse, rc);
  }
  return rc;
................................................................................
  sqlite *db = pParse->db;
  int rc;
  if( db->xAuth==0 ){
    return SQLITE_OK;
  }
  rc = db->xAuth(db->pAuthArg, SQLITE_INSERT_ROW, zName, "");
  if( rc==SQLITE_DENY || (rc==SQLITE_IGNORE && forceError) ){
    sqliteSetString(&pParse->zErrMsg,"insertion into table ",
        zName, " is prohibited", 0);
    pParse->nErr++;
  }else if( rc!=SQLITE_OK && rc!=SQLITE_IGNORE ){
    rc = SQLITE_DENY;
    sqliteAuthBadReturnCode(pParse, rc);
  }
  return rc;

Changes to src/insert.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
122
123
124
125
126
127
128



129
130
131
132
133
134
135
**    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
** to handle INSERT statements in SQLite.
**
** $Id: insert.c,v 1.69 2003/01/12 18:02:18 drh Exp $
*/
#include "sqliteInt.h"

/*
** This routine is call to handle SQL of the following forms:
**
**    insert into TABLE (IDLIST) values(EXPRLIST)
................................................................................
  zTab = sqliteTableNameFromToken(pTableName);
  if( zTab==0 ) goto insert_cleanup;
  pTab = sqliteFindTable(pParse->db, zTab);
  if( pTab==0 ){
    sqliteSetString(&pParse->zErrMsg, "no such table: ", zTab, 0);
    pParse->nErr++;
    goto insert_cleanup;



  }

  /* Ensure that:
  *  (a) the table is not read-only, 
  *  (b) that if it is a view then ON INSERT triggers exist
  */
  row_triggers_exist = 







|







 







>
>
>







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
**    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
** to handle INSERT statements in SQLite.
**
** $Id: insert.c,v 1.70 2003/01/12 19:33:53 drh Exp $
*/
#include "sqliteInt.h"

/*
** This routine is call to handle SQL of the following forms:
**
**    insert into TABLE (IDLIST) values(EXPRLIST)
................................................................................
  zTab = sqliteTableNameFromToken(pTableName);
  if( zTab==0 ) goto insert_cleanup;
  pTab = sqliteFindTable(pParse->db, zTab);
  if( pTab==0 ){
    sqliteSetString(&pParse->zErrMsg, "no such table: ", zTab, 0);
    pParse->nErr++;
    goto insert_cleanup;
  }
  if( sqliteAuthInsert(pParse, zTab, 0) ){
    goto insert_cleanup;
  }

  /* Ensure that:
  *  (a) the table is not read-only, 
  *  (b) that if it is a view then ON INSERT triggers exist
  */
  row_triggers_exist = 

Added test/auth.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
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
# 2003 January 12
#
# 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.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script testing the sqlite_set_authorizer() API.
#
# $Id: auth.test,v 1.1 2003/01/12 19:33:54 drh Exp $
#

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

if {[info command sqlite_set_authorizer]!=""} {

do_test auth-1.1 {
  db close
  set ::DB [sqlite db test.db]
  proc auth {code arg1 arg2} {
    if {$code=="SQLITE_INSERT_ROW" 
          && [string compare -nocase $arg1 sqlite_master]==0} {
      return SQLITE_DENY
    }
    return SQLITE_OK
  }
  sqlite_set_authorizer $::DB ::auth
  catchsql {CREATE TABLE t1(a,b,c)}
} {1 {insertion into table sqlite_master is prohibited}}
do_test auth-1.2 {
  proc auth {code arg1 arg2} {
    if {$code=="SQLITE_INSERT_ROW" 
          && [string compare -nocase $arg1 sqlite_master]==0} {
      return SQLITE_IGNORE
    }
    return SQLITE_OK
  }
  catchsql {CREATE TABLE t1(a,b,c)}
} {1 {insertion into table sqlite_master is prohibited}}
do_test auth-1.3 {
  proc auth {code arg1 arg2} {
    if {$code=="SQLITE_INSERT_ROW" 
          && [string compare -nocase $arg1 sqlite_master]==0} {
      return SQLITE_OK
    }
    return SQLITE_OK
  }
  catchsql {CREATE TABLE t1(a,b,c)}
} {0 {}}
do_test auth-1.4 {
  execsql {SELECT name FROM sqlite_master}
} {t1}
do_test auth-1.5 {
  proc auth {code arg1 arg2} {
    if {$code=="SQLITE_INSERT_ROW" 
          && [string compare -nocase $arg1 sqlite_master]==0} {
      return BOGUS
    }
    return SQLITE_OK
  }
  catchsql {CREATE TABLE t2(a,b,c)}
} {1 {illegal return value (1) from the authorization function - should be SQLITE_OK, SQLITE_IGNORE, or SQLITE_DENY}}
do_test auth-1.6 {
  proc auth {code arg1 arg2} {
    if {$code=="SQLITE_DELETE_ROW" 
          && [string compare -nocase $arg1 sqlite_master]==0} {
      return SQLITE_DENY
    }
    return SQLITE_OK
  }
  catchsql {DROP TABLE t1}
} {1 {deletion from table sqlite_master is prohibited}}
do_test auth-1.7 {
  proc auth {code arg1 arg2} {
    if {$code=="SQLITE_DELETE_ROW" 
          && [string compare -nocase $arg1 sqlite_master]==0} {
      return SQLITE_IGNORE
    }
    return SQLITE_OK
  }
  catchsql {DROP TABLE t1}
} {1 {deletion from table sqlite_master is prohibited}}
do_test auth-1.8 {
  proc auth {code arg1 arg2} {
    if {$code=="SQLITE_INSERT_ROW" 
          && [string compare -nocase $arg1 t1]==0} {
      return SQLITE_DENY
    }
    return SQLITE_OK
  }
  catchsql {INSERT INTO t1 VALUES(1,2,3)}
} {1 {insertion into table t1 is prohibited}}
do_test auth-1.9 {
  proc auth {code arg1 arg2} {
    if {$code=="SQLITE_INSERT_ROW" 
          && [string compare -nocase $arg1 t1]==0} {
      return SQLITE_IGNORE
    }
    return SQLITE_OK
  }
  catchsql {INSERT INTO t1 VALUES(1,2,3)}
} {0 {}}
do_test auth-1.10 {
  execsql {SELECT * FROM t1}
} {}
do_test auth-1.11 {
  proc auth {code arg1 arg2} {
    if {$code=="SQLITE_INSERT_ROW" 
          && [string compare -nocase $arg1 t1]==0} {
      return SQLITE_OK
    }
    return SQLITE_OK
  }
  catchsql {INSERT INTO t1 VALUES(1,2,3)}
} {0 {}}
do_test auth-1.12 {
  execsql {SELECT * FROM t1}
} {1 2 3}
do_test auth-1.13 {
  proc auth {code arg1 arg2} {
    if {$code=="SQLITE_DELETE_ROW" 
          && [string compare -nocase $arg1 t1]==0} {
      return SQLITE_DENY
    }
    return SQLITE_OK
  }
  catchsql {DELETE FROM t1 WHERE a=1}
} {1 {deletion from table t1 is prohibited}}
do_test auth-1.14 {
  execsql {SELECT * FROM t1}
} {1 2 3}
do_test auth-1.15 {
  proc auth {code arg1 arg2} {
    if {$code=="SQLITE_DELETE_ROW" 
          && [string compare -nocase $arg1 t1]==0} {
      return SQLITE_IGNORE
    }
    return SQLITE_OK
  }
  catchsql {DELETE FROM t1 WHERE a=1}
} {0 {}}
do_test auth-1.16 {
  execsql {SELECT * FROM t1}
} {1 2 3}
do_test auth-1.17 {
  proc auth {code arg1 arg2} {
    if {$code=="SQLITE_READ_COLUMN" 
          && [string compare -nocase $arg1 t1]==0
          && [string compare -nocase $arg2 a]==0} {
      return SQLITE_DENY
    }
    return SQLITE_OK
  }
  catchsql {SELECT * FROM t1}
} {1 {access to t1.a is prohibited}}
do_test auth-1.18 {
  proc auth {code arg1 arg2} {
    if {$code=="SQLITE_READ_COLUMN" 
          && [string compare -nocase $arg1 t1]==0
          && [string compare -nocase $arg2 a]==0} {
      return SQLITE_IGNORE
    }
    return SQLITE_OK
  }
  catchsql {SELECT * FROM t1}
} {0 {{} 2 3}}
do_test auth-1.19 {
  proc auth {code arg1 arg2} {
    if {$code=="SQLITE_WRITE_COLUMN" 
          && [string compare -nocase $arg1 t1]==0
          && [string compare -nocase $arg2 a]==0} {
      return SQLITE_DENY
    }
    return SQLITE_OK
  }
  catchsql {UPDATE t1 SET a=11 WHERE a=1}
} {1 {changes to t1.a are prohibited}}
do_test auth-1.20 {
  execsql {SELECT * FROM t1}
} {1 2 3}
do_test auth-1.21 {
  proc auth {code arg1 arg2} {
    if {$code=="SQLITE_WRITE_COLUMN" 
          && [string compare -nocase $arg1 t1]==0
          && [string compare -nocase $arg2 a]==0} {
      return SQLITE_DENY
    }
    return SQLITE_OK
  }
  catchsql {UPDATE t1 SET b=12 WHERE a=1}
} {0 {}}
do_test auth-1.22 {
  execsql {SELECT * FROM t1}
} {1 12 3}
do_test auth-1.23 {
  proc auth {code arg1 arg2} {
    if {$code=="SQLITE_WRITE_COLUMN" 
          && [string compare -nocase $arg1 t1]==0
          && [string compare -nocase $arg2 a]==0} {
      return SQLITE_IGNORE
    }
    return SQLITE_OK
  }
  catchsql {UPDATE t1 SET a=11, b=22 WHERE a=1}
} {0 {}}
do_test auth-1.24 {
  execsql {SELECT * FROM t1}
} {1 22 3}
do_test auth-1.25 {
  proc auth {code arg1 arg2} {
    if {$code=="SQLITE_WRITE_COLUMN" 
          && [string compare -nocase $arg1 t1]==0
          && [string compare -nocase $arg2 a]==0} {
      return SQLITE_DENY
    }
    return SQLITE_OK
  }
  catchsql {UPDATE t1 SET a=11, b=33 WHERE a=1}
} {1 {changes to t1.a are prohibited}}
do_test auth-1.26 {
  execsql {SELECT * FROM t1}
} {1 22 3}
do_test auth-1.27 {
  proc auth {code arg1 arg2} {
    if {$code=="SQLITE_READ_COLUMN" 
          && [string compare -nocase $arg1 t1]==0
          && [string compare -nocase $arg2 a]==0} {
      return SQLITE_DENY
    }
    return SQLITE_OK
  }
  catchsql {UPDATE t1 SET b=33, c=44 WHERE a=1}
} {1 {access to t1.a is prohibited}}
do_test auth-1.28 {
  execsql {SELECT b, c FROM t1}
} {22 3}
do_test auth-1.29 {
  proc auth {code arg1 arg2} {
    if {$code=="SQLITE_READ_COLUMN" 
          && [string compare -nocase $arg1 t1]==0
          && [string compare -nocase $arg2 a]==0} {
      return SQLITE_IGNORE
    }
    return SQLITE_OK
  }
  catchsql {UPDATE t1 SET b=33, c=44 WHERE a=1}
} {0 {}}
do_test auth-1.30 {
  execsql {SELECT b, c FROM t1}
} {22 3}
do_test auth-1.31 {
  proc auth {code arg1 arg2} {
    if {$code=="SQLITE_READ_COLUMN" 
          && [string compare -nocase $arg1 t1]==0
          && [string compare -nocase $arg2 a]==0} {
      return SQLITE_IGNORE
    }
    return SQLITE_OK
  }
  catchsql {UPDATE t1 SET b=33, c=44 WHERE a IS NULL}
} {0 {}}
do_test auth-1.32 {
  execsql {SELECT b, c FROM t1}
} {33 44}

  
} ;# End of the "if( db command exists )"

finish_test