How do I validate an SQL query before executing it using C#

Question

I have a textbox where the user enters his SQL query. However,I need to make a program that validates the query before executing it in the database.

For Example:

Suppose the user enters

SELECT A1,
       A2,
       A3 
  FROM XYZ

So now, before executing the query, I need to check whether A1, A2 and A3 exists in the table XYZ or not. If not, the user should be shown an error message.

I'm not able to think of a way to proceed. So Can anyone give a basic idea with a sample code snippet about how to proceed further?

Answer 1

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.

Answer 2

Probably you need to do it one by one. First Check whether the Table XYZ exist or not

SELECT * FROM INFORMATION_SCHEMA.TABLES 
           WHERE TABLE_NAME = 'XYZ';

Then would come to the next question that is if the field name in the table exists or not

SELECT * FROM   INFORMATION_SCHEMA.COLUMNS
          WHERE  TABLE_NAME = 'XYZ'
                 AND COLUMN_NAME = 'A1'
                 AND COLUMN_NAME = 'A2'
                 AND COLUMN_NAME = 'A3'

Answer 3

The very suitable way is excecuting the code in MS SQL and let MS SQL figure out the errors.

StringBuilder  query= new StringBuilder();

query.Append("BEGIN \n");
query.Append("BEGIN TRY \n");
query.Append("    -- Table does not exist; object name resolution   \n");
query.Append("    -- error not caught.   \n");
query.Append("    --Append the variable which holds your sql query \n");
query.Append("    --For eg.: SELECT * FROM NonexistentTable;   \n");
query.Append("    END TRY \n");
query.Append("    BEGIN CATCH \n");
query.Append("      SELECT \n");
query.Append("        ERROR_NUMBER() AS ErrorNumber \n");
query.Append("       ,ERROR_MESSAGE() AS ErrorMessage; \n");
query.Append("    END CATCH \n");
query.Append("END");

Excecute the query using ExcecuteScalar() of SQLCommand.

SQL Server will return the exact errors for the query submitted.