huangs3
asked on
Validate T-SQL text in powershell?
Hi Experts,
I am using Powershell to write a T-SQL query validation to validate it before it actually get executed in database.
I want to check whether it is only a select-statement or involves any table/view drop/created, and return error if there is not only one single select-statement.
I checked the MSDN SQL Server Tool and UI Class Library tool but haven't found the way to do it.
Can you help me to find a way?
Thank you!
Below is the environment I am working with:
Remote database: SQL Server 2012
Powershell version on desktop: 4.0
SQL Server Management Studio on desktop: 2014
Desktop OS: Win 7
I am using Powershell to write a T-SQL query validation to validate it before it actually get executed in database.
I want to check whether it is only a select-statement or involves any table/view drop/created, and return error if there is not only one single select-statement.
I checked the MSDN SQL Server Tool and UI Class Library tool but haven't found the way to do it.
Can you help me to find a way?
Thank you!
Below is the environment I am working with:
Remote database: SQL Server 2012
Powershell version on desktop: 4.0
SQL Server Management Studio on desktop: 2014
Desktop OS: Win 7
ASKER
I am automating the validation of some sql select statements by test running them to make sure they returns some records for some down-the road technical reason. The database account I am using can do everything such as dropping a view. Practically mistake can be made when the sql select statement is submitted to the validation script I write, by including a "DROP" statement (myself have been do that). Hence it seems to be helpful if I can do some syntax validation on the sql statements before actually running them.
ASKER
Limiting the DML from DB account may be a technical option, but I want to make it safe from the code first.
SQL Server engine gives you all the tools that you need to secure your databases so writing code to reinvent the wheel doesn't seems to me practical. Anyway I can't help you with Powershell since I'm a SQL Server Expert only.
ASKER
Thanks for your try. It is ok.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I think ruling out the most obvious keywords ( CREATE, DROP, ALTER, UPDATE, MERGE) is the feasible thing that can be done. As you say, it cannot find some difficult case such as multi-select, but still better than nothing. Finding the CREATE, DROP, ALTER, UPDATE, MERGE can still make the code safer.
There are more practical solutions to not allow DML (Data Management Language).