EX0005 : Check script for data modifying statements - INSERT,UPDATE,DELETE or EXECUTE
Introduction
Section titled “Introduction”The rule list all data modifying statements - INSERT,UPDATE,DELETE or EXECUTE that appear in the script.
Description
Section titled “Description”By identifying these statements, the rule helps prevent accidental or unauthorized modifications to the database. This is particularly useful in environments where scripts are being run automatically or by users without full knowledge of their impact. By flagging or restricting these operations, the rule ensures that changes to critical data are intentional and authorized, improving database security and integrity.
Example of problematic query:
UPDATE EmployeesSET Salary = Salary * 1.10WHERE Department = 'Sales';How to fix
Section titled “How to fix”The rule has a Batch scope and is applied only on the SQL script.
Parameters
Section titled “Parameters”Rule has no parameters.
Remarks
Section titled “Remarks”The rule does not need Analysis Context or SQL Connection.
Effort To Fix
Section titled “Effort To Fix”8 minutes per issue.
Categories
Section titled “Categories”Explicit Rules, Code Smells
Additional Information
Section titled “Additional Information”There is no additional info for this rule.
Example Test SQL
Section titled “Example Test SQL”-- This INSERT statement will cause analysis rule violationINSERT INTO Cities (Location)VALUES ( dbo.CreateNewPoint(x, y) );
-- This INSERT statement will cause analysis rule violationINSERT INTO Cities (Location)VALUES ( CONVERT(Point, '12.3:46.2') );
-- This INSERT statement will cause analysis rule violationINSERT INTO dbo.Table1 VALUES(1, 10.0), (1, 20.0), (1, 0.0);
-- This UPDATE statement will cause analysis rule violationUPDATE dbo.Table2SET dbo.Table2.ColB = dbo.Table2.ColB + dbo.Table1.ColBFROM dbo.Table2 INNER JOIN dbo.Table1 ON (dbo.Table2.ColA = dbo.Table1.ColA);
SELECT ColA, ColBFROM dbo.Table2;
DECLARE abc CURSOR LOCAL FOR SELECT c1, c2 FROM dbo.Table1;OPEN abc;FETCH abc;
-- This UPDATE statement will cause analysis rule violationUPDATE dbo.Table1SET c2 = c2 + d2FROM dbo.Table2WHERE CURRENT OF abc;
-- This DELETE statement will cause analysis rule violationDELETE FROM dbo.Table1WHEREColA > 4556Analysis Results
Section titled “Analysis Results”| Message | Line | Column | |
|---|---|---|---|
| 1 | EX0005 : Script contains INSERT data modifying statement. | 2 | 0 |
| 2 | EX0005 : Script contains INSERT data modifying statement. | 6 | 0 |
| 3 | EX0005 : Script contains INSERT data modifying statement. | 10 | 0 |
| 4 | EX0005 : Script contains UPDATE data modifying statement. | 14 | 0 |
| 5 | EX0005 : Script contains UPDATE data modifying statement. | 31 | 0 |
| 6 | EX0005 : Script contains DELETE data modifying statement. | 37 | 0 |