Link to home
Start Free TrialLog in
Avatar of hindersaliva
hindersalivaFlag for United Kingdom of Great Britain and Northern Ireland

asked on

SQL Server - running a stored procedure to run a SELECT statement that's passed as a parameter

I'm building a learning tool. A the client app will send a Exec statement to run a stored procedure, with a parameter which will be a SELECT statement.
I want the stored procedure to run the SELECT statement. But that's a string. So I'm not sure how that can be done in the stored procedure.

Say the SQL statement is in the string strSQL.
EXEC (strSQL) in the stored procedure will not work. It's looking for a stored procedure by that name.

Thank you for any ideas.




Avatar of ste5an
ste5an
Flag of Germany image

In general: This an approach which should be avoided. SQL Injection, permissions and scope problems may arise.

For your case: What kind of tool makes you think, this approach is necessary?
If you need to dynamically build the SQL statement, you can pass parameters to the stored procedure and build the SQL string in the stored procedure. This will generally make it much easier to prevent a SQL Injection attack.

But you can build pretty complex SQL Statements that take advantage of parameters without actually having to resort to building a SQL string in the SP.
Avatar of hindersaliva

ASKER

Ah ste5an. My experience with SQL injection is only hearing about it. I thought sending the SQL statement as a parameter to a stored procedure will prevent such attacks. So, only the specified statement will be executed. I could be completely on the wrong lines so please tell me if my thinking is wrong.
The SQL statement is (necessarily) sent from an Excel app via connecting to the SQL Server on Azure with ADO.


I should have said, these are VERY simple SELECT statements. Later I shall have very simple INSERT INTO, UPDATE and DELETE.

Good idea Dale re. passing parts in parameters to assemble in the SP.

Also, I'm hoping to setup a User that will only have permission to run named SPs.
If that's possible.

This does not change my question.

Cause to put it into other words:
This is approach is most scenarios just terribly wrong.

If you need to send a SELECT, INSERT or UPDATE just do it directly. Don't introduce such a layer of indirection. A RDBMS is no like a OO language, where these kind of patterns (proxy, service, service façade) are good.

Only parameterized queries or equivalent handling prevents SQL injection. Sending a complete SQL statement as string does not. It is per definition always a SQL injection.

The SQL statement is (necessarily) sent from an Excel [..]

Open in new window

Why not sending directly?
Also, I'm hoping to setup a User that will only have permission to run named SPs.
If that's possible.
This is the classic no-go scenario. Cause it means to circumventing security.

 If your user needs to have this kind of access, create an appropriate role and add your user to it and send the statements directly.
Hi,

If the SQL is a string and passed to the stored procedure then you simply use the Prepared Statement to execute it. Thus a dynamic statement.
https://www.mssqltips.com/sqlservertutorial/2907/sql-server-prepared-statement/

One point often overlooked is that in such use cases where you pass a query as a string to a stored procedure, then the stored procedure has become a gateway for hackers to exploit and gain access to your database and retrieve all the data they desire. Thus such stored procedures are very likely to cause a data breach.

Regards,
   Tomas Helgi

Ste5an, I see your point now. I was thinking the other way round, which I can now see that I wasn't seeing properly.

@Tomas: Using Prepared Statemtn is for parameterized queries, not for general SQL statements of any kind like @hindersaliva seems to have in mind.
@ste5an
I know that but Prepared Statement can be used to execute SQL statements with zero to n parameters.

Having a SP with that takes any SQL and executes it blindly using prepared statement will most likely cause a data breach in a distant/near future.

Regards,
     Tomas Helgi

@Tomas: Then it makes no sense. It adds an layer of complexity, the call, for no difference. Prepared statements require that you have permissions on the objects, not the prepare call.
So, is the issue with 1 to n parameters, are those query criteria?  If so, you can predefine the parameters in your SQL Statement and setup default values.  For example, a SQL statement that looks like:

Declare @ProgramID int = 0
SELECT P.* FROM tblPrograms as P
WHERE (@ProgramID = 0 OR P.ProgramID = @ProgramID)

