Hi guys,

I'm using llblgen 3.1(Product version 3.1.11.907) and LLblGen Pro Framework for SQL Server.

I'm trying to build a query like this:

SELECT CASE WHEN a2.ID is null THEN 'a1' ELSE 'a2' END, count(*)
FROM ... a1 left join ... a2
WHERE ...
GROUP BY CASE WHEN a2.ID is null THEN 'a1' ELSE 'a2' END

I'm setting an expression for field:

   field.SetExpresion(new DbFunctionCall("CASE WHEN {0} is null THEN {1} ELSE {2} END"), new [] {field1, 'a1', 'a2'})

I'm adding the same field(same instance) into the fieldsToFetch collection and into the groupby collection.

When query is executed the SQL statement looks like this:

exec sp_executesql N'
SELECT CASE WHEN [LPA_a2].[Id] IS NOT NULL THEN @p4 ELSE @p2 END AS [groupKey], COUNT(*) AS [count] 
FROM (... [LPA_L1]  LEFT JOIN ... [LPA_a2]  ON  [LPA_L1].[Id]=[LPA_a2].[Id]) 
GROUP BY CASE WHEN [LPA_a2].[Id] IS NOT NULL THEN @p9 ELSE @p10 END', .....

Parameters p4 has the same value as p9, and p2 as p10. This query fails during compilation. Because parameter values are not used during compilation. This happens becase different paremeters in SELECT clause and in the GROUP BY clause are used. If the same parameters are used - it would work.

It seems to be LLBLGen issue as for me, unless I'm missing something? Is there any quick way to fix that? Temporarily I removed parameters and just injected the values into the query - but this is just a temporary solution and I do not want to keep it.

Thanks, Anton

When LLBLGen runtime adds parameters to the query it uses different paramater for each value instance. At that time LLBLGen doesn't know you want to re-utilize them. As a matter of fact, your workaround is the way to achieve this. That is why the constant possibility was added to DBFunctionCalls.

Really?!?!?

It is a best practice and you always have to use parameters to eliminate the ability of SQL injection attacks.

So, does you answer mean that by using LLBLGen I have to forget about best practices and introduce security hole into the system?

I really do not expect to get such answer :-(

kievBug wrote:

Really?!?!? It is a best practice and you always have to use parameters to eliminate the ability of SQL injection attacks.

So, does you answer mean that by using LLBLGen I have to forget about best practices and introduce security hole into the system?

I really do not expect to get such answer :-(

Stay calm, please. Just because there's 1 edge case doesn't mean all other situations are the same.

Re-using parameters isn't always possible, as some databases don't allow re-using of parameters. The problem is that this is generic code, working on all databases. Re-using parameters would crash the query on some other databases. So rock - hard place.

I agree with you that using parameters is the right thing to do, and we always do that, however there are situations where it's not possible. Additionally, re-using parameters requires value comparison, which can be costly if you have large values, e.g. blob/image values.

The situation you have is an edge case where re-using parameters is the only way to fix it, and the workaround isn't ideal. I'll look into another workaround to see if the usage of hard-coded values can be avoided.

I couldn't find a direct workaround, and also couldn't find a way to fix it. The problem is that the DbFunctionCall class doesn't know whether it's being re-used. Even if it does, it goes wrong for databases with anonymous parameters (Sybase ASE, IBM DB2, Access), as the query contains ? characters at the position where a parameter should be, meaning you can't re-use parameters in different parts of the query.

So fixing it won't work. There's a different solution however. Instead of DbFunctionCall, use a derived class of DbFunctionCall:

public class ReusableDbFunctionCall : DbFunctionCall
{
    private string _queryText;

    public ReusableDbFunctionCall(string functionName, object[] parameters) : base(functionName, parameters)
    {
        _queryText = string.Empty;
    }


    public override string ToQueryText(bool inHavingClause)
    {
        if(string.IsNullOrEmpty(_queryText))
        {
            _queryText = base.ToQueryText(inHavingClause);
        }
        else
        {
            // already processed, clear parameters, otherwise they're added again.
            if(this.DatabaseParameters != null)
            {
                this.DatabaseParameters.Clear();
            }
        }
        return _queryText;
    }
}

Test:

[Test]
public void ReUseParametersInExpressionGroupByTest()
{
    var caseExpression = new ReusableDbFunctionCall("CASE WHEN {0} IS NULL THEN {1} ELSE {2} END", new object[] { EmployeeFields.ReportsTo, 0, 1});
    var fields = new ResultsetFields(2);
    fields.DefineField(EmployeeFields.EmployeeId.SetExpression(caseExpression), 0);
    fields.DefineField(new EntityField2("Amount", null, AggregateFunction.CountRow), 1);
    var groupBy = new GroupByCollection(EmployeeFields.EmployeeId.SetExpression(caseExpression));
    var results = new DataTable();
    using(var adapter = new DataAccessAdapter())
    {
        adapter.FetchTypedList(fields, results, null, 0, null, true, groupBy);
    }
    Assert.AreEqual(2, results.Rows.Count);
}

Thank you Otis! This solution works for me, and it is better than injecting parameters right into the query.

As usually LLBLGEN ROCKS!!!

Thank you guys.

Well, it doesn't work again. I've checked it, and it works for group by but when the fields is used in the where clause it doesn't work.

The method ToQueryText() is called 3 times, first time for where clause, second time for resultset fields, and one more time for where clause. During the last call without the changes it changes the query to have different parameter names with different indexes.

So after all, the parameter names are messed up and it doesn't return correct data.

In there where clause I use different instance of the field, not the same as in result set fields.

It doesn't work in the where clause, as the workaround class is only meant for re-using a function call in a group by as the expression has to be the same. You should use a new normal DBFunctionCall to use in the WHERE clause.