SQL Query-DDL or DML

Question

I have a query regarding identifying SQL query types through which I want to make out if any DDL statements are getting executed.

Is there is any C# API available to identify whether the SQL query type is DDL or DML?

No - the humanoid at the keyboard needs to decide that, based on what's in the query ... — marc_s, Nov 18 '11 at 07:11
see: http://stackoverflow.com/questions/589096/parsing-sql-code-in-c-sharp — Sam Saffron, Nov 18 '11 at 08:01

score 1 · Answer 1 · answered Nov 18 '11 at 07:47

1

In C# that's impossible except the parsing way proposed by devio.

To allow only DML statements to be executed you should use internal database security mechanisms. For example, move all DDL to stored procedure and "grant exec" to admin. Or grant "db_ddladmin" role to admin only.

answered Nov 18 '11 at 07:47

mikalai

1,746
13
23

This is for the end user where one can write sql queries and get the result in a UI. I need to prevent queries containing 'drop' or 'exec' etc. If I need to stop all of these by parsing it will be a huge list. Hence I'm looking for another option. – Neena Nov 18 '11 at 08:58
Then I'd suggest looking for SQL parsers - there is one for example at Code Project – mikalai Nov 18 '11 at 10:10

devio · Answer 2 · 2011-11-21T07:34:11.927

Does your question refer to code analysis or execution analysis?

In code, search for CREATE, ALTER, and DROP statements (depending on your data access layer).

For execution analysis, you can create DDL Triggers.

UPDATE after comment

After reading your comment I think the best solution is to use SQL Server's permission system to restrict the capabilities of the interactive user.

Create a database or application role, assign a user to that role, and select the GRANTs needed by the user/role. Run the interactive SQL statements in the context of this new user.

This way you can set permissions per table/view/sp/etc. and per command (SELECT/INSERT etc), without the need to parse the SQL statements.

This is something like I should allow only DML statements to be executed, to prevent harmful queries which may inject my DB. Using search for keywords will be cumbersome. — Neena, Nov 18 '11 at 07:32

score -1 · Answer 3 · answered Feb 19 '13 at 16:45

Run this code and at the end of it, you will have a summary of all DDL matches. Works perfect every single time!!

    string matchedSQL;

    //Parse DDL statements
    Regex DDL = new Regex(@"(?<=\b(create|alter|drop)\s+(procedure|proc|table|trigger|view|function)\b\s\[dbo\].)\[.*?\]", RegexOptions.IgnoreCase);
    Match match = DDL.Match(script);
    while (match.Success)
    {
         matchedSQL += match.Groups[1].Value + " " + match.Groups[2].Value + " " + match.Value;
        match = match.NextMatch();
    }

SQL Query-DDL or DML

3 Answers3