ADO.NET: using a parameter in a SQL query containing a file path (attaching a dB)

If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Pardon the use of my convoluted actual method and variable names, I'm afraid I'll mess something up if I started substituting "foo"s and "bar"s. Anyway, when running the code analysis in VS, I get this CA2100 warning about SQL injection vulnerabilities, and although my software will never be subject to any attacks (it's embedded and its distribution is extremely low-volume), I'd still like to make an effort to make the requisite corrections. I also do SQL once a year, at best (as evidenced by the scarcity of my posts here), so I'm very rusty and I'm not really looking at learning the best techniques for producing the most proper SQL code (like dynamic SQL). If I can't figure something simple and elegant, I'd rather keep it as it is, lest it causes aggravation when I revisit the code for modifications. So, my first attempt was to add the string arguments as SQL command parameters, but it seems like parameters don't work inside single quotes or anywhere in CREATE DATABASE statements. After unsuccessfully trying a few permutations of:

I decided to ask the experts, hoping you'll indulge me with your wisdom (in a lenient manner). Thank you in advance!

Kamen

P.S. I use whatever version of SQL Express is the latest, currently 2014 but will upgrade to 2016 shortly.
K.
P.P.S. The errors I get vary slightly and are completely generic, such as "A file activation error occurred. The physical file name '@dBFolder\GenVMain.mdf' may be incorrect. Diagnose and correct additional errors, and retry the operation."
K.

Thanks, Pat. However, that made no difference. It wasn't exactly one of the permutations I had tried, but it doesn't seem to resolve what Microsoft feels is a security hazard:

Code:

CA2100 Review SQL queries for security vulnerabilities The query string passed to 'SqlCommand.CommandText.set(string)' in 'CDatabaseServices.AttachDatabaseMain(out string, string)' could contain the following variables 'DBFolder'. If any of these variables could come from user input, consider using a stored procedure or a parameterized SQL query instead of building the query with string concatenations.

It seems like anything that allows the SQL command text to show actual information (e.g., "MyPa$sw0rd") instead of a parameter name (e.g., "@UserPassword") will bring up this warning. There might be no way around it and I can live with that; but if there were, I'd like to know.

I have only seen this sort of error from an ASP.NET application. Given the nature of any web application, ASP.NET makes you go through an extra hoop or two to use variables for the CommandText in a command object. I am sure there is a way around it, but you may need to go to StackExchange to find it.

I can't provoke that message to appear with similar (but not exactly the same) code. Even a fresh install on a virgin VM doesn't generate an error or warning. Now you have me puzzled.

-PatP

Pat, I am so sorry you had to go through the effort of creating a whole setup to try and reproduce this! Maybe I should have been more specific: this is not a language-compilation / platform-build warning, it is produced by the Visual Studio built-in Code Analysis tool. It is useful for finding various problems that are outside the standard errors/warnings associated with C# and .NET, and go towards the actual design of the assemblies that might deviate from Microsoft's guidelines and recommendations. Some of its suggestions are actually quite useful (e.g., proper disposal of native resources), but some, like this one, might be quite the overreaction. The reason I deicide to go after it is because I had like a hundred SQL vulnerability warnings in one project and the other ones were being drowned within. For now I have decided to suppress them. If it ever becomes an issue, I might revisit. Thanks for your efforts!

I have only seen this sort of error from an ASP.NET application. Given the nature of any web application, ASP.NET makes you go through an extra hoop or two to use variables for the CommandText in a command object. I am sure there is a way around it, but you may need to go to StackExchange to find it.

Thanks, M! I actually found some relevant info about this issue on StackOverflow and MSDN forums before I posted here. Although it wasn't the exact same issues posted, and none had a good answer that I could use. The only plausible solutions referred to use of dynamic SQL but not under an ADO.NET scenario, where the proposed syntax cannot be used; I fear that might get rather complicated in that case. I don't want to push this much further - I don't personally have a definite need for a solution; it would be the only solution available on the Internet, apparently, if that is incentive enough.