search

nhibernate / nhibernate-core

NHibernate Object Relational Mapper
https://nhibernate.info
GNU Lesser General Public License v2.1
2.12k stars 924 forks source link

NH-2754 - Exception "Invalid filter-parameter name format\r\nParameter name: filterParameterName" when using colons in quoted strings in sql-query T-SQL definitions #1010

Open nhibernate-bot opened 6 years ago

nhibernate-bot commented 6 years ago

Sean McElroy created an issue — :

I found a problem related to http://opensource.atlassian.com/projects/hibernate/browse/HHH-5932 in NH 3.0.0 GA -- when a sql-query exists that has a colon in the text (not just the HQL as the title of the referenced HHH issue has), and if you have a session filter enabled (which I do for multi-lingual support) the NHibernate loader appears to barf on it. In the query below, replacing statements with colons in quoted strings, i.e. COALESCE('ACH:'...) with COALESCE('ACH') fixes the problem.

It appears additional supports needs to be added to support colons in quoted strings in sql-query statements. The workaround I proposed above (remove colon from quoted string) works for my specific case, but I believe this is an issue that needs to be addressed for others who find themselves in the same scenario.

I can provide further source/information to reproduce the issue if needed. I am using SQL Server 2008 R2 as my data source.

***** EXCEPTION THROWN: could not execute query [ SELECT COALESCE('ACH:' pach.ReceivingRoutingNumber '' pach.ReceivingAccountNumber, COALESCE(pbp.PayeeKey, COALESCE('Wire:' pw.CreditRoutingNumber '' pw.CreditAccountNumber, NULL))) AS ID, tcc.ID AS TransactionCategoryClassificationID,
COUNT(pp.ID) AS PendingPaymentCount FROM core.ScheduledPaymentBase sp LEFT OUTER JOIN core.ScheduledPaymentACH spa ON (sp.ID = spa.ID) LEFT OUTER JOIN core.PaymentTemplateACH pta ON (pta.ID = spa.PaymentTemplateACHID) LEFT OUTER JOIN core.ScheduledPaymentBillPay spbp ON (sp.ID = spbp.ID) LEFT OUTER JOIN core.PaymentTemplateBillPay ptbp ON (ptbp.ID = spbp.PaymentTemplateBillPayID) LEFT OUTER JOIN core.ScheduledPaymentWire spw ON (sp.ID = spw.ID) LEFT OUTER JOIN core.PaymentTemplateWire ptw ON (ptw.ID = spw.PaymentTemplateWireID) LEFT OUTER JOIN core.PaymentTemplateItemBase ptib ON (ptib.PaymentTemplateBaseID IN (pta.ID, ptbp.ID, ptw.ID)) LEFT OUTER JOIN core.PaymentTemplateItemACH ptia ON (ptia.ID = ptib.ID) LEFT OUTER JOIN core.PaymentTemplateItemBillPay ptibp ON (ptibp.ID = ptib.ID) LEFT OUTER JOIN core.PaymentTemplateItemWire ptiw ON (ptiw.ID = ptib.ID) LEFT OUTER JOIN core.PayeeACHAccount pach ON (spa.PayeeACHAccountID = pach.ID OR ptia.PayeeACHAccountID = pach.ID) LEFT OUTER JOIN core.PayeeBillPayAccount pbp ON (spbp.PayeeBillPayAccountID = pbp.ID OR ptibp.PayeeBillPayAccountID = pbp.ID) LEFT OUTER JOIN core.PayeeWireAccount pw ON (spw.PayeeWireAccountID = pw.ID OR ptiw.PayeeWireAccountID = pw.ID) INNER JOIN core.Payee p ON ( p.ID IN ( SELECT ptib.PayeeID FROM core.PaymentTemplateItemBase ptib WHERE ptib.PaymentTemplateBaseID IN (pta.ID, ptbp.ID, ptw.ID) ) ) INNER JOIN core.TransactionCategoryClassification tcc ON tcc.ID = ( SELECT tc.TransactionCategoryClassificationID FROM core.TransactionCategory tc WHERE tc.ID = p.DefaultExpenseTransactionCategoryID ) INNER JOIN core.PendingPaymentBase pp ON pp.ScheduledPaymentID = sp.ID AND = 1 WHERE sp.SourceAccountID IN (SELECT ua.AccountID FROM core.UserAccount ua WHERE ua.UserID = ?) AND sp.Frequency > 0 AND Deleted = 0 GROUP BY COALESCE('ACH:' pach.ReceivingRoutingNumber '' pach.ReceivingAccountNumber, COALESCE(pbp.PayeeKey, COALESCE('Wire:' pw.CreditRoutingNumber '' pw.CreditAccountNumber, NULL))), tcc.ID HAVING COUNT(pp.ID) > 0 ] Name:UserID - Value:9223372036854775807 [SQL: SELECT COALESCE('ACH:' pach.ReceivingRoutingNumber '' pach.ReceivingAccountNumber, COALESCE(pbp.PayeeKey, COALESCE('Wire:' pw.CreditRoutingNumber '' pw.CreditAccountNumber, NULL))) AS ID, tcc.ID AS TransactionCategoryClassificationID,
COUNT(pp.ID) AS PendingPaymentCount FROM core.ScheduledPaymentBase sp LEFT OUTER JOIN core.ScheduledPaymentACH spa ON (sp.ID = spa.ID) LEFT OUTER JOIN core.PaymentTemplateACH pta ON (pta.ID = spa.PaymentTemplateACHID) LEFT OUTER JOIN core.ScheduledPaymentBillPay spbp ON (sp.ID = spbp.ID) LEFT OUTER JOIN core.PaymentTemplateBillPay ptbp ON (ptbp.ID = spbp.PaymentTemplateBillPayID) LEFT OUTER JOIN core.ScheduledPaymentWire spw ON (sp.ID = spw.ID) LEFT OUTER JOIN core.PaymentTemplateWire ptw ON (ptw.ID = spw.PaymentTemplateWireID) LEFT OUTER JOIN core.PaymentTemplateItemBase ptib ON (ptib.PaymentTemplateBaseID IN (pta.ID, ptbp.ID, ptw.ID)) LEFT OUTER JOIN core.PaymentTemplateItemACH ptia ON (ptia.ID = ptib.ID) LEFT OUTER JOIN core.PaymentTemplateItemBillPay ptibp ON (ptibp.ID = ptib.ID) LEFT OUTER JOIN core.PaymentTemplateItemWire ptiw ON (ptiw.ID = ptib.ID) LEFT OUTER JOIN core.PayeeACHAccount pach ON (spa.PayeeACHAccountID = pach.ID OR ptia.PayeeACHAccountID = pach.ID) LEFT OUTER JOIN core.PayeeBillPayAccount pbp ON (spbp.PayeeBillPayAccountID = pbp.ID OR ptibp.PayeeBillPayAccountID = pbp.ID) LEFT OUTER JOIN core.PayeeWireAccount pw ON (spw.PayeeWireAccountID = pw.ID OR ptiw.PayeeWireAccountID = pw.ID) INNER JOIN core.Payee p ON ( p.ID IN ( SELECT ptib.PayeeID FROM core.PaymentTemplateItemBase ptib WHERE ptib.PaymentTemplateBaseID IN (pta.ID, ptbp.ID, ptw.ID) ) ) INNER JOIN core.TransactionCategoryClassification tcc ON tcc.ID = ( SELECT tc.TransactionCategoryClassificationID FROM core.TransactionCategory tc WHERE tc.ID = p.DefaultExpenseTransactionCategoryID ) INNER JOIN core.PendingPaymentBase pp ON pp.ScheduledPaymentID = sp.ID AND = 1 WHERE sp.SourceAccountID IN (SELECT ua.AccountID FROM core.UserAccount ua WHERE ua.UserID = ?) AND sp.Frequency > 0 AND Deleted = 0 GROUP BY COALESCE('ACH:' pach.ReceivingRoutingNumber '' pach.ReceivingAccountNumber, COALESCE(pbp.PayeeKey, COALESCE('Wire:' pw.CreditRoutingNumber '' pw.CreditAccountNumber, NULL))), tcc.ID HAVING COUNT(pp.ID) > 0]

