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?

if you want just check the tablename and columns name, You can read all Object from Sys table in SQL server and cached it, and just check the object in your application.But if you want to check the syntax i have no idea. — Mahdi Farhani
– Mahdi Farhani, Commented Jul 27, 2016 at 6:26
check this question:stackoverflow.com/questions/13316328/… — LoekD
– LoekD, Commented Jul 27, 2016 at 6:26
You can change approach and not check a query but provide user all possible columns which user can select. Then you will be sure that all selected columns exists in database — Fabio
– Fabio, Commented Jul 27, 2016 at 6:26
if it's simply checking if columns exist in the database or not you can run a query again schema information tables and check if columns exist or not otherwise you might need to use some tool which can parse the query. Check these links stackoverflow.com/questions/1054984/… stackoverflow.com/questions/1525672/… — The Shooter
– The Shooter, Commented Jul 27, 2016 at 6:26
Seems like a lot of effort to go to when a SQL database will surely provide you an error anyway if the query is ill-formed or references non-existent objects. What benefit do you perceive in having your application having to duplicate (faithfully?) that work? — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Jul 27, 2016 at 6:34

Dmitrii Bychenko · Accepted Answer · 2016-07-27 06:58:53Z

7

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.

edited Jul 27, 2016 at 6:58

answered Jul 27, 2016 at 6:43

Dmitrii Bychenko

188k20 gold badges178 silver badges231 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Tim Schmelter Over a year ago

You forgot to mention that this would be valid sql too but probably not desired: SELECT A1 FROM XYZ; DROP TABLE XYZ;. This speaks against your advice to "execute the query without any preliminary check"

Dmitrii Bychenko Over a year ago

@Tim Schmelter: for undesired, but still valid sql I suggest using priveleges (GRANT / REVOKE) : if DDL (and may be DML) is not desired, then we should no grant user to perform such operations. Parsing query for DROP, ALTER, may be DELETE, UPDATE etc. is, IMHO, a bad design

Dani Mathew · Accepted Answer · 2016-07-27 09:29:03Z

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.

Mohit S · Accepted Answer · 2016-07-27 06:29:58Z

1

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'

answered Jul 27, 2016 at 6:29

Mohit S

14.1k6 gold badges36 silver badges73 bronze badges

Comments

Rahul Tripathi · Accepted Answer · 2016-07-27 06:25:29Z

0

So now, before executing the query, I need to check whether A1,A2 and A3 exist in the table XYZ or not.

If you want to check if the values exists in the table you have to query in the table. Without executing the query you cannot find if the value exists in the table.

If you are working in SQL Server(for example) then you can make use of the IF EXISTS clause like

IF EXISTS(
    SELECT *
    FROM sys.columns 
    WHERE Name = 'A1' AND Name = 'A2' AND Name = 'A3' 
      AND Object_ID = Object_ID(N'XYZ'))
BEGIN

END

answered Jul 27, 2016 at 6:25

Rahul Tripathi

173k33 gold badges292 silver badges341 bronze badges

2 Comments

Zein Makki Over a year ago

I think he can. He can query the INFORMATION_SCHEMA for column names and check that. But that needs some heavy parsing for the query to detect column names used. Unless the user have only simple queries like the one posted (With no sub-queries and aggregate functions)

ankastic Over a year ago

@user3185569 the queries would be like the ones posted.

Collectives™ on Stack Overflow

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

4 Answers 4

2 Comments

Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related