SQL filter by LINQ with SELECTs

[Andrey]

Hello,

I want to filter LINQ-query with custom SQL-code (with SELECTs and UNIONs) through ExtensionMethods.SQL method. I need the resulting query be like this:

WHERE ID in (CUSTOM_SQL_HERE)

But the following exception occurs: Potential SQL injection detected: CUSTOM_SQL_CODE. This is because my code contains SELECT keyword.

How can I workaround this check? The possibility of use custom SQL filter is very important and greatly extend OpenAccess potential. Because this provides the ability to execute any custom queries without completely rewriting all query builder logic.

[Alexander]

Hello Andrew,

The SQL<T> extension method is not meant for including whole statements/subselects into the OpenAccess generated code but only small chunks of SQL, for example some specific handling of parameters. It is not allowed this SQL to contain words like SELECT, INSERT, UPDATE, DELETE, DROP and others, in order to avoid SQL injection and I am afraid this check cannot be avoided.

Could you please give more details about your scenario and the query you are trying to implement? There could be another better solution.

Regards,
Alexander
Telerik

[Andrey]

Hi, Alexander.

All that you wrote I understand and know. But sometimes the only one thing that requires using low-level API is complicated filter logic with SELECTs, UNIONs and subqueries. LINQ engine doesn't support some constructs. And it maybe many specific workarounds for this specific logic that you are possibly know. And once it turns out that this specific logic LINQ does not support. Or maybe handles incorrectly. And the only way to get it work will be to rewrite all query logic to low-level API for this case. 
Sometimes I need to make optimizations, for example, OR statement for MSSQL sometimes is bad for performance. And I need to replace it by UNION, that LINQ doesn't support yet (it does this in memory).

And I understand that protection against SQL-injection is really needed for modification statements (UPDATE, DELETE, ALTER). But why for SELECT?

Maybe to add partially unsafe version with SELECT allowed? Or maybe add specific option to Connection object that skips check for SELECT statement?

[Doroteya]

Hi Andrew,

Thank you for your feedback and excuse us for the inconvenience caused.

Indeed, there are scenarios that would be significantly easier to implement with such an option, although, we do not have plans to enhance Telerik OpenAccess ORM with it. Perhaps you could send us a sample query that requires it and we could come up with a feasible solution?

Additionally, for complicated scenarios and depending on the type of the model utilized by the application, we offer our Low Level (ADO) API and our Stored Procedures and Functions support.

Low Level (ADO) API is suitable when you have either a domain model (built with our Visual Designer) or a fluent model (built with our Code - Only Mapping). It allows you to execute practically all kinds of statements (stored procedures and functions as well) that fit best in your scenario with the help of ADO like code.

Our Stored Procedures and Functions support for Visual Designer allows you to utilize Domain Method Editor and to automatically generate domain methods for stored procedures and functions. It is suitable when you have a domain model and is based on the Low Level (ADO) API. The most significant benefit from it is that you do not have to write the code yourself and can consume the generated method in your application as any other method of the context class. This video offers a quick overview of the feature.

I hope this helps. I am looking forward to your feedback.


Regards,
Doroteya
Telerik

[Andrey]

Hello!

Does this check protect against which SELECT SQL-injection ? I searched on the Internet and didn't find injections. Almost all injections based on batches and modification scripts.
But allowing SELECT SQL will greatly extend limits of combining low-level API with LINQ-logic.

For example I need the following filter to be applied:

WHERE ProcessId IN (
          select Id from wf_Process p where p.RequestorUserId = @UserId
          union all
          select ID from wf_Process p where  p.RequestorUserId in (select distinct SubstitutedUserId from scr_UserGroupSubstitutor where UserId = @UserId))

In real situation my filter has dynamic table associations that added to query (thanks to artificial API).

In user interface this is checkbox filter and applies when checkbox set. All project listforms based on my listform-engine, in which user can specify needed fields from all fields and associations (large thanks for artificial API). 

I have 2 options:
1. To query this filter by separate low-level api request. And store results: a) memory b) temporary table. These both are bad.
2. Completely rewrite all listform logic for this listform. This will duplicate all logic. If I rewrite it in low-level way, then almost all OpenAccessORM functionality will be useless for me unfortunately.

Best regards, 
Andrey

Best regards,

Andrey

Best regards,

Andrey

[Boris Georgiev]

Hello Andrew,

Unfortunately at the moment there isn't any workaround for this issue.

Currently we are working on a feature which could resolve your issue, so in a future version the issue could have a workaround. Anyway I would advise you to create a feedback item in our Ideas and Feedback portal, there other users will also be able to vote and express their opinions and requests.

Please excuse us for the inconvenienced caused. 

Regards,
Boris Georgiev
Telerik

[Andrey]

Hello!

Could you give me little details about this planning workaround? 
It will allow SELECT in SQL<T> or not?

Regards,
Andrey

[Boris Georgiev]

Hello Andrew,

Unfortunately at the moment I am not able to give you any details. I would recommend you to create a feedback item in our Ideas and Feedback portal and to describe the issue and express your opinion. 

I will notify you in this thread when I have more information about the workaround.
 
Regards,
Boris Georgiev
Telerik

[Andrey]

Hello, Boris!

Thank you.
I placed feedback here: http://feedback.telerik.com/Project/114/Feedback/Details/88623-sql-filter-by-linq-with-selects

[Boris Georgiev]

Hello Andrew,

Thank you for the feedback. The feature is in our backlog.

I will notify you in this thread when I have more information about the progress of this feature.

Regards,
Boris Georgiev
Telerik

[Boris Georgiev]

Hi Andrew, 

I will post here the last information from my colleague about the feedback item, which you can also find in the feedback portal.

Thank you for your suggestion, but we see this as huge security issue not only related to SQL injections, but also to type safety. If we enable SELECT then developers can deliberately or not alter the return shape/type of the result, which may have unexpected and hard to track consequences in one complex query like yours. Additionally putting so much raw SQL statements in your LINQ query is a step back to Plain ADO.NET data handling with all the bad things of the both worlds.
Based on these grounds I don't think that we will implement such feature in near future.
As work around I may suggest that you implement your complex and performance critical queries either using Plain ADO.NET or using Stored Procedures and map back the results to your persistent types.

I am sorry for the inconvenience caused.

Regards,
Boris Georgiev
Telerik