**** STACK TRACE: at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters) in d:\CSharp\NH\nhibernate\src\NHibernate\Loader\Loader.cs:line 1683 at NHibernate.Loader.Loader.List(ISessionImplementor session, QueryParameters queryParameters, ISet`1 querySpaces, IType[] resultTypes) in d:\CSharp\NH\nhibernate\src\NHibernate\Loader\Loader.cs:line 1585 at NHibernate.Impl.SessionImpl.ListCustomQuery(ICustomQuery customQuery, QueryParameters queryParameters, IList results) in d:\CSharp\NH\nhibernate\src\NHibernate\Impl\SessionImpl.cs:line 2055 at NHibernate.Impl.SessionImpl.List(NativeSQLQuerySpecification spec, QueryParameters queryParameters, IList results) in d:\CSharp\NH\nhibernate\src\NHibernate\Impl\SessionImpl.cs:line 2037 at NHibernate.Impl.SessionImpl.List(NativeSQLQuerySpecification spec, QueryParameters queryParameters) in d:\CSharp\NH\nhibernate\src\NHibernate\Impl\SessionImpl.cs:line 2024 at NHibernate.Impl.SqlQueryImpl.List() in d:\CSharp\NH\nhibernate\src\NHibernate\Impl\SqlQueryImpl.cs:line 163 at Alkami.App.Core.Data.Dao.HibernateScheduledPaymentDao.FindPayeeClassificationScheduledPaymentCountByUser(Int64 userId) in C:\SRC\ithryv\Alkami.App\Source\Alkami.App.Core.Data\Dao\HibernateScheduledPaymentDao.cs:line 221 at CompositionAopProxy_3f677a65d116437faa1e64951adc1a05.FindPayeeClassificationScheduledPaymentCountByUser(Int64 userId) at dynamic_CompositionAopProxy*3f677a65d116437faa1e64951adc1a05.FindPayeeClassificationScheduledPaymentCountByUser(Object , Object[] ) at Spring.Reflection.Dynamic.SafeMethod.Invoke(Object target, Object[] arguments) in c:_svn\spring-net\tags\spring-net-1.3.1\src\Spring\Spring.Core\Reflection\Dynamic\DynamicMethod.cs:line 156 at Spring.Aop.Framework.DynamicMethodInvocation.InvokeJoinpoint() in c:_svn\spring-net\tags\spring-net-1.3.1\src\Spring\Spring.Aop\Aop\Framework\DynamicMethodInvocation.cs:line 100 at Spring.Aop.Framework.Adapter.ThrowsAdviceInterceptor.Invoke(IMethodInvocation invocation) in c:_svn\spring-net\tags\spring-net-1.3.1\src\Spring\Spring.Aop\Aop\Framework\Adapter\ThrowsAdviceInterceptor.cs:line 223

***** EXCEPTION SITE: FindPayeeClassificationScheduledPaymentCountByUser line 207-221 block where exception is thrown:

var session = SessionFactoryUtils.GetSession(Factory, true); if (session == null) throw new InvalidOperationException("Unable to obtain an NHibernate session from the session factory");

session.FlushMode = FlushMode.Never; var getPayeeClassificationScheduledPaymentCount = session.GetNamedQuery("GetPayeeClassificationScheduledPaymentCountByUser"); if (getPayeeClassificationScheduledPaymentCount == null) throw new InvalidOperationException("Unable to obtain the named query 'GetPayeeClassificationScheduledPaymentCountByUser'");

getPayeeClassificationScheduledPaymentCount .SetParameter("UserID", userId) .SetFirstResult(0) .SetMaxResults(1000);

return getPayeeClassificationScheduledPaymentCount.List();

** SQL QUERY 'GetPayeeClassificationScheduledPaymentCountByUser' REFERENCED ABOVE:

pach.ReceivingRoutingNumber '_' pach.ReceivingAccountNumber, COALESCE(pbp.PayeeKey, COALESCE('Wire:' pw.CreditRoutingNumber '_' pw.CreditAccountNumber, NULL))) AS ID, tcc.ID AS TransactionCategoryClassificationID, COUNT(pp.ID) AS PendingPaymentCount FROM core.ScheduledPaymentBase sp LEFT OUTER JOIN core.ScheduledPaymentACH spa ON (sp.ID = spa.ID) LEFT OUTER JOIN core.PaymentTemplateACH pta ON (pta.ID = spa.PaymentTemplateACHID) LEFT OUTER JOIN core.ScheduledPaymentBillPay spbp ON (sp.ID = spbp.ID) LEFT OUTER JOIN core.PaymentTemplateBillPay ptbp ON (ptbp.ID = spbp.PaymentTemplateBillPayID) LEFT OUTER JOIN core.ScheduledPaymentWire spw ON (sp.ID = spw.ID) LEFT OUTER JOIN core.PaymentTemplateWire ptw ON (ptw.ID = spw.PaymentTemplateWireID) LEFT OUTER JOIN core.PaymentTemplateItemBase ptib ON (ptib.PaymentTemplateBaseID IN (pta.ID, ptbp.ID, ptw.ID)) LEFT OUTER JOIN core.PaymentTemplateItemACH ptia ON (ptia.ID = ptib.ID) LEFT OUTER JOIN core.PaymentTemplateItemBillPay ptibp ON (ptibp.ID = ptib.ID) LEFT OUTER JOIN core.PaymentTemplateItemWire ptiw ON (ptiw.ID = ptib.ID) LEFT OUTER JOIN core.PayeeACHAccount pach ON (spa.PayeeACHAccountID = pach.ID OR ptia.PayeeACHAccountID = pach.ID) LEFT OUTER JOIN core.PayeeBillPayAccount pbp ON (spbp.PayeeBillPayAccountID = pbp.ID OR ptibp.PayeeBillPayAccountID = pbp.ID) LEFT OUTER JOIN core.PayeeWireAccount pw ON (spw.PayeeWireAccountID = pw.ID OR ptiw.PayeeWireAccountID = pw.ID) INNER JOIN core.Payee p ON ( p.ID IN ( SELECT ptib.PayeeID FROM core.PaymentTemplateItemBase ptib WHERE ptib.PaymentTemplateBaseID IN (pta.ID, ptbp.ID, ptw.ID) ) ) INNER JOIN core.TransactionCategoryClassification tcc ON tcc.ID = ( SELECT tc.TransactionCategoryClassificationID FROM core.TransactionCategory tc WHERE tc.ID = p.DefaultExpenseTransactionCategoryID ) INNER JOIN core.PendingPaymentBase pp ON pp.ScheduledPaymentID = sp.ID AND = 1 WHERE sp.SourceAccountID IN (SELECT ua.AccountID FROM core.UserAccount ua WHERE ua.UserID = :UserID) AND sp.Frequency > 0 AND Deleted = 0 GROUP BY COALESCE('ACH:' pach.ReceivingRoutingNumber '_' pach.ReceivingAccountNumber, COALESCE(pbp.PayeeKey, COALESCE('Wire:' pw.CreditRoutingNumber '_' pw.CreditAccountNumber, NULL))), tcc.ID HAVING COUNT(pp.ID) > 0 ]]>

***** RETURN CLASS DEFINITION PayeeClassificationScheduledPaymentCount

public class PayeeClassificationScheduledPaymentCount { public virtual string ID { get; set; } public virtual long TransactionCategoryClassificationID { get; set; } public virtual int PendingPaymentCount { get; set; } }

***** RETURN CLASS MAPPING

<?xml version="1.0" encoding="utf-8" ?>

Fabio Maulo added a comment — : please attach a failing test (only if you want that some committer will look to this issue, otherwise leave it as is). Thanks.

Sean McElroy added a comment — : I can confirm this exception as well with the precise conditions. Using a SQL named query with a colon in a quoted string caused this exception. My offending line was:

AND s. NOT LIKE '%:%'

I was able to workaround the issue by doing this:

AND s.[Value] NOT LIKE '%' CHAR(58) '%'

in the meantime

trivalik commented 2 months ago

Another example where colons give trouble for postgres:

DO $$
declare
  filename text;
begin
 -- I am a comment with ::inside
  filename := 'test';
end $$

In the comment is the colon interpreted as well! Root cause of parsing issue can be found at ParameterParser class here https://github.com/nhibernate/nhibernate-core/blob/7ed5bc82fc446ddaeed14c526fcde7bc5a65b28d/src/NHibernate/Engine/Query/ParameterParser.cs#L109.