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

Question

0.00/5 (No votes)

See more:

Hi
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 exist 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? Thank you

What I have tried:

I have not tried anything as of now. I am not getting any logic to proceed further

Posted 26-Jul-16 20:15pm

Ankush Soni

Updated 26-Jul-16 21:42pm

v3

Add a Solution

Comments

Tomas Takac 27-Jul-16 2:41am

Why don't you just execute the query and show the error message you get from the database server? Why do you need to validate it yourself?

[no name] 27-Jul-16 3:04am

An idea, maybe not efficient and Needs to be worked out:
SELECT * FROM XYZ WHERE 1=0
The above will return you an empty result set, but I think there will be a way to determine at least the fieldnames of the table with it....(?)

Foothill 27-Jul-16 10:15am

I didn't check this to make sure it works but you might give it a once-over.
http://www.codeproject.com/Articles/410081/Parse-Transact-SQL-to-Check-Syntax

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2016-07-26T21:42:00

Solution 1

Pretty much, you can't - particularly when it comes to table and column names without actually running the query against SQL server. It's possible, but it would be a huge investment in effort to try and check every detail before you ran a query.
The best way would probably be to "test run" the query on the server within a transaction and always roll the transaction back. That way, if there were any problems (but part of it had an effect) it would not cause any changes to the DB anyway.
Use a try-catch-finally block, and rollback the transaction in the finally and it should give you a "success" / "fail" indication.
SqlTransaction Class (System.Data.SqlClient)[^]

Posted 26-Jul-16 21:42pm

OriginalGriff

Comments

[no name] 27-Jul-16 5:00am

Is not Prepare (in a try/catch) also enough to check the SQL?

OriginalGriff 27-Jul-16 5:25am

Unfortunately not - the SqlCommand.Prepare doesn't throw an exception for "bad table name" and so forth after VS2010.
https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.prepare(v=vs.110).aspx
Which makes it pretty much useless... :laugh:
If you try this:
try
{
using (SqlConnection con = new SqlConnection(strConnect))
{
con.Open();
DataTable dt = new DataTable();
using (SqlCommand cmd = new SqlCommand("SELECT Column FROM TableThatDoesntExist ORDER BY Title ASC", con))
{
cmd.Prepare();
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
da.Fill(dt);
}
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}

You'll get an exception at the DataAdapter.Fill, but not at Prepare.

[no name] 27-Jul-16 5:28am

Thank you for this, a 5.

OriginalGriff 27-Jul-16 5:47am

You're welcome!