Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2005
    Posts
    74

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

    I am using ADO.NET in my C# program to perform various operations on databases controlled by my program. So far I've had no problem running statements like this:
    Code:
    SqlCommand DbSqlCmd;
    DbSqlCmd.CommandText = "CREATE DATABASE GenVMain " + "ON PRIMARY (NAME = GenVMain, FILENAME = '" + CGVRegSettings.GetSpecialFolder(CGVRegSettings.ESpecialFolders.Databases) + @"\GenVMain.mdf') LOG ON (NAME = GenVMain_log, FILENAME = '" + CGVRegSettings.GetSpecialFolder(CGVRegSettings.ESpecialFolders.Databases) + @"\GenVMain_log.ldf') FOR ATTACH";
    DbSqlCmd.ExecuteNonQuery();
    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:
    Code:
    DbSqlCmd.Parameters.Add("@dBFolder", SqlDbType.NChar).Value = CGVRegSettings.GetSpecialFolder(CGVRegSettings.ESpecialFolders.Databases);
    DbSqlCmd.CommandText = "CREATE DATABASE GenVMain " + "ON PRIMARY (NAME = GenVMain, FILENAME = '@dBFolder\\GenVMain.mdf') LOG ON (NAME = GenVMain_log, FILENAME = '@dBFolder\\GenVMain_log.ldf') FOR ATTACH";
    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.
    Last edited by KamenG; 06-24-16 at 19:56.

  2. #2
    Join Date
    Dec 2005
    Posts
    74
    Did I post this question in the wrong forum, or is it that the only way to go about this would be quite complex, such as with dynamic SQL?

    Kamen

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd do something like:
    Code:
    DBFolder = CGVRegSettings.GetSpecialFolder(CGVRegSettings.ESpecialFolders.Databases);
    
    DbSqlCmd.CommandText = "CREATE DATABASE GenVMain " 
    +   "ON PRIMARY (NAME = GenVMain, FILENAME = '" + DBFolder
    +   "\\GenVMain.mdf') LOG ON (NAME = GenVMain_log, FILENAME = '"
    +   DBFolder + "\\GenVMain_log.ldf') FOR ATTACH";
    -PatP
    Last edited by Pat Phelan; 06-28-16 at 06:38. Reason: Removed @ from concat
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Dec 2005
    Posts
    74
    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.

    Kamen

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,794
    Provided Answers: 11
    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.

  7. #7
    Join Date
    Dec 2005
    Posts
    74
    Quote Originally Posted by Pat Phelan View Post
    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!

    Kamen

  8. #8
    Join Date
    Dec 2005
    Posts
    74
    Quote Originally Posted by MCrowley View Post
    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.

    Kamen

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •