Dynamically run SQL queries

Question

I want to run SQL queries which retrieve from a database according to user input.

Lets assume there is a table named Queries and the fields of the table are Index, Query, Name.

query - select * from Student
name  - GetStudents
Index - 1

When user clicks a button an index will be passed into the server and a query match with that index will be triggered. Assume there are no inputs into queries.

Lets say there are 5 rows in the table and when user pass 3,the third query will be run. When user pass 4 the fourth query will be run.

I think I can simply do this by storing the query as a string on table retrieving the query and run. But I'm not sure whether this workaround is efficient.

Please help me with these points.

Is this approach is okay or is there any better workaround that I can follow.
Is it okay to store query as a string in a table.
Is there any workaround that I can create Stored Procedures pragmatically using asp.net in SQL server management studio.

I'm using ASP.Net and SQL server.

Note that here I can't use Stored Procedures to do this task. Because there is another front-end where user can insert queries into table that I have mentioned above. User has no access to use SQL server management studio.

Dale K · Accepted Answer · 2020-01-14 06:37:16Z

1

In theory, yes you certainly could store the query string and then use sp_executesql to run that particular query string.

However, CAUTION. If you have a front end that allows a user to write and submit a query then how are you sanitizing that input? Is there anything to prevent the user submitting 'DROP DATABASE' as the query or event introducing other SQL injection attacks?

A better approach would be to create the procedures (assuming that the activities are all standard tasks) and allowing the user to select which procedure to execute.

edited Jan 14, 2020 at 6:37

Dale K

28.1k15 gold badges59 silver badges85 bronze badges

answered Jan 14, 2020 at 5:04

Martin Cairney

1,7771 gold badge9 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Buddhika Chathuranga Over a year ago

Yes. There is that kind of danger. But user who get access to that front-end is only selected users. Actually only admins will be granted the access into that front-end.

Martin Cairney Over a year ago

If they are admins then they should have permissions to connect with SSMS? Or just admins on the front end? Not all malicious users are hackers though - what if one of those admins was about to leave the company and wasn't happy - there goes your data!!!

Buddhika Chathuranga Over a year ago

Yeah I know. But unfortunately this is the requirement that I got.Since this is a maintenance tool they need to simplify the task as much as possible.

Martin Cairney Over a year ago

That scares me - data breach is all but guaranteed for that application.

Buddhika Chathuranga Over a year ago

Is there any workaround where I can create stored procedures programmetically. I think if it is possible I can continue with that. What is your idea..?

|

lptr · Accepted Answer · 2020-01-14 13:26:51Z

You could check the integrity of the dynamic select statement by executing it under the most restrictive security context (a dbuser that has readonly permissions). To take it a bit further, you could also wrap the dynamic select statement into an ever changing dynamic container/string (ever changing part + dynamic query + ever changing part) and suppress any errors that happen during the validation/integrity check. You cannot rely solely on sanitization because you'll end up in a never-ending catching-up struggle.

use mydbxyz;
go
--create a readonly dbuser
create user readonlydbuser without login;
alter role db_datareader add member readonlydbuser;
alter role db_denydatawriter add member readonlydbuser;
go

--procedure to execute dynamic select (no cte, no variables, just selects)
create or alter procedure execute_simpleselect @sqlinput nvarchar(max) = null
with execute as owner
as
begin
    set nocount on;

    if nullif(@sqlinput, '') is null
    begin
        --nothing to execute
        return;
    end


    --check if sql input is a valid/simple select query
    declare @foocontrol tinyint;
    declare @tblalias sysname = quotename(newid());

    declare @sqlcheck nvarchar(max) = N'
    select @var = 1;
    begin transaction;
    begin try
    select top (0) @var = '+ @tblalias + '.mycol
    from
    (
    select 1 as mycol
    where exists
    (
    '
    + @sqlinput +
    '
    )
    ) as '+ @tblalias + N'
    end try
    begin catch
        select @var = 2;
    end catch
    rollback transaction;
    ';

    /*
    create user readonlydbuser without login;
    alter role db_datareader add member readonlydbuser;
    alter role db_denydatawriter add member readonlydbuser;
    */

    --catch errors
    begin try
        --change context to a readonlyuser
        execute as user='readonlydbuser'; --if this dbuser does not exist, nothing executes
        exec sys.sp_executesql @stmt = @sqlcheck, @params = N'@var tinyint output', @var = @foocontrol output;
    end try
    begin catch
        --do nothing, suppress errors
    end catch
    revert;


    --if @foocontrol is not 1, the query cannnot be executed (syntactically incorrect, violation under minimal permissions etc)
    if isnull(@foocontrol, 2) = 2
    begin
        raiserror('what are you trying to do?', 16, 1);
        return;
    end

    --change to the callers security context
    exec as caller;

    exec sys.sp_executesql @stmt = @sqlinput;
end

--test
exec execute_simpleselect @sqlinput = 'select * from sys.objects';
exec execute_simpleselect @sqlinput = 'create table dbo.testtbl(id int)';
exec execute_simpleselect @sqlinput = 'drop table dbo.tablexzy';
exec execute_simpleselect @sqlinput = 'select user_name()';

Collectives™ on Stack Overflow

Dynamically run SQL queries

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related