Skip to content

EX0005 : Check script for data modifying statements - INSERT,UPDATE,DELETE or EXECUTE

The rule list all data modifying statements - INSERT,UPDATE,DELETE or EXECUTE that appear in the script.

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 Employees
SET Salary = Salary * 1.10
WHERE Department = 'Sales';

The rule has a Batch scope and is applied only on the SQL script.

Rule has no parameters.

The rule does not need Analysis Context or SQL Connection.

8 minutes per issue.

Explicit Rules, Code Smells

There is no additional info for this rule.

-- This INSERT statement will cause analysis rule violation
INSERT INTO Cities (Location)
VALUES ( dbo.CreateNewPoint(x, y) );
-- This INSERT statement will cause analysis rule violation
INSERT INTO Cities (Location)
VALUES ( CONVERT(Point, '12.3:46.2') );
-- This INSERT statement will cause analysis rule violation
INSERT INTO dbo.Table1 VALUES(1, 10.0), (1, 20.0), (1, 0.0);
-- This UPDATE statement will cause analysis rule violation
UPDATE dbo.Table2
SET dbo.Table2.ColB = dbo.Table2.ColB + dbo.Table1.ColB
FROM dbo.Table2
INNER JOIN dbo.Table1
ON (dbo.Table2.ColA = dbo.Table1.ColA);
SELECT ColA, ColB
FROM dbo.Table2;
DECLARE abc CURSOR LOCAL FOR
SELECT c1, c2
FROM dbo.Table1;
OPEN abc;
FETCH abc;
-- This UPDATE statement will cause analysis rule violation
UPDATE dbo.Table1
SET c2 = c2 + d2
FROM dbo.Table2
WHERE CURRENT OF abc;
-- This DELETE statement will cause analysis rule violation
DELETE FROM dbo.Table1
WHERE
ColA > 4556
  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

Analysis Rules