Ways to validate T-SQL queries?
Actually, a combination of MattMc3's answer and FremenFreedom's answer should work.
Download SQL Express.
Then, declare the following stored procedure:
create procedure IsValidSQL (@sql varchar(max)) as
begin
begin try
set @sql = 'set parseonly on;'+@sql;
exec(@sql);
end try
begin catch
return(1);
end catch;
return(0);
end; -- IsValidSQL
You can test it with:
declare @retval int;
exec @retval = IsValidSQL 'select iif(val, 0, 1) from t';
select @retval
or with:
declare @retval int;
exec @retval = IsValidSQL 'select val from t';
select @retval
Note: this will catch the IIF() issue. It will not catch anything related to the table structures or column structures. You would need the schema for that and a slightly different approach ("select top 0 * from () t") woudl do it.
You might be able to do something with SQL Fiddle online. However, I would suggest having a local copy of the database.
How do I validate an SQL query before executing it using C#
I doubt if you should do this:
- what if
XYZ
is not a table, but a view, materialized view, stored procedure (depends on RDBMS) which returns cursor? - what if
XYZ
is a table, but user has not permission (grant
) to read it? - what if user has no permission on, say,
A2
field reading?
There're other cases which should be taken into account
- the query can be re-written (e.g. in case of Oracle via FGA - Fine Grain Audit)
XYZ
can be a synonym for whatever, e.g. dblink to remote table on Hadoop, while this Hadoop is temporary out of service
So I suggest executing the query without any preliminary check, but parsing and explaining exception thrown if any.
How to validate sql query syntax?
dont think there is any (easy) way to validate sql
Sql syntax is complex and allows for alot of different ways to enter a statement.
Think you best shot would be to just execute the sql statent and if you have a SQl exception see if its a bad syntax thats causing it.
you can prepend some sql to avoid from actually executing the query
in sybase it would be
SET NOEXEC ON
Query validation using C#
If you want to validate SQL syntax without the use of a database, the TSql100Parser
class will do well for this situation.
Disclaimer, code borrowed from this post here Code to validate SQL Scripts
Pretty straightforward to use though. If it returns null, then there were no errors in parsing it.
using Microsoft.Data.Schema.ScriptDom;
using Microsoft.Data.Schema.ScriptDom.Sql;
public class SqlParser
{
public List<string> Parse(string sql)
{
TSql100Parser parser = new TSql100Parser(false);
IScriptFragment fragment;
IList<ParseError> errors;
fragment = parser.Parse(new StringReader(sql), out errors);
if (errors != null && errors.Count > 0)
{
List<string> errorList = new List<string>();
foreach (var error in errors)
{
errorList.Add(error.Message);
}
return errorList;
}
return null;
}
}
Validate SQL queries and result sets
If you're looking to provide a free-form window into your SQL server that allows students to execute queries but not destroy the database (as SQL Fiddle does), then the simplest thing to do is to build a webapp that takes their SQL as input and runs it within a transaction. Capture the result sets from each query as you execute it. After executing each of their SQL statements, roll back the transaction; none of their changes will then be committed to the server.
If you suspect your students are incredibly mischievous and will stop at nothing to break the database (as I have with SQL Fiddle users), then you will have to guard against explicit transaction commits, such as commit transaction;
. Guarding against this is highly database-specific; I recommend looking through my code on github to see how I protect the various databases for SQL Fiddle (hint - the easiest server to protect is PostgreSQL; use it if you can). You may also find it interesting to read through my question on dba.se on the subject of preventing explicit commits: Disable explicit commits in JDBC, detect them in SQL, or put the database in a readonly state
Parsing a SQL Server query without executing the query against a database connection
As a general rule, you cannot parse SQL with a simple search method, the rules are way too complex for that. You need a full lexer and grammar. But since SQL Server 2012 you have the Transact-SQL Language Service option, which is what tools like Visual Studio use to parse T-SQL w/o a back-end server. You can leverage it from C# via Microsoft.SqlServer.Management.SqlParser.Parser.Parse()
.
Code to validate SQL Scripts
If you are creating a tool that allows the user enter some sql code by hand and you want to validate the code entered using C# code before execution on sql server, you can create a method like this:
using Microsoft.Data.Schema.ScriptDom;
using Microsoft.Data.Schema.ScriptDom.Sql;
public class SqlParser
{
public List<string> Parse(string sql)
{
TSql100Parser parser = new TSql100Parser(false);
IScriptFragment fragment;
IList<ParseError> errors;
fragment = parser.Parse(new StringReader(sql), out errors);
if (errors != null && errors.Count > 0)
{
List<string> errorList = new List<string>();
foreach (var error in errors)
{
errorList.Add(error.Message);
}
return errorList;
}
return null;
}
}
As of 2018 and new database versions, this might be newer version:
using Microsoft.SqlServer.TransactSql.ScriptDom;
(download with npm: PM> Install-Package Microsoft.SqlServer.TransactSql.ScriptDom -Version 14.0.3811.1 )
public bool IsSQLQueryValid(string sql, out List<string> errors)
{
errors = new List<string>();
TSql140Parser parser = new TSql140Parser(false);
TSqlFragment fragment;
IList<ParseError> parseErrors;
using (TextReader reader = new StringReader(sql))
{
fragment = parser.Parse(reader, out parseErrors);
if (parseErrors != null && parseErrors.Count > 0)
{
errors = parseErrors.Select(e => e.Message).ToList();
return false;
}
}
return true;
}
Related Topics
Postgresql With-Delete "Relation Does Not Exists"
Why Do I Need to Explicitly Specify All Columns in a SQL "Group By" Clause - Why Not "Group by *"
Index Autoincrement for Microsoft SQL Server 2008 R2
Is There Any Better Option to Apply Pagination Without Applying Offset in SQL Server
SQL Server 2008:Cannot Insert New Column in the Middle Position and Change Data Type
There Is Already an Object Named '#Columntable' in the Database
Selecting Distinct Values for Multiple Columns
How to Convert the System Date Format to Dd/Mm/Yy in SQL Server 2008 R2
Using Oracle SQL, How Does One Output Day Number of Week and Day of Week
SQL Server: How to Get a Database Name as a Parameter in a Stored Procedure
Ignore Certain Columns When Using Bulk Insert
Select "Where Clause" Evaluation Order
Postgresql Does Not Use a Partial Index
With Hibernate, How to Query a Table and Return a Hashmap with Key Value Pair Id>Name