Parsing a SQL string in c#

Question

I have the need to Parse a Command.CommandText.

I don't want to run the query. I only want to see if the query will succeed if the command was executed.

Say i have; "SELECT * FROM SomeTable WHERE (1=1)"

This string will succeed.

but,

"SELECT * FROM SomeTable WHERE (1=1"

will not succeed.

Now my question. How would i Parse this string c#?

Sadie

Why don't you just hand this a PREPARE operation, which should complain the SQL query is malformed? – Elery

Possible duplicate of #2055110 – Interested

@Ira Baxter How would i do this? I don't know what functions i should use in c#. Should i create a function in SQL and pass the string to the function and let it check the string or is there such a function in c#? – Sadie

If you have a wrong connection string, CLR throws an exception when u try to open it. So u dont have to pass it to SQL. Second, you need to write parser for this string to check if you need to validate. You need to make use of stack to push each encountered keyword and again pop item by item and seeing if entry and exit keywords match. Hope ur getting my point. – Damiondamita

Why do you want to parse the string? Do you just need to check that it is syntactically valid or do you need to check that it is valid syntax and references valid objects etc.? – Apps

Have you looked at this yet https://mcmap.net/q/259856/-parsing-sql-code-in-c-closed – Counterchange

@zenwalker: I assumed he had a valid database connection already open, and is using ODBC (after all, he has a SQL text string; what else can he do with it but give it to ODBC?). I told him how to avoid writing his own parser. – Elery

@Willem: Every ODBC package has an EXECUTE call that accepts a valid SQL string, which you must already be using somehow. They also have a PREPARE entry point, which accepts a valid SQL string, and pre-compiles it into an efficient request to be (repeatedly) used later. I'm suggesting you pass your possibly malformed string to that; it will complain if the string is illegal syntactically (and likely even if it references tables or columns not in the DB). – Elery

Answer 1

If you just want to validate the syntax. You can use Microsoft.Data.Schema.ScriptDom for this.

using Microsoft.Data.Schema.ScriptDom;
using Microsoft.Data.Schema.ScriptDom.Sql;

.....

        string sql = "SELECT * FROM SomeTable WHERE (1=1";
        var p = new TSql100Parser(true);
        IList<ParseError> errors;

        p.Parse(new StringReader(sql), out errors);


        if (errors.Count == 0)
            Console.Write("No Errors");
        else
            foreach (ParseError parseError in errors)
                Console.Write(parseError.Message);

Apps

This is perfect. Just what i was looking for. Thanks. =) – Sadie

Answer 2

I've tried a handful of libraries that are designed to parse/manipulate/generate SQL in code. The most recent was ActiveQueryBuilder, but I know there are many out there.

With AQB - you would be able to 'validate' SQL but the problem I think you'll run into is that it is not 100%. None of them, that I've used, provide identical results to the actual database. You will find some certain SQL string that appears valid to your parser but invalid to the database or vice-versa. For example, in the AQB you couldn't have a subquery without giving it an alias or the parser would throw an exception - but Oracle would gladly accept and run the same SQL.

Depending on the database, you should be able to ask the DATABASE to validate the SQL without running it. In SQL Server, I believe you can use the Prepare statement, in Oracle I believe it's called an Explain Plan.

That's the only way I've found to get consistent results. Of course, if your queries are expected to be simple or if you don't require 100% accuracy it might be more work.

Kathlenekathlin

Answer 3

Based on the fact that you use SqlCommand, I assume you want to check correctness of the statement against Sql Server. As folks pointed out, Prepare statement is the one to go with. On the c# side you can only check the correctness of the syntax, and nothing else (that does not mean the query will pass). Prepare statement will check naming resolution, binding and more, but it will consist of a round trip to the server. Moreover, you have to be aware of its limitations (ex. temporary objects). For detail on TSQL Prepare statement take a look here.

Roadability

Answer 4

As you said: "I don't want to run the query."

Microsoft.Data.Schema.ScriptDom is a good choice if you only need to validate SQL syntax of Microsoft SQL Server. Otherwise, you may consider other SQL Parser such as General SQL Parser(support Oracle, SQL Server, DB2, MySQL, Teradata, PostgreSQL) help you to do offline SQL syntax check in your program.

Floppy

Answer 5

This can be done by analyzing your query with help of formal grammar but this might be an overload for you and totally depends on your usage scenario.

For analyzing any formal languages I advise you to use Irony library which is very popular both in production and individual projects and already has a built-in SQL grammar, so it would be just a matter of invoking that initializer. It would give you info about errors and their type but for something simpler use Ira's proposal.

Bandbox