0

This is the implementation in code: the schema name comes from a configuration file and is different for each environment. Sonar throws SQL injection alert at this statement:

select * 
from dbName.dbo.stu_name;     <<dbname is the variable that comes from property file>>

I tried these solutions:

  1. Using query.setParameter(?,dbname). Tried passing dbname dynamically and vulnerability was fixed. When debugging, the query was throwing an error and did not execute.

  2. Used String.format(select * from \'%s\',tablename.replace("\'","\'\'")) - vulnerability fixed, but query failed to execute

I tried various other solutions and nothing worked.

Can anyone please help me with this?

10
  • 1
    You should post some code and specify which technology you're using. And use a print to see the actual query you're getting Commented Feb 20, 2024 at 12:51
  • You cannot parametrize database object names - like tables, columns etc. - only values. If you really must do this - again, really?!?! - then you must resort to using dynamic SQL (which has its own share of tricky drawbacks and issues) Commented Feb 20, 2024 at 12:56
  • You can limit the avenue of attack by validating the database name, and then using QUOTENAME to properly quote it. Commented Feb 20, 2024 at 13:04
  • Wait, is the table and database name dynamic..? Commented Feb 20, 2024 at 13:13
  • @ThomA : Database name is dynamic and table name is the same for all envs. Commented Feb 20, 2024 at 13:15

2 Answers 2

1

The "best" defence you can do is not use dynamic SQL. Considering that this is the database that's dynamic, and based on a prior question, I would suggest that this is occurring from an application, so that means you don't need 3-part naming. Instead you parametrise the database name in the connection string and use 2-part naming; no dynamic SQL needed.

If you must use dynamic SQL, then the "best™️" defence would be to validate the database name, and then ensure you properly quote the database name when you inject it into your dynamic statement.

Here I validate the database name in sys.databases and then use QUOTENAME to quote the name appropriately. If the database doesn't exist, no query is run (and no error is returned):

DECLARE @DatabaseName sysname = N'YourDatabaseName';

DECLARE @SQL nvarchar(MAX);
SELECT @SQL = N'SELECT * FROM ' + QUOTENAME(d.name) + N'.dbo.stu_name;'
FROM sys.databases d
WHERE d.name = @DatabaseName;

EXEC sys.sp_executesql @SQL;
Sign up to request clarification or add additional context in comments.

Comments

1

In addition to Thom's advice about validating the database name, to avoid injecting the database name in the query, you can just execute the dynamic SQL in the context of that database. If somehow @DatabaseName had some garbage in it from a user, it would just not work.

SET @sql = N'SELECT * FROM dbo.stu_name;';

DECLARE @exec nvarchar(1024),
  @db sysname = QUOTENAME(DB_NAME(DB_ID(@DatabaseName)));          

IF @db IS NOT NULL
BEGIN
  SET @exec = @db + N'.sys.sp_executesql';

  EXEC @exec @sql;
END

I wrap it in DB_NAME(DB_ID( to avoid complications with names passed in already quoted with [] or "".

Another benefit of this approach - aside from not putting potentially dangerous user input directly into a query you execute - is that functions like OBJECT_ID() and DB_NAME() work, because they run in that database's context.

1 Comment

I like this solution, as there's no actual injection; it's a method I too often forget about. Plus, even if you didn't bother validating the database name, it won't cause problems. DECLARE @Procedure nvarchar(514) = N'[SELECT * FROM sys.databases]' + N'.sys.sp_executesql'; EXEC @Procedure N'SELECT * FROM sys.tables'; will just generate the error "Database 'SELECT * FROM sys.databases' does not exist. Make sure that the name is entered correctly."

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.