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, Jul 27 '16 at 06:26
check this question:http://stackoverflow.com/questions/13316328/ways-to-validate-t-sql-queries — LoekD, Jul 27 '16 at 06: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, Jul 27 '16 at 06: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 http://stackoverflow.com/questions/1054984/how-can-i-get-column-names-from-a-table-in-sql-server http://stackoverflow.com/questions/1525672/determine-a-tables-primary-key-using-tsql — The Shooter, Jul 27 '16 at 06:26
You may also add a TOP 0 (yes, it still involves some basic parsing and it limits you to SELECT queries) to check query syntax without effectively retrieve all data from database (I hope you're running these queries with an account with read-only permissions). Unfortunately to _validate_ a query is a complex task because it involves both SQL syntax and schema information. Note that SELECT may be anywhere (sub-query, for example) but query optimizer should detect you don't need that data. — Adriano Repetti, Jul 27 '16 at 06:30
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, Jul 27 '16 at 06:34
That was just an example though. The user can enter any query he wants to. It must check if the syntax of the Query is correct and everything else before actually exeuting it — ankastic, Jul 27 '16 at 06:37

Dmitry Bychenko · Accepted Answer · 2016-07-27T06:58:53.860

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 '16 at 06:58

answered Jul 27 '16 at 06:43

Dmitry Bychenko

180,369
20
160
215

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"_ – Tim Schmelter May 11 '21 at 15:26
@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 – Dmitry Bychenko May 11 '21 at 15:56

Dani Mathew · Answer 2 · 2016-07-27T09:29:03.767

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.

score 1 · Answer 3 · answered Jul 27 '16 at 06:29

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'

score 0 · Answer 4 · answered Jul 27 '16 at 06:25

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 '16 at 06:25

Rahul Tripathi

168,305
31
280
331

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) – Zein Makki Jul 27 '16 at 06:28
@user3185569 the queries would be like the ones posted. – ankastic Jul 27 '16 at 06:44

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

4 Answers4