Results 1 to 8 of 8

Thread: Confused...

  1. #1
    Join Date
    Mar 2003
    Location
    Atlanta, GA
    Posts
    191

    Unanswered: Confused...

    Feel like I should know this....

    Does anyone know of a way to test a query for syntax without actually running the query?

    I have an asp page that creates a series of nearly 50 INSERT statements from about 20 parameters entered by the user.

    I don't want to even begin the process of doing the inserts unless all the queries have correct syntax and will work without error. (Reversing the process, if an error occurs on Insert statement #43 would be a real pain.)

    My plan is to create ALL the Insert statements in an array... and then run them through some sort of syntax test PRIOR to actually submitting them to the database.

    Some of the Inserts will get pretty complex... and depending on the user's parameters... some of which are based on retrieved data... there is a possibility of many odd syntax problems.

    Any ideas?

    Tim
    Tim

  2. #2
    Join Date
    Apr 2002
    Location
    Germany
    Posts
    228
    Many databases have a DESCRIBE, EXPLAIN or similar statement that will show you the execution plan of a query. If your query has a syntax error the statement will throw an error too.
    Another possibility would be to encapsulate all statements within a big transaction and roll it back if one statement fails, this saves you the work of undoing the queries yourself.
    The "clean" way would be to check all parameters for validity _before_ executing any query. This is also strongly advisable from a security standpoint to prevent SQL injection attacks.

  3. #3
    Join Date
    Jan 2004
    Location
    India
    Posts
    31

    Re: Confused...

    Originally posted by MrWizard
    I don't want to even begin the process of doing the inserts unless all the queries have correct syntax and will work without error. (Reversing the process, if an error occurs on Insert statement #43 would be a real pain.)


    Tim

    sorry for editing ur post ;-)

    hey, u can try using objConn.BeginTrans at start of processing of INSERT statements and on error use Rollback. I donno how helpful this is

    btw .try this page for some help
    http://www.w3schools.com/ado/met_conn_begintrans.asp
    Do not walk behind me, for I may not lead.
    Do not walk ahead of me, for I may not follow.
    Do not walk beside me, either.
    Just leave me alone.

    Yogesh Jangam
    http://yogeshjangam.blogspot.com

  4. #4
    Join Date
    Jun 2003
    Location
    USA
    Posts
    1,032
    Instead of acting on the SQL statements you could have your Web page simply print the statements. Then you could separately test them one by one by copying and pasting them into something like the query builder area of Access changing any % to be * instead.
    J. Paul Schmidt, Freelance Web and Database Developer
    www.Bullschmidt.com
    Access Database Sample, Web Database Sample, ASP Design Tips

  5. #5
    Join Date
    Mar 2003
    Location
    Atlanta, GA
    Posts
    191
    Originally posted by Bullschmidt
    Instead of acting on the SQL statements you could have your Web page simply print the statements. Then you could separately test them one by one by copying and pasting them into something like the query builder area of Access changing any % to be * instead.

    Thanks.... but I'm not sure I understand you're suggestion.

    The point is that I'm already certain of the basic structure of the SQL... that's no problem. My issue is with the data that a user might enter.

    I don't want to wait until I actually submit the query to know if it will work ok. I'm looking for a way to test the query... just as if it were submitted... but not actually have it processed.

    The issue is that these multiple queries rely on diverse and unpredictable data entry.... with untrusted users. In addition, if submitted, the queries willl take a LONG time to run, and will modifiy existing data..... which means reversing them will be a big of a nightmare.

    What I'm now doing is simply checking for illegal characters before submitting the query.

    Tim
    Tim

  6. #6
    Join Date
    Dec 2003
    Posts
    454
    This is the better way to check for illegal characters before submitting the query.

  7. #7
    Join Date
    Apr 2002
    Location
    Germany
    Posts
    228
    Yes, if the users are untrusted you MUST validate the entered data correctly before running the query. A malicious user might construct parameters that will actually pass your "execution test" but do harmful things to your data.
    Imagine this simple query:
    Code:
    MyCon.Execute "DELETE FROM MyTable WHERE MyName='" & MyUntrustedParameter & "'"
    An attacker could pass something like:
    Code:
    doesnotexist' OR 'bla'='bla
    as MyUntrustedParameter. The query would execute and delete all records in MyTable.

  8. #8
    Join Date
    Jun 2003
    Location
    USA
    Posts
    1,032
    In addition, if submitted, the queries willl take a LONG time to run, and will modifiy existing data..... which means reversing them will be a big of a nightmare.
    I'd suggest testing with sample data instead of the real thing (i.e. perhaps use a copy of the database or something if possible).
    J. Paul Schmidt, Freelance Web and Database Developer
    www.Bullschmidt.com
    Access Database Sample, Web Database Sample, ASP Design Tips

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •