Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2004
    Posts
    145

    Unanswered: History of queries being run

    Is it possible to view a history of the queries being run against a db? Specifically I'd like to know if any "unauthorized" queries are being successfully run. We can see via our error table that people are trying to hack our system, but those are the ones that are unsuccessful because of bad syntax. So we fix those holes, but my concern is we can't see the holes when the hacker actually gets a query to run even if it doesn't really do anything.

    I've read a little about dbcc inputbuffer but I think that would require continuously querying the db which I don't think is an option.

    Thanks,
    GG
    Code:
    On Error Goto Hell
    
    Hell:
        Msgbox("An error occurred, but was sent to Hell. You may continue.")

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    any monitoring you do is going to incur some overhead. You could always SCRIPT a trace (not using profiler) but this will have an impact.

    The first thing I would do before you worry about this kind of intrusion detection, is to make sure the windows and doors are locked tight. Things you could to do this include but are not limited to...

    Deny all direct access to tables to non-sysadmin users. Force your developers to use command objects that only call stored procedures with properly typed parameters (no concatenated strings). Make sure your application only uses an account that only has permissions to fire those certain procedures it needs. Oh and you might to run that microsoft baseline security analyzer things on your DB server.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jan 2004
    Posts
    145
    unfortunately there is a whole lot of string concat going on. It was all done before I arrived here. So it is kind of a patch job at this point.

    Is there any performance difference between executing sprocs with recordsets and command objects? I know command objects take a bit more setup. Say I have a stored proc, it takes one parameter that I KNOW is going to be legit, it then returns some records. Is there any performance advantage running it using a recordset or command object? My thought is that if I know the param is good I can use a recordset and string concat for the parameter which is less code than setting up a command object.

    Thanks,
    GG
    Code:
    On Error Goto Hell
    
    Hell:
        Msgbox("An error occurred, but was sent to Hell. You may continue.")

  4. #4
    Join Date
    Jan 2004
    Posts
    145
    Hello, I'm just bumping this. I had got side tracked, but I'd like to know if anyone else has some suggestions.
    Thanks,
    GG
    Code:
    On Error Goto Hell
    
    Hell:
        Msgbox("An error occurred, but was sent to Hell. You may continue.")

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Is your question:

    "should I do this
    Code:
    rs.Open "EXEC dbo.something @some_param='" & some_variable & "'"
    Or this?"
    Code:
    cmd.CommandText = "dbo.something"
    cmd.Parameters.Append cmd.CreateParameter("@some_param", adVarChar, adParamInput, 10, some_variable)
    
    rs.Open cmd
    George
    Home | Blog

  6. #6
    Join Date
    Jan 2004
    Posts
    145
    No, my question is if there is a way to see what queries are being run against our database. Ideally something like:

    User:web_user
    Query: SELECT * FROM products
    Date: 2008-00-21 12:00:00

    I know we have to fix the string concat, but if I were able to see what hackers were trying to run it would help in two ways. First it would tell us they are indeed getting queries to run against our db and second we can see what they are trying to run which will help us in understanding their techniques if that makes sense.

    Thanks,
    GG
    Code:
    On Error Goto Hell
    
    Hell:
        Msgbox("An error occurred, but was sent to Hell. You may continue.")

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Oh I thought Sean had answered this part above
    Quote Originally Posted by Thrasymachus
    any monitoring you do is going to incur some overhead. You could always SCRIPT a trace (not using profiler) but this will have an impact.
    George
    Home | Blog

  8. #8
    Join Date
    Jan 2004
    Posts
    145
    How would I go about scripting a trace?
    GG
    Code:
    On Error Goto Hell
    
    Hell:
        Msgbox("An error occurred, but was sent to Hell. You may continue.")

  9. #9
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I believe you can design a trace in the SQL Server Profiler and save it as a script. Run it server side, monitor the size of your trace file (save to file and not table) with regards to available disk space and be weary of how much activity (number of events) you are monitoring. Good luck I still dork up the performance on production servers with a sloppy trace from time to time.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  10. #10
    Join Date
    Jan 2004
    Posts
    145
    Thanks Thrasy, that looks to be exactly what I need.
    GG
    Code:
    On Error Goto Hell
    
    Hell:
        Msgbox("An error occurred, but was sent to Hell. You may continue.")

Posting Permissions

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