Make explicit the restrictions on UPDATE, DELETE, and INSERT statement syntax
within triggers. Ticket #3947. (CVS 6840)
FossilOrigin-Name: c8bf40df7be728b11bb633516d1988c6064a9d70
diff --git a/manifest b/manifest
index 23bb4d8..073e81e 100644
--- a/manifest
+++ b/manifest
@@ -1,5 +1,5 @@
-C Test\sthe\sresult\sof\spthread_create()\sand\sdo\snot\scall\spthread_join()\sif\sthe\nthread\screation\sfailed.\s\sTicket\s#3933.\s(CVS\s6839)
-D 2009-07-03T12:57:58
+C Make\sexplicit\sthe\srestrictions\son\sUPDATE,\sDELETE,\sand\sINSERT\sstatement\ssyntax\nwithin\striggers.\s\sTicket\s#3947.\s(CVS\s6840)
+D 2009-07-03T15:37:28
F Makefile.arm-wince-mingw32ce-gcc fcd5e9cd67fe88836360bb4f9ef4cb7f8e2fb5a0
F Makefile.in 8b8fb7823264331210cddf103831816c286ba446
F Makefile.linux-gcc d53183f4aa6a9192d249731c90dbdffbd2c68654
@@ -148,7 +148,7 @@
F src/os_win.c 725c38a524d168ce280446ad8761d731bc516405
F src/pager.c 04fdbede529dc9f933637301d789dc7354df6e49
F src/pager.h 5aec418bf99f568b92ae82816a1463400513726d
-F src/parse.y c48ce2025d848bbd1995f811e74dced8ab30abe6
+F src/parse.y bcd46d43fbd23a22b8c020a3eb1806b794794ed5
F src/pcache.c 395f752a13574120bd7513a400ba02a265aaa76d
F src/pcache.h 9b927ccc5a538e31b4c3bc7eec4f976db42a1324
F src/pcache1.c 97e7e8e6e34026fb43b47d08532b0c02e959c26c
@@ -162,7 +162,7 @@
F src/shell.c db2643650b9268df89a4bedca3f1c6d9e786f1bb
F src/sqlite.h.in ccc67f14d5661240d05eadb8ab308aa637b0630c
F src/sqlite3ext.h 1db7d63ab5de4b3e6b83dd03d1a4e64fef6d2a17
-F src/sqliteInt.h 7f6ab3d1c8aaedc64dc046dc413d9bbe187adf00
+F src/sqliteInt.h 8460c998fff4834f875c32233a2d63004163cee9
F src/sqliteLimit.h ffe93f5a0c4e7bd13e70cd7bf84cfb5c3465f45d
F src/status.c 237b193efae0cf6ac3f0817a208de6c6c6ef6d76
F src/table.c cc86ad3d6ad54df7c63a3e807b5783c90411a08d
@@ -663,7 +663,7 @@
F test/trans.test d887cb07630dc39879a322d958ad8b006137485c
F test/trans2.test d5337e61de45e66b1fcbf9db833fa8c82e624b22
F test/trans3.test d728abaa318ca364dc370e06576aa7e5fbed7e97
-F test/trigger1.test 53342dfd582155a599518f1918fdc997e9413177
+F test/trigger1.test 4ecf469e6f46610e0864f52b29d0ea162a2767c1
F test/trigger2.test 53cf2209f68eb5bbb0ea0a07d694007fa702f1ed
F test/trigger3.test 501b8489eb6b9cb5b005f60b071583c01a3c3041
F test/trigger4.test 8e90ee98cba940cd5f96493f82e55083806ab8a0
@@ -739,7 +739,7 @@
F tool/speedtest8.c 2902c46588c40b55661e471d7a86e4dd71a18224
F tool/speedtest8inst1.c 293327bc76823f473684d589a8160bde1f52c14e
F tool/vdbe-compress.tcl 672f81d693a03f80f5ae60bfefacd8a349e76746
-P 45fd5419a7cde29eb6ab5d98141bd642af0d78fb
-R 091f332adcee32735e6b502032b7718f
+P 304c5110ad958b2cc1ddff30e68c8791109128b5
+R 23c00185520dd16e8ecfdeb38c15386d
U drh
-Z 92c87878b82808eaaf54bc37c5295c2b
+Z eccd19ba4bfa8e991ddfacf0b5d84154
diff --git a/manifest.uuid b/manifest.uuid
index 92301b8..310e79a 100644
--- a/manifest.uuid
+++ b/manifest.uuid
@@ -1 +1 @@
-304c5110ad958b2cc1ddff30e68c8791109128b5
\ No newline at end of file
+c8bf40df7be728b11bb633516d1988c6064a9d70
\ No newline at end of file
diff --git a/src/parse.y b/src/parse.y
index 25207cf..04d7aca 100644
--- a/src/parse.y
+++ b/src/parse.y
@@ -14,7 +14,7 @@
** the parser. Lemon will also generate a header file containing
** numeric codes for all of the tokens.
**
-** @(#) $Id: parse.y,v 1.284 2009/07/01 14:56:40 danielk1977 Exp $
+** @(#) $Id: parse.y,v 1.285 2009/07/03 15:37:28 drh Exp $
*/
// All token codes are small integers with #defines that begin with "TK_"
@@ -1179,22 +1179,54 @@
A = X;
}
+// Disallow qualified table names on INSERT, UPDATE, and DELETE statements
+// within a trigger. The table to INSERT, UPDATE, or DELETE is always in
+// the same database as the table that the trigger fires on.
+//
+%type trnm {Token}
+trnm(A) ::= nm(X). {A = X;}
+trnm(A) ::= nm DOT nm(X). {
+ A = X;
+ sqlite3ErrorMsg(pParse,
+ "qualified table names are not allowed on INSERT, UPDATE, and DELETE "
+ "statements within triggers");
+}
+
+// Disallow the INDEX BY and NOT INDEXED clauses on UPDATE and DELETE
+// statements within triggers. We make a specific error message for this
+// since it is an exception to the default grammar rules.
+//
+tridxby ::= .
+tridxby ::= INDEXED BY nm. {
+ sqlite3ErrorMsg(pParse,
+ "the INDEXED BY clause is not allowed on UPDATE or DELETE statements "
+ "within triggers");
+}
+tridxby ::= NOT INDEXED. {
+ sqlite3ErrorMsg(pParse,
+ "the NOT INDEXED clause is not allowed on UPDATE or DELETE statements "
+ "within triggers");
+}
+
+
+
%type trigger_cmd {TriggerStep*}
%destructor trigger_cmd {sqlite3DeleteTriggerStep(pParse->db, $$);}
// UPDATE
-trigger_cmd(A) ::= UPDATE orconf(R) nm(X) SET setlist(Y) where_opt(Z).
- { A = sqlite3TriggerUpdateStep(pParse->db, &X, Y, Z, R); }
+trigger_cmd(A) ::=
+ UPDATE orconf(R) trnm(X) tridxby SET setlist(Y) where_opt(Z).
+ { A = sqlite3TriggerUpdateStep(pParse->db, &X, Y, Z, R); }
// INSERT
-trigger_cmd(A) ::= insert_cmd(R) INTO nm(X) inscollist_opt(F)
- VALUES LP itemlist(Y) RP.
- {A = sqlite3TriggerInsertStep(pParse->db, &X, F, Y, 0, R);}
+trigger_cmd(A) ::=
+ insert_cmd(R) INTO trnm(X) inscollist_opt(F) VALUES LP itemlist(Y) RP.
+ {A = sqlite3TriggerInsertStep(pParse->db, &X, F, Y, 0, R);}
-trigger_cmd(A) ::= insert_cmd(R) INTO nm(X) inscollist_opt(F) select(S).
+trigger_cmd(A) ::= insert_cmd(R) INTO trnm(X) inscollist_opt(F) select(S).
{A = sqlite3TriggerInsertStep(pParse->db, &X, F, 0, S, R);}
// DELETE
-trigger_cmd(A) ::= DELETE FROM nm(X) where_opt(Y).
+trigger_cmd(A) ::= DELETE FROM trnm(X) tridxby where_opt(Y).
{A = sqlite3TriggerDeleteStep(pParse->db, &X, Y);}
// SELECT
diff --git a/src/sqliteInt.h b/src/sqliteInt.h
index 80510da..304697e 100644
--- a/src/sqliteInt.h
+++ b/src/sqliteInt.h
@@ -11,7 +11,7 @@
*************************************************************************
** Internal interface definitions for SQLite.
**
-** @(#) $Id: sqliteInt.h,v 1.890 2009/06/26 15:14:55 drh Exp $
+** @(#) $Id: sqliteInt.h,v 1.891 2009/07/03 15:37:28 drh Exp $
*/
#ifndef _SQLITEINT_H_
#define _SQLITEINT_H_
@@ -2141,17 +2141,14 @@
*
*/
struct TriggerStep {
- int op; /* One of TK_DELETE, TK_UPDATE, TK_INSERT, TK_SELECT */
- int orconf; /* OE_Rollback etc. */
+ u8 op; /* One of TK_DELETE, TK_UPDATE, TK_INSERT, TK_SELECT */
+ u8 orconf; /* OE_Rollback etc. */
Trigger *pTrig; /* The trigger that this step is a part of */
-
- Select *pSelect; /* Valid for SELECT and sometimes
- INSERT steps (when pExprList == 0) */
- Token target; /* Target table for DELETE, UPDATE, INSERT. Quoted */
- Expr *pWhere; /* Valid for DELETE, UPDATE steps */
- ExprList *pExprList; /* Valid for UPDATE statements and sometimes
- INSERT steps (when pSelect == 0) */
- IdList *pIdList; /* Valid for INSERT statements only */
+ Select *pSelect; /* SELECT statment or RHS of INSERT INTO .. SELECT ... */
+ Token target; /* Target table for DELETE, UPDATE, INSERT */
+ Expr *pWhere; /* The WHERE clause for DELETE or UPDATE steps */
+ ExprList *pExprList; /* SET clause for UPDATE. VALUES clause for INSERT */
+ IdList *pIdList; /* Column names for INSERT */
TriggerStep *pNext; /* Next in the link-list */
TriggerStep *pLast; /* Last element in link-list. Valid for 1st elem only */
};
diff --git a/test/trigger1.test b/test/trigger1.test
index 18271e4..b344bee 100644
--- a/test/trigger1.test
+++ b/test/trigger1.test
@@ -639,4 +639,64 @@
catchsql { INSERT INTO tA VALUES('abc', 2, 3) }
} {1 {datatype mismatch}}
+# Ticket #3947: Do not allow qualified table names on INSERT, UPDATE, and
+# DELETE statements within triggers. Actually, this has never been allowed
+# by the grammar. But the error message is confusing: one simply gets a
+# "syntax error". That has now been changed to give a full error message.
+#
+do_test trigger1-16.1 {
+ db eval {
+ CREATE TABLE t16(a,b,c);
+ CREATE INDEX t16a ON t16(a);
+ CREATE INDEX t16b ON t16(b);
+ }
+ catchsql {
+ CREATE TRIGGER main.t16err1 AFTER INSERT ON tA BEGIN
+ INSERT INTO main.t16 VALUES(1,2,3);
+ END;
+ }
+} {1 {qualified table names are not allowed on INSERT, UPDATE, and DELETE statements within triggers}}
+do_test trigger1-16.2 {
+ catchsql {
+ CREATE TRIGGER main.t16err2 AFTER INSERT ON tA BEGIN
+ UPDATE main.t16 SET rowid=rowid+1;
+ END;
+ }
+} {1 {qualified table names are not allowed on INSERT, UPDATE, and DELETE statements within triggers}}
+do_test trigger1-16.3 {
+ catchsql {
+ CREATE TRIGGER main.t16err3 AFTER INSERT ON tA BEGIN
+ DELETE FROM main.t16;
+ END;
+ }
+} {1 {qualified table names are not allowed on INSERT, UPDATE, and DELETE statements within triggers}}
+do_test trigger1-16.4 {
+ catchsql {
+ CREATE TRIGGER main.t16err4 AFTER INSERT ON tA BEGIN
+ UPDATE t16 NOT INDEXED SET rowid=rowid+1;
+ END;
+ }
+} {1 {the NOT INDEXED clause is not allowed on UPDATE or DELETE statements within triggers}}
+do_test trigger1-16.5 {
+ catchsql {
+ CREATE TRIGGER main.t16err5 AFTER INSERT ON tA BEGIN
+ UPDATE t16 INDEXED BY t16a SET rowid=rowid+1 WHERE a=1;
+ END;
+ }
+} {1 {the INDEXED BY clause is not allowed on UPDATE or DELETE statements within triggers}}
+do_test trigger1-16.6 {
+ catchsql {
+ CREATE TRIGGER main.t16err6 AFTER INSERT ON tA BEGIN
+ DELETE FROM t16 NOT INDEXED WHERE a=123;
+ END;
+ }
+} {1 {the NOT INDEXED clause is not allowed on UPDATE or DELETE statements within triggers}}
+do_test trigger1-16.7 {
+ catchsql {
+ CREATE TRIGGER main.t16err7 AFTER INSERT ON tA BEGIN
+ DELETE FROM t16 INDEXED BY t16a WHERE a=123;
+ END;
+ }
+} {1 {the INDEXED BY clause is not allowed on UPDATE or DELETE statements within triggers}}
+
finish_test