Variable in C# SQL query not working

Question

I have a loop in which I grab certain ID's to make a call in a database. There are 2 variables within the query.

The first one works fine but the second one returns nothing. I have tested it a lot and know that the correct value is coming through to the query. Not sure what I am doing wrong here. I replace the variable with a hard coded value that I know is returning and it works fine.

Here is my code:

SqlDataAdapter d8;

d8 = new SqlDataAdapter("SELECT SUM(CAST(AMOUNT AS BIGINT)) AS NEW_AMOUNT 
                         FROM ddb_proc_log_base 
                         WHERE (PROVID = " + docId + 
                       " AND CHART_STATUS = 90 
                         AND YEAR(PLDATE) = 2016 
                         AND CLASS = 2 
                         AND ORD = " + defer + ") OR (ORD = " + defer +
                       " AND PROVID = " + this.getDocHygDS.Tables[0].Rows[t]["HYG_ID"] + 
                       " AND CHART_STATUS = 90 
                         AND YEAR(PLDATE) = 2016 AND CLASS = 2)", conn3);

cmdBuilder5 = new SqlCommandBuilder(d8);
d8.Fill(this.balances);

SQL Injection Alert https://stackoverflow.com/a/50597820/2946329 — Salah Akbari, Jun 02 '18 at 06:59
As already stated your code is vulnerable to Sql Injection, but this is just one problem here. Parameters should always be used when passing values to a database query to avoid parsing errors. In any case, why don't you use the debugger to check what are the value passed to the query when you execute it? — Steve, Jun 02 '18 at 07:02
`this.getDocHygDS.Tables[0].Rows[t]["HYG_ID"]` is object change it to `this.getDocHygDS.Tables[0].Rows[t]["HYG_ID"].ToString()` — Hasan Gholamali, Jun 02 '18 at 07:04

Faraz Babakhel · Answer 1 · 2018-06-02T07:17:57.227

0

@Tyler Nichol You are missing the a single quote where you concatenate string value like

Example ORD = '" + defer + "'

below is an example:

try{
    "select * from SomeTable where name='"+name+","


// in your case this may like the following

    d8 = new SqlDataAdapter("select SUM(CAST(AMOUNT AS BIGINT)) AS NEW_AMOUNT 
        FROM ddb_proc_log_base where ( PROVID = "+docId+" AND CHART_STATUS = 90 
        AND YEAR(PLDATE) = 2016 AND CLASS = 2 AND ORD = '" + defer + "') OR (ORD = '" 
        + defer + "' AND PROVID = " + this.getDocHygDS.Tables[0].Rows[t]["HYG_ID"] 
        + " AND CHART_STATUS = 90 AND YEAR(PLDATE) = 2016 AND CLASS = 2)", conn3);
}
catch(Exception e)
{
//Throw Null Exception Here
}

Recommended Solution

try{
        string UserName="John";
        cmd.CommandText = "select * from SomeTable where name=@Name";
        cmd.Parameters.AddWithValue("@Name", UserName);
}
catch(Exception e)
{
//Throw Null Exception Here
}

edited Jun 02 '18 at 07:17

answered Jun 02 '18 at 07:02

Faraz Babakhel

654
5
14

@Tyler Nichol try this answer. i hope this will help you – Faraz Babakhel Jun 02 '18 at 07:03
6

This encourages a SQL injection pattern. You really shouldn't do that. – Dragonthoughts Jun 02 '18 at 07:08
And if the problem is passing a not correctly quoted string then you will receive an exception that you should see (unless you have empty try/catch block around this code) – Steve Jun 02 '18 at 07:15
@Dragonthoughts Steve Answer updated. Thankyou so much for you supervision – Faraz Babakhel Jun 02 '18 at 07:16

score 0 · Answer 2 · answered Jun 02 '18 at 10:36

You need to use a CONTAIN in the query :

            DataSet getDocHygDS = new DataSet();
            string[] hyg_id = getDocHygDS.Tables[0].AsEnumerable().Select(x => x.Field<string>("HYG_ID")).Distinct().ToArray();
            string or = "'" + string.Join("' OR '", hyg_id) + "'";

            SqlDataAdapter d8;
            string query = string.Format("SELECT SUM(CAST(AMOUNT AS BIGINT)) AS NEW_AMOUNT" +
                              " FROM ddb_proc_log_base" +
                              " WHERE (PROVID = {0}" +
                              " AND CHART_STATUS = 90" +
                              " AND YEAR(PLDATE) = 2016" +
                              " AND CLASS = 2" +
                              " AND ORD = {1})" +
                              " OR" +
                              " (ORD = {1}" +
                              " AND CONTAINS(PROVID, {2})" +
                              " AND CHART_STATUS = 90" +
                              " AND YEAR(PLDATE) = 2016 AND CLASS = 2)", docId, defer, or);

            d8 = new SqlDataAdapter(query, conn3);
            cmdBuilder5 = new SqlCommandBuilder(d8);
            d8.Fill(this.balances);

Variable in C# SQL query not working

2 Answers2