Dynamic SQL Query

Question

I'm trying to retrieve information based on Year, Faculty, Quarter, and Course. Depending on how specific the user would like to be (Faculty for the year, quarter, course, etc...).

    string a = "";

    if (DDLYear.SelectedValue == "Select")
    {
       return;
    }
    else
     {
        a = DDLYear.SelectedValue;
     }

 if (DDLFaculty.SelectedValue != "Select")
            {
                arrFields.Add("Employee.Employee_ID = " + DDLFaculty.SelectedValue);
            }
            if (DDLQuarter.SelectedValue != "Select")
            {
                arrFields.Add("Quarter.Quarter_Name = " + DDLQuarter.SelectedValue);
            }
            if (DDLCourse.SelectedValue != "Select")
            {
                arrFields.Add("Course.Title = " + DDLCourse.SelectedValue);
            }

 Custom.SelectCommand = Custom.SelectCommand = 
   "SELECT AVG(Rating.Score) AS YearsAverageScore 
    FROM Rating INNER JOIN Survey ON Rating.Survey_ID = Survey.Survey_ID 
    INNER JOIN Course_Quarter ON Survey.CourseQuarter_ID = Course_Quarter.CourseQuarter_ID 
   INNER JOIN Quarter ON Course_Quarter.Quarter_ID = Quarter.Quarter_ID 
   INNER JOIN Employee ON Course_Quarter.Employee_ID = Employee.Employee_ID 
   WHERE (Quarter.Year = " + a + String.Join("and ", arrFields.ToArray()) + ")";

When I try to retrieve information for Year 2000 on Employee 'A' I run into an error: "Incorrect syntax near 'mployee'". There is no 'mployee' in the code except for that in Employee. When I add a space between " Employee" I run into the same error except it says: Incorrect syntax near 'Employee'"

The Error Report:

Incorrect syntax near 'Employee'. 
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near 'Employee'.

Source Error: 

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.  

Stack Trace: 


[SqlException (0x80131904): Incorrect syntax near 'Employee'.]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +2073502
   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +5064460
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() +234
   System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2275
   System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +33
   System.Data.SqlClient.SqlDataReader.get_MetaData() +86
   System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +311
   System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +987
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32
   System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +141
   System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +12
   System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +10
   System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +144
   System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +319
   System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +92
   System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1618
   System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +21
   System.Web.UI.WebControls.DataBoundControl.PerformSelect() +143
   System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +74
   System.Web.UI.WebControls.GridView.DataBind() +4
   System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +66
   System.Web.UI.WebControls.GridView.OnPreRender(EventArgs e) +26
   System.Web.UI.Control.PreRenderRecursiveInternal() +103
   System.Web.UI.Control.PreRenderRecursiveInternal() +175
   System.Web.UI.Control.PreRenderRecursiveInternal() +175
   System.Web.UI.Control.PreRenderRecursiveInternal() +175
   System.Web.UI.Control.PreRenderRecursiveInternal() +175
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2496

Where is `a` defined? (and this code looks like a recipe for SQL-injection) — Kirk Woll, Sep 19 '11 at 00:12
Can you print out the entire query you're generating that's causing the error and put it here so we can see what the output of your query building is? Also, the way you're appending SQL between user input is potentially dangerous. — mal-wan, Sep 19 '11 at 00:13
@Kirk Woll yes indeed it looks like some massive SQL injection could take place. — Jeremy, Sep 19 '11 at 00:20
Does this leave a security vulnerability in the code behind file? — nick, Sep 19 '11 at 00:25
I found a link which maybe useful for you: http://stackoverflow.com/questions/337704/parameterizing-a-sql-in-clause — Thinhbk, Sep 19 '11 at 00:34

score 1 · Accepted Answer · answered Sep 19 '11 at 00:18

1

You should give us the SQL after the substitution, but your problem is probably among these:

You're getting "Quarter.Year = aand. . ." because of there's a space missing after "a"
The values you're comparing to what I'm guessing are string fields (QuarterName, Course Title) don't have quotation marks around them
String.Join probably does not stick "and" at the very beginning of the string.

answered Sep 19 '11 at 00:18

Larry Lustig

49,320
14
110
160

Thank you, your points were very helpful. The String.Join does add "and" but only after the first array value has been populate. So what I did was change the WHERE clause to: (Quarter.Year = " + a + " and " + String.Join(" and", arrFields.ToArray()) + ")"; – nick Sep 19 '11 at 00:46

score -1 · Answer 2 · answered Sep 19 '11 at 00:13

When you use equality on a string in SQL you need to wrap it around quotes. Usually '. If the column type is numeric (ie long, int etc) then you do not need the quotes.

You can also use the String.Format which will improve readability. An example of this is: String.Format("Quarter.Quarter_Name = '{0}'", DDLQuarter.SelectedValue.ToString());

Here is your original code with string equality fixed:

 if (DDLFaculty.SelectedValue != "Select")
        {
            arrFields.Add("Employee.Employee_ID = '" + DDLFaculty.SelectedValue + "'");
        }
        if (DDLQuarter.SelectedValue != "Select")
        {
            arrFields.Add("Quarter.Quarter_Name = '" + DDLQuarter.SelectedValue + "'");
        }
        if (DDLCourse.SelectedValue != "Select")
        {
            arrFields.Add("Course.Title = '" + DDLCourse.SelectedValue + "'");
        }

Dynamic SQL Query

2 Answers2