Code to Validate SQL Scripts

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;
}

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.

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 Query Syntax with Python and SQLite

I've settled with creating an in-memory database and executing the queries in which I am interested. However, the following code example is very slow and I will continue looking for a better solution. Also, I am aware of the vulnerability to SQL injection attacks in the following code, but that is not something with which I am concerned at the moment.

import sqlite3

# open the SQL file and read the contents
f_contents = open("example.sql").read()

# Use regexes to split the contents into individual SQL statements.
# This is unrelated to the issues I'm experiencing, show I opted not
# to show the details. The function below simply returns a list of
# SQL statements
stmnt_list = split_statements(f_contents)

temp_db = sqlite3.connect(":memory:")

good_stmnts = []    # a list for storing all statements that executed correctly
for stmnt in stmnt_list:
    # try executing the statement
    try:
        temp_db.execute(stmnt)
    except Exception as e:
        print("Bad statement. Ignoring.\n'%s'" % stmnt)
        continue
    good_stmnts.append(stmnt)

temp_db.close()

An error while processing the validating sql scripts(ACUMATICA UPGRADE 18.217.002 to 19.207.0026)

Sometimes after an upgrade inappropriate errors can be shown which force validation can be enforced to ignore the error by creating a CstValidationIgnore.txt file in visual studio and adding it to your project. To learn more about it visit acumatica 2019R2 documentation at To Use an Ignore List for the Validation Errors

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

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

---

Related Topics