Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1474
  • Last Modified:

How to Validate SQL Syntax Before Execution

I have a table of data that contains many different "where clauses".  I run those "where clauses" in a DTS package each night.  The problem is that for some reason a few of the "where clauses" are corrupt and causing my step in the DTS to fail when it tries to execute that statement after it has been dynamically built.  My question is "how do I validate the SQL syntax in the statement before the execution.  I would like to pass a string (sql statement) to some object and get back some type of "valid/not valid" check before I actually execute each statement.  I found something that looks close, but I am not using it properly.
=====
objDatabase.ExecuteImmediate(Script, SQLDMO.SQLDMO_EXEC_TYPE.SQLDMOExec_ParseOnly, Script.Length)
=====

For testing purpose lets just say I should be able to send it one of two strings:
  SELECT TOP 100 FROM tblUsers where LName = ('anderson')
    (valid)
  SELECT TOP 100 FROM tblUsers where LName = (anderson)
    (not valid)

Any help would be greatly appreciated.

Thank you,
  Chad
0
a_d_m_i_n
Asked:
a_d_m_i_n
  • 5
  • 5
  • 4
  • +1
1 Solution
 
ala_frostyCommented:
Say, why don't you just post the sp's that are building the 'corrupt' SQL Strings and we'll see about fixing them. It might be easier. I'm not familiar with any 'user' accessible parser under SQL. You're probably trying to do an implicit conversion and it's not liking it. When you post your code that's building the queries, be sure to give us the table schema.
0
 
a_d_m_i_nAuthor Commented:
well....its not that easy.... we have web pages that post the where clauses based on options the user choices.  When I see the bad "where clauses" I easily recognize they are bad and why they are bad.  The problem is that there currently are 21,000 users and they have all put in some criteria.  Some of those WHERE clauses got posted to the DB bad and when the DTS comes across one of those bad where clauses it chokes.  I would rather just be able to cofirm the SQL before I execute it and only execute it if the SQL is valid.  If the SQL that is not valid I will simply log it to a file and investigate why it is happening.

Thanks for your help.

-Chad
0
 
ala_frostyCommented:
I can see you have some work ahead of you. :-)
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
justinbilligCommented:
you going to have to fix the web code to make the SQL queries work.
0
 
a_d_m_i_nAuthor Commented:
The problem is the "web code" is extremely complex because the WHERE clause comes from many different options the user selects. All I'm basically trying to accomplish is the same thing the "check mark" does in Query Analyzer.  I would think MS just has that speed button make some type of stored function and assigns a button to that action.  There must be some type of SQL Server call I can do.

In the mean time, I'm just going to add some "on error resume next" / "on error skip record and report to file" type action, but I would rather not turn on the "on error resume next" because then the ActiveX script in my DTS could skip over something else that I don't want it to.

The best solution would be to figure out what function call MS is doing in Query Analyzer or find some other function that can validate SQL syntax with out executing it.

Of course once I figure out which set of logic is causing these bad "where clauses" we will fix them as find them, but it's way to hard to just dig into all the scripts (over 100 different scripts) that generate the "where clauses" for users on the site.  I'm sure it's only one or two scripts that are causing this, but it's a needle in a hay stack search to find.  It would be so much simpler to just find the bad records and find the common where clause.

Thanks,
  Chad
0
 
arbertCommented:
Well, it's not totally foolproof, but this might work for you:

SET FMTONLY ON
go
your query here.....
SET FMTONLY OFF


It doesn't actually execute the query, but it does parse it....There use to be a DLL floating around called SQLPARSE.DLL.  I can't really find it or see where it's supported any more though.

Brett
0
 
justinbilligCommented:
what you could do is run some scenarios on your webpage and instead of submitting the sql to the database, write the SQL to a file, run those queries then find out what is causing the errors ... the only real ( and best ) way to do it is to fix the web code
0
 
justinbilligCommented:
you might be able to run it in a stored procedure


create procedure uspRunQuery
(
          strQuery                varchar( 1000 )
)
AS
set nocount on

-- Run the query
EXECUTE ( strQuery )

-- If that query caused an error just return the string Error
if @@ERROR <> 0 THEN SELECT "ERROR"


You could then modify your ASP cod e to email you when the recordset / whatever returns  the string "ERROR" with any pertanant information ( Date, Time, SQL String, User, Page, Function) and then fix the problem. So each time you get an error you know exactly where to look
0
 
a_d_m_i_nAuthor Commented:
Thanks - I will try that later today. I was wondering how I was going to run that logic in my ActiveX Script.  I think your Stored Procedure method may work.  I will try it after my afternoon meeting.

-Chad
0
 
arbertCommented:
So what exactly are you looking for--I thought you wanted to validate the SQL without running it?
0
 
justinbilligCommented:
the SP i wrote will run the query, but it will tell you if you have an error, then he can take the appropriate measures
0
 
arbertCommented:
"the SP i wrote will run the query, but it will tell you if you have an error, then he can take the appropriate measures "

I realize that--that's why I asked the question.  The original question asked how to validate SQL  BEFORE execution....
0
 
justinbilligCommented:
yes i realize that but there really isnt a way, that will stop the query from interuppting DTS
0
 
arbertCommented:
Sure there is

set FMTONLY ON
0
 
a_d_m_i_nAuthor Commented:
Sorry I took so long... I had a new project jump up and had to drop this issue and finish the new thing.  I was able to create a SP with the code above and basicly find out if there was a error or not in the statement.  Thank you everyone for your help.

-Chad
0
 
arbertCommented:
For anyone browsing in the future, the accepted answer didn't answer the question on how to validate BEFORE execution...
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 5
  • 5
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now