Open in new window

can be used to return all records from tblPrograms by either passing in a zero to the @ProgramID parameter, or simply leaving that parameter out of the SQL command that is passed to SQL Server.  Or, if you pass that parameter into the query, it can return a single record.

You can do this with multiple parameters which is basically the same as programmatically adding criteria to a WHERE clause based on the value in some search field.
@ste5an

Note that
An ownership chain exists when objects that access each other sequentially are owned by the same user. For example, a stored procedure can call other stored procedures, or a stored procedure can access multiple tables. If all objects in the chain of execution have the same owner, then SQL Server only checks the EXECUTE permission for the caller, not the caller's permissions on other objects. Therefore you need to grant only EXECUTE permissions on stored procedures; you can revoke or deny all permissions on the underlying tables.

https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/managing-permissions-with-stored-procedures-in-sql-server

This means that if you implicit access to all tables that are under the same schema as the stored procedure.
So any query can be executed, using such SP as we have talked about, on the tables that the schema owner has access to to retrieve data.

Therefore, best practices in creating Stored Procedures with parameters is to have the parameters only accept data/values that are validated and used in a query, insert,update or delete statements inside the Stored Procedure.

Regards,
    Tomas Helgi
Therefore, best practices in creating Stored Procedures with parameters is to have the parameters only accept data/values that are validated and used in a query, insert,update or delete statements inside the Stored Procedure.
Yes Tomas.
Thanks for the link re. Prepared Statement. I need to study that a little more carefully.
This is a low-risk app. The data is only for complete beginners to learn very basic CRUD. A typical query will be
SELECT Guitarists, City FROM tblRockstars.
And some WHERE and GROUP BY and combinations of fields to return.

So, it looks like my best approach should be to stick with a normal Stored Procedure? Anything other than SELECT will have a Username/Key so that only the data with their Username/Key can be altered by that user.

I suppose the best way forward is to post the actual app for you experts to try and hack into. Right?
Can do.
I'll be back.



@Tomas: nope. He wants to execute SQL as string. Thus dynamic SQL.
Here's a short video that may help put my question in context.
Thanks.
https://youtu.be/4F3sQhnWWew


Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

and if they execute:
drop table rockstars


IMHO, using Excel to re-invent the MANY SQL front ends out there is a really bad idea.  There are MANY SQL tools already out there.  Why use a program that was never intended to do what you are trying to make it do?

Then you have the problem of properly displaying any syntax issues or errors coming back from the database.

Personally I would create the schema/database/??? for their sandbox and create a read only user for them to use.

Have you looked at Azure Data Studio?
https://docs.microsoft.com/en-us/sql/azure-data-studio/what-is-azure-data-studio?view=sql-server-ver15

SSMS is probably overkill for a simple SQL worksheet but it exists.

There are also tools like DBeaver that is a cross-database tool:
https://dbeaver.io/

When people are learning SQL, some sort of object browser is pretty important so they can see the tables and column names.  They also provide syntax checking and intellisense in many cases.
slightvw, I agree with you completely.

But this is for a different purpose. Many millions of spreadsheets are storing data in them. Those spreadsheets do an important job, but they're chaotic because Excel can work better with data that is stored externally. Hence, moving the location of the data in a spreadsheet model to a database brings huge advantages in organisations.

But the purpose here is to let the users themselves become aware of it (not turn them into DBAs!). To do that, they need to overcome their fear of SQL. This tool is to help with that. By giving them an Excel tool to practice with they become more comfortable with how Excel manages data stored externally.

So, we're not trying to create a 'front end to a database with Excel', but moving the data into a database, where the spreadsheet can work with it (automated) more robustly. Nor are we trying to turn accounting staff into SQL programmers. But they need to have an appreciation of SQL; because one short SQL statement can replace 1,000s of formulas when consolidating 100s of spreadsheets.

I guess we can agree to disagree on ways to teach non-IT people SQL.

Then what better than a GUI where you can drag and drop?

