SQL Script USE statement - C#

Question

In my C#.NET application, I use Microsoft.SqlServer.Management.Server.ConnectionContext.ExecuteNonQuery() to run a script against a SQL Server database.

I would like to know if there is a way to make sure the script does not contain "USE" statement.

I have to make sure the script is ran against a specific database and that it does not change DB while executing.

Thanks!

That's a bit of an odd request. Are you executing arbitrary SQL scripts from a remote source? — asawyer
– asawyer, Commented Nov 9, 2012 at 14:22
Of course there are ways. But nothing that is implemented. I would suggest you write a parser that checks exactly this condition. I think regular expressions can be used for this — Mohnkuchenzentrale
– Mohnkuchenzentrale, Commented Nov 9, 2012 at 14:24
What happens if the sql contains qualified table names? e.g. FROM [DB].[Owner].[TableName]? — James
– James, Commented Nov 9, 2012 at 14:26
Yes the script is a file but I read all the file and pass it as a string parameter. Let's say I have script1.sql. I set the connection string to use MyDB catalog. I execute the script againt MyDb. Now, I want to make sure the script won't execute against any other DB than MyDB. So: 1- No use Statement 2- NO fully qualified table names (Thanks JamesB) — Baral
– Baral, Commented Nov 9, 2012 at 14:28
That sounds very dangerous. The real question is - Do you trust the source of the file? If yes - Why filter? If no DONT RUN IT. — asawyer
– asawyer, Commented Nov 9, 2012 at 14:30

Joel Coehoorn · Accepted Answer · 2012-11-09 14:30:31Z

5

have to make sure the script is ran against a specific database and that it does not change DB while executing.

The way you control that in sql server is through security. You create a user account in sql server that only had read access, and only for that one database, and then make sure to use that account when connecting to the server. If that means using a different connection here than at other points in your app, so be it.

answered Nov 9, 2012 at 14:30

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

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

2 Comments

James Over a year ago

This is definately the way to go. Let the server worry about what the script can and can't do by just setting the correct permissions.

Baral Over a year ago

Thanks Joel, seems in fact the best way

Collectives™ on Stack Overflow

SQL Script USE statement - C#

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related