How to fix SQL injection if we have to use DB name dynamically in SQL Server?

Question

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?

Answer 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;

Answer 2

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.