!SQL Injection is my new best friend...

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

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

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

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...

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

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

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

Thanks John!



v/r

Gooser

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='' />

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