There is a part in software which allows user to build a complete query. In which then a Variable is replaced by the software and is executed.
This also means that a malicious user could write something like delete update insert into this query.
Only a very, very limited number of users can actually create these custom reports.
But still my questions are:
ADOConnection or ADOQuery itself to read-only?To question 2 something likes this
my software itself always add line :
SET SESSION READONLY
USER CREATED SQL QUERY IS ADDED
even if user uses insert/update/delete it is prevented from execution.
If non of the above would work in worst case I could check the user entered query for keywords like insert update delete and prevent saving.
Thank you
UPDATE 1
I thought this would be a legit question . And a valid situation.
The application itself uses ONE secret username and password to connect to the database, and naturally it needs Read/Write Access. All user rights are handled in the application. And everywhere I use Stored Procedures . Only in one part I allow administrators to write a Query without restrictions, this is due to the fact that Customer Reports can get really unique , and sometimes they need to create Temporary Tables insert stuff there and read data etc.
That is why I was hopping that like Oracle MSSQL also has something like READ ONLY for the Session .
Meaning even if I allow the user to completely construct the query from scratch I always tell the SQL Server before that the query that follows is only read only .
Like it was mentioned before it is more or less impossible to prevent this in Software to write INSERT,UPDATE,DROP or something else that is bad . Because it can be combined from multiple words together .
if I allow the user to completely construct the query from scratch I always tell the SQL Server before that the query that follows is only read only .
As stated in the comments, the way you do this in SQL Server is with permissions. If you are constrainted to connect to the server with a single set of credentials, you can use impersonation or application roles to have different security contexts within a single connection.
For instance the privileged application user can impersonate a less-privileged user for running the user-provided query.
create user ReportUser without login
grant select to ReportUser
grant impersonate on user::ReportUser to ApplicationUser
--then before running the custom query
execute as user='ReportUser' with no revert