DatabaseProcApplicationCreatedLinks
sybsystemprocssp_export_qpgroup  31 Aug 14Defects Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */
3     
4     /*
5     ** Messages for "sp_export_qpgroup"
6     **
7     ** 17230, "You must be the System Administrator (SA) or the Database Owner (dbo) to execute this procedure."
8     ** 17260, "Can't run %1! from within a transaction."
9     ** 17703, "The name supplied is not a user, group, or aliased."
10    ** 17756, "The execution of the stored procedure '%1!' in database
11    **         '%2!' was aborted because there was an error in writing the
12    **         replication log record."
13    ** 18639, "There is no query plans group named '%1!' in this database."
14    */
15    
16    /*
17    ** sp_export_qpgroup exports in an user table (that it creates) 
18    ** the SYSQUERYPLANS section that corresponds to an AP group of 
19    ** a given user. This table has an opaque format (actually the 
20    ** same as SYSQUERYPLANS), and can be used by sp_import_qpgroup, 
21    ** maybe after having been transferred to another database or 
22    ** server. 
23    */
24    
25    create procedure sp_export_qpgroup
26        @usr varchar(255),
27        @group varchar(255),
28        @tab varchar(255)
29    as
30        declare
31            @gid int,
32            @uid int,
33            @s_gid varchar(10),
34            @s_uid varchar(10),
35            @dbname char(30)
36    
37        set nocount on
38    
39        select @dbname = db_name()
40    
41        if user_id() != 1 and charindex("sa_role", show_role()) = 0
42        begin
43            /* 17230, "You must be the System Administrator (SA) or the Database Owner (dbo) to execute this procedure." */
44            raiserror 17230
45            return 2
46        end
47    
48        /*
49        ** AP group handling may not be done within a transaction. 
50        */
51        if @@trancount > 0
52        begin
53            /*
54            ** 17260, "Can't run %1! from within a transaction."
55            */
56            raiserror 17260, "sp_export_qpgroup"
57            return 1
58        end
59    
60        /* Don't start a transaction for the user, he does not expect it. */
61        set chained off
62    
63        /* Don't hold long READ locks, the user might not appreciate it. */
64        set transaction isolation level 1
65    
66    
67        exec sp_aux_get_qpgroup @group, @gid out
68    
69        if @gid is null
70        begin
71            /* 18639, "There is no query plans group named '%1!' in this database." */
72            raiserror 18639, @group
73            return 1
74        end
75    
76        select @uid = user_id(@usr)
77    
78        if @uid is null
79        begin
80            /* 17703, "The name supplied is not a user, group, or aliased." */
81            raiserror 17703
82            return 2
83        end
84    
85        /* prepare the UID and GID as VARCHAR, for EXEC ("...") */
86        select @s_uid = convert(varchar(10), @uid)
87        select @s_gid = convert(varchar(10), @gid)
88    
89        /* create the export table and feed it with the APs */
90        execute (
91        "create table " + @tab + " (
92    			uid		int,
93    			gid		int,
94    			hashkey		int,
95    			id		int,
96    			type		smallint,
97    			sequence	smallint,
98    			status		int null,
99    			text		varchar(255) null, 
100                           dbid            int null, 
101                           qpdate          datetime null, 
102                           sprocid         int null, 
103                           hashkey2        int null, 
104                           key1            int null, 
105                           key2            int null, 
106                           key3            int null, 
107                           key4            int null  
108   		)")
109       /*
110       ** This transaction also writes a log record for replicating the
111       ** invocation of this procedure. If logexec() fails, the transaction
112       ** is aborted.
113       **
114       ** IMPORTANT: The name rs_logexec is significant and is used by
115       ** Replication Server.
116       */
117       begin transaction rs_logexec
118   
119       execute (
120       "insert into " + @tab +
121       " select * 
122   				from sysqueryplans where uid = " + @s_uid +
123       "and gid = " + @s_gid
124       )
125       /*
126       ** Write the log record to replicate this invocation
127       ** of the stored procedure.
128       */
129       if (logexec() != 1)
130       begin
131           /*
132           ** 17756, "The execution of the stored procedure
133           **         '%1!' in database '%2!' was aborted
134           **          because there was an error in writing
135           **          the replication log record."
136           */
137           exec ("drop table " + @tab)
138           raiserror 17756, "sp_export_qpgroup", @dbname
139           rollback transaction rs_logexec
140           return (1)
141       end
142   
143       commit transaction rs_logexec
144       return 0
145   


exec sp_procxmode 'sp_export_qpgroup', 'AnyMode'
go

Grant Execute on sp_export_qpgroup to public
go
DEFECTS
 MPSI 4 Possible SQL Injection @tab 90
 MPSI 4 Possible SQL Injection @tab 119
 MPSI 4 Possible SQL Injection @tab 137
 MTYP 4 Assignment type mismatch @name: varchar(30) = varchar(255) 67
 MDYN 3 Proc uses Dynamic SQL but is not flagged with Dynamic Ownership Chain 25
 MGTP 3 Grant to public sybsystemprocs..sp_export_qpgroup  
 MNER 3 No Error Check should check return value of exec 67
 MUCO 3 Useless Code Useless Brackets 129
 MUCO 3 Useless Code Useless Brackets 140
 QISO 3 Set isolation level 64
 MDYS 2 Dynamic SQL Marker 90
 MDYS 2 Dynamic SQL Marker 119
 MDYS 2 Dynamic SQL Marker 137
 MTR1 2 Metrics: Comments Ratio Comments: 50% 25
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 2 = 6dec - 6exi + 2 25
 MTR3 2 Metrics: Query Complexity Complexity: 38 25

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..sp_aux_get_qpgroup  
   reads table sybsystemprocs..sysattributes  
   calls proc sybsystemprocs..sp_aux_sargs_qpgroup