Problem
In some cases when executing a query via Conext.ExecuteStoreCommand through Devar Edmx provide then it is not able to bind all the instances of parameters in query. For me it happens only when I used large and complex queries with multiple parameters
Sample Code
string entitySQL = "A Large and somplex ORACLE query";
Root Cause
Root cause could not be investigated but it seem that it is a problem with Devart Entity Data Provider for Oracle and it not able to parse complex queries efficiently. In my one project it worked all the queries except few that ware quite large involving complex joins of more that 10-15 table.
Solution:
As of now I am not able to identify any amicable solution except hard find replace.
Warning
If parameters are being received from external world (like end user) them must validate them against unexpected values. Preferably put a strict length limit on parameters.Otherwise application will be under threat of SQL Injection Attack however depending upon nature of application and parameters defiance strategy may vary check out more details at msdn.
In some cases when executing a query via Conext.ExecuteStoreCommand through Devar Edmx provide then it is not able to bind all the instances of parameters in query. For me it happens only when I used large and complex queries with multiple parameters
Sample Code
string entitySQL = "A Large and somplex ORACLE query";
Devart.Data.Oracle.OracleParameter[] parameters =
{
new Devart.Data.Oracle.OracleParameter("LANG_CD", language.ToUpper()),
new Devart.Data.Oracle.OracleParameter("CNTRY_CD", country.ToUpper())
};
ObjectResult<MyEntity> result = this.CurrentDataSource.ExecuteStoreQuery<MyEntity>(entitySQL, parameters);
Root Cause
Root cause could not be investigated but it seem that it is a problem with Devart Entity Data Provider for Oracle and it not able to parse complex queries efficiently. In my one project it worked all the queries except few that ware quite large involving complex joins of more that 10-15 table.
Solution:
As of now I am not able to identify any amicable solution except hard find replace.
entitySQL = entitySQL.Replace(":LANG_CD",MyLanguage);
entitySQL = entitySQL.Replace(":CNTRY_CD",MyCountry);
// Do not pass parameter collection.
ObjectResult<MyEntity> result = this.CurrentDataSource.ExecuteStoreQuery<MyEntity>(entitySQL);
Warning
If parameters are being received from external world (like end user) them must validate them against unexpected values. Preferably put a strict length limit on parameters.Otherwise application will be under threat of SQL Injection Attack however depending upon nature of application and parameters defiance strategy may vary check out more details at msdn.
No comments:
Post a Comment