SQL DDL code to avoid SQL Injection Attacks

Question

The following piece of code is being highlighted as a security vulnerability to SQL injection attacks.

StringBuilder sb = new StringBuilder();
sb.Append("DROP DATABASE IF EXISTS " + dbname);

String **sqlCommText** = sb.ToString();

using (SqlCommand command = new SqlCommand(**sqlCommText**, connection))
{

   connection.Open();

Namely the sqlCommText

I'm aware of creating prepared statements on DML sql like insert and updates but i dont think this works on DDL sql - i cant parameterize the dbname into the sql.

Any suggestions how this should be fixed?

Answer 1

Preventing SQL Injection here is pretty easy: query the names of all of the databases on the SQL Server like this:

SELECT name, QUOTENAME(name) as QName 
FROM sys.databases 
WHERE database_id > 4;

Then, before you execute the rest of your SQL code, just check that your dbname variable is in that list of names returned by the query and if it is, then use the corresponding QName in your query (this protects against odd characters in the database name and also against something called latent injection).

However, as I mentioned in the comments, stopping injection is the easy problem. The hard problem here is to make sure that that legitimate users do not accidentally drop the wrong database, and worse, that bad actors do not intentionally drop the wrong database(s).