plsql:DeleteOrUpdateWithoutWhereCheck triggered on ALTER TABLE

SonarCloud
,
1

This Blocker is triggered for auto-generated migration SQL code (Entity Framework Core). The description of the rule mentions UPDATE and DELETE statements should contain WHERE clause, but the code does not actually contain any UPDATE or DELETE statements. However, the keyword DELETE is used in the ALTER TABLE statement (that is the line where the rule is triggered).

I believe this to be a false positive.

  • versions used: sonarcloud Version 1.0
  • sql script follows (table/column/migration names were changed, they should not be important anyway)
IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'xxx')
BEGIN
    CREATE INDEX [IX_TD_DId] ON [a] ([DId]);
END;

GO

IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'xxx')
BEGIN
    ALTER TABLE [TD] ADD CONSTRAINT [FK_TD_D_DId] FOREIGN KEY ([DId]) REFERENCES [D] ([Id]) ON DELETE CASCADE;
END;

GO

IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'xxx')
BEGIN
    INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
    VALUES (N'xxx', N'2.2.0-rtm-35687');
END;

GO
2

Hi,

On SonarCloud, files with a “.sql” suffix are analyzed by the PL/SQL analyzer by default.
However, your file really looks like a TSQL file:

  • It has GO commands
  • It uses identifiers delimited by square brackets

As documented, I suggest to configure your project so that .sql files are analyzed by the TSQL analyzer:

sonar.tsql.file.suffixes=sql,tsql
sonar.plsql.file.suffixes=pks,pkb

The TSQL analyzer should give much better results on your file.

3

Thanks, I will try that and report back.

4

The configuration did help, the rule is not triggered anymore. Thank you.