Testing if a string is a valid SQL statement

  • OK, this is a new one for me. I'm working on a tool that will be used at design time. A user/developer will set some properties and I'll go off and effectively generate some SQL code. I'd like to give the user/developer the ability to set as one of the properties a SQL Select statement that returns a value. I'd expect the statement to be something like "SELECT CompanyId + ' ' + CompanyName from dbo.Company where CompanyId = ?". Think of it as setting the value of a group header.

    What I'd like to do at a minimum is to validate that the string provided is at least syntactically correct. Something along the lines of:

    If dbo.fnIsSQLValid(@sql) THEN

    --continue

    ELSE

    --error

    Of course, ideally I'd like to ensure that only one column is being returned. I'm not having much luck in finding an easy way to do this. Ideally I'd like to avoid actually executing the statement. And by the way, I'm not too concerned about SQL injection as this will be an internal, design time only tool with relatively expert users; I'm just trying to catch fat-fingered SQL statements not malicious intent. Any suggestions?

  • Check out "SET FMTONLY ON"

  • I've looked at that and SET PARSEONLY ON; both seem to work fine interactively but I can't get them to work inside a function.

  • Why can't you call it form within the app and not sql server?

  • Basically the architecture of what I'm trying to fit this into -- pretty much everything happens in SQL inside stored procs, so at this point I'm not really allowed any other options.

    I didn't think this would be so hard -- i thought there had to be something out there that I could pass a sql string to and get back a boolean good/bad flag. Sigh.

  • You could always use a stored prod and do something like this...

    Either use format only or parse only (never used that one so not sure what it does or how it works).

    Pseudo-almost-code 🙂

    TRY

    EXEC (@Mystring_with_fmtonly)

    catch

    --process errors here

    else go for it!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply