SQL Server Performance

!SQL Injection is my new best friend...

Discussion in 'General DBA Questions' started by Gooser, Jul 18, 2006.

  1. Gooser New Member

    Note the subtle use of the ! character

    I've been compiling a list of symbols/words/phrases to remove from passed in varchars to help protect against SQL Injection. Here's what I've got so far:




    '
    "
    ;
    !
    +
    -
    --
    =
    <
    >
    <=
    >=
    !
    !=
    GRANT --This one is tricky, because Grant likely is the name of a Street/City/County
    REVOKE
    CREATE
    INSERT
    SELECT
    AS
    FROM
    WHERE
    AND
    OR
    UPDATE
    DELETE
    ALTER
    DROP --This one could be problematic as well. It is dangerous to pass to the database, yet may be needed as part of a name. “Dewdrop”, “Dropoff”
    TRUNCATE
    DETACH
    Bcp
    Exec --This could be an abbreviation of Executive, and could be part of a name as well, I#%92ve seen “Executive Park” as a name for a strip-mall.
    exec(
    execute
    execute(
    insert
    openquery
    restore
    shutdown
    sp_sqlexec
    sp_executesql
    xp_cmdshell



    Does anyone have anything to add to this list?

    Thanks,

    v/r

    Gooser
  2. spacemonkey New Member

    Rather than compiling a list of words, you might try to look for a way to cut the opportunity for SQL injection out completley. What is the script/procedure etc. that you are worried about SQL injection for?

    John
  3. Gooser New Member

    John,

    Thank you for asserting the obvious. Unfortunately, the (dynamic SQL) code exists and must be used until 'version 2.0'. In the meantime, I don't want us to be standing here wide open to attack. I need to patch the holes before the boat sinks, then I can hopefully catch enough fish to buy a new boat.

    Getting rid of the dynamic SQL would be ideal, however, in the short-term, I can't.

    Thanks,

    v/r

    Gooser
  4. Haywood New Member

    Regular expression checks are going to be your best friend for a while. I highly suggest you learn how to regex through your params in order to 'validate' them...
  5. FrankKalis Moderator

  6. satya Moderator

  7. spacemonkey New Member

    Hmmm. That's what I was afraid of. That's why I asked what type of script situation you were in.

    I'm going to do a little more captain obvious stuff just to make sure it's covered.

    1. I hope hope hope your application doesn't log in as sa.
    2. Assuming it has its own login account, is there any way to limit its security to only exec on particular procedures and isolated selects?
    3. I see that you are filtering out xp_cmdshell. If your proxy account is not configured and you are not logged in as an admin, this should not be a problem. Though, it isn't a bad idea to filter it anyway.
    4. quotename() is a good idea if you are forced to have dynamic table or column names.

    It may sound silly but you may also think about trying to use two procedures. The first parses the user input and the second takes the parameterized input to perform the select. The feasibility of this option is dependant on your user input but it may be the temporary solution you are looking for.


    John
  8. Gooser New Member


    Good-god man!

    (first let me preface the following statements with a little history. I am new to this company and I have been out of the dba world for about 2 1/2 years...so I may be a little rusty in spots. Also, the contractor they had here seems to shrug off every best-practice in the book, so that I am neck-deep in things to find-and-fix. That said...)

    It's only on our dev box at this point, but there's only one user in the database and that's dbo, logged in as ... you guessed it: sa!

    I can feel my neck muscles tightening...

    So on top of being full of dynamic sql, our db is being logged into as sa.

    Hey, I've got an idea, why don't we put our server out on the sidewalk and put a post-it with the sa password on the front? That way, anyone passing by can do whatever they want!





    v/r

    Gooser
  9. spacemonkey New Member

    Two words come to mind.

    1. Backups
    2. Pray

    I'm sorry that you have to deal with a situation like the one you are describing. The worst part may be that the users will be authenticating through SQL Server. That means that the password sa can be probably be obtained by a network sniffer and anyone who has 5 minutes on their hands.

    Here are a few keywords to maybe help hide the identity of the login account?

    CURRENT_USER
    USER
    SETUSER
    SESSION_USER
    SYSTEM_USER

    John
  10. Gooser New Member


    Thanks John!



    v/r

    Gooser
  11. ghemant Moderator

    Hi,
    more over
    1). file system should be NTFS
    2). change the listening port
    3). strengthen password mechanism
    4). if possible use windows authentication
    5). set a proper firewall rule also


    Regards

    Hemantgiri S. Goswami
    ghemant@gmail.com
    "Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri S. Goswami
  12. merrillaldrich New Member

    You could secure the server by cutting the small cable that runs into the network card at the back...<br /><br /><img src='/community/emoticons/emotion-1.gif' alt=':)' />
  13. Gooser New Member

    Flash-bangs, I need a bunch of flash-bangs, and a fire-extinguisher-sized can of OC.

    That'll take care of most of the users.



    v/r

    Gooser


    quote:'Why do today that which might not need to be done tomorrow?'
    --me

Share This Page