I know many DBAs that cannot write good SQL...  ;)

>>Nor are we trying to turn accounting staff into SQL programmers

End users probably shouldn't be writing SQL to start with.  That's what Reporting and Analytics tools are for.


We have all pointed out that this is a bad idea.

But it appears we cannot talk you out of it.

I think what you are looking for in your procedure is:
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql?view=sql-server-ver15
ASKER CERTIFIED SOLUTION
Avatar of Andrei Fomitchev
Andrei Fomitchev
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Andrei, perfect! Exactly what I'm looking for.

Now to assess the risks. If any of my students try to sobotage the class I can just throw him/her out. LOL!

(thinking aloud)
Mitigating risks = abort if keywords like DROP are present. Only SELECT, INSERT, UPDATE and DELETE are allowed.
And nothing is allowed to run unless a valid AccessKey is present in a parameter, checked against a table.

>>And nothing is allowed to run unless a valid AccessKey is present in a parameter

I'm far from an Excel Expert but I'm betting there are still ways to obtain the database connect string from the code.  A quick google says you can password protect the VBA but I'm not sure how secure that actually is.

Then I don't need your key.

I'll bet I can get around any code you write that only allows SELECT, INSERT, UPDATE and DELETE.  It is next to impossible to stop malicious intent.

Possibly chain SQL statements together, use convert to make my drop statement a varbinary and then back to varchar to be executed?

Granted, even if you use a SQL tool like Azure Data Studio, you still have the drop problem.....

>>If any of my students try to sobotage the class I can just throw him/her out.

If this is a class, then again, why re-invent the wheel?  Use a tool that is designed for SQL.

Say you do your job and they really LOVE SQL.  Then what?  Give them a tool they can take away from any class and continue learning.  They can download SQL Server Express and some canned databases and continue learning on their own.  Like the AdventureWorks  database?

https://docs.microsoft.com/en-us/sql/samples/adventureworks-install-configure?view=sql-server-ver15&tabs=ssms

Give them something they can take with them.
slightvw, I appreciate your input (though it is 3:30 am here).

It's pretty simple to circumvent a simple scan for DROP.
That approach does not stops me from creating a query utilizing the entire tempdb, which will stop all other processes.
That approach does not stops me from creating a query filling the entire database.

For learning SQL, I can only recommend Itzik Ben-Gan's T-SQL Fundamentals.

Use a appropriate tool allowing syntax highlighting and proper syntax checks.
Using Azure Data Studio is a cheap solution.
And using a users (groups or roles) allows you to exactly to control what the can do using permissions as well as resources.

Using your sproc approach will work, but will make the task to control this very much harder. And there is a bigger chance for errors. So that you in the end will have serious issues.
Proper tools give you more information about errors, so learning can be faster. Well, okay, imho SQL should be learned the old 6.5 way in a console.

For learning SQL it is absolutely ok to demand also to learn how to run a Local DB instance.  

It currently sounds like you want to teach your students how to grate wood, but won't give them a proper rasp to exercise.
>>For learning SQL it is absolutely ok to demand also to learn how to run a Local DB instance.  <<
ste5an,
This sounds like a workable idea/compromise.
Thanks

You can use security settings to forbid dropping of DB objects:
CREATE TRIGGER NO_DROP_TABLE
ON DATABASE
FOR DROP_TABLE
AS
PRINT 'Dropping tables are not allowed'
ROLLBACK

Open in new window

The events like DROP_TABLE are listed here:
https://docs.microsoft.com/en-us/sql/relational-databases/triggers/ddl-events?view=sql-server-ver15

Or you can grant only SELECT, UPDATE, INSERT, DELETE (whatever you need) to a student's account like:
GRANT SELECT on TestTable1 TO Student1;
About the points raised by slightwv (䄆 Netminder) - good points.
However, if 20 people come to my class, either classroom or online, having paid to be there it is very unlikely that any of them will want to sobotage the class by messing up my exercise data.
The login will only be live during the class.