September 2, 2008 at 4:09 pm
A friend of mine is a developer for an organization that was hacked. He asked the following,
"select * from table where" column contains one of the following :
'insert','delete','select','update','create','alter','drop','truncate',
'grant','revoke','declare','exec','backup','restore','sp_','xp_','set',
'execute','dbcc','deny','union','Cast','Char','Varchar','nChar',
'nVarchar','@@','%20','sysObjects','schema',' '
Is there some way to do it that I'm not getting, or do I need to do the
whole list of column like '%x%' OR column like '%y%' etc...?
I mentioned that if he had 'char' then varchar, nvarchar, nchar were redundant. Other than that
I don't see a clever way to do what he suggests. I know the wildcards will be a tremendous
performance hit.
Any suggestions? Has someone already invented this wheel?
Mike Hinds Lead Database Administrator1st Source BankMCP, MCTS
September 2, 2008 at 4:35 pm
First, It is not at all clear to me what he is actually trying to do, that is not a valid query that is listed, and it is impossible to infer what it is is supposed to be doing as what is there is incomplete and ambiguous. Secondly, it is unclear what question he is trying to ask.
Maybe a little bit more context or explanation? Or at least valid SQL/procedure?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 3, 2008 at 3:18 am
Keyword filters aren't a very good way of defending against SQL injection.
1) Use only procedures (preferably) or parameterised queries from your front end apps.
2) Ensure that the app user has only rights on the procs and no rights on the base tables.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 4, 2008 at 6:38 pm
GilaMonster (9/3/2008)
Keyword filters aren't a very good way of defending against SQL injection.1) Use only procedures (preferably) or parameterised queries from your front end apps.
2) Ensure that the app user has only rights on the procs and no rights on the base tables.
Thanks for the advice. Your two valid points were included in a list of about 20 different methods that were recommended both by the multitude of websites and by the high-priced A-Team that were brought in by this guy's company.
At least you were able to identify that he was attempting to filter SQL keywords, and that it was pseudocode, not intended to be taken as "valid SQL", which had not yet been written (the reason for his inquiry).
Most of those methods (including yours) were implemented within the first few days following the breach. Trying to harden the installation further, and to identify the methods being used, he asked if I knew a clever way of identifying SQL keywords in a string, with his pseudocode as a beginning example of what he was looking for. I felt like if it had been done, someone here would have done it, and so I offered to post it in hopes of a helpful reply.
Mike Hinds Lead Database Administrator1st Source BankMCP, MCTS
September 7, 2008 at 5:57 pm
You may find it faster to use a CLR function (search for Matt Miller's posts here for more info) that wraps a dotNET RegEx object to match your keyword string. It'll still be ugly and brute force but may be more efficient than a nest of OR'ed LIKE statements.
So you could do something like:
...
WHERE dbo.fn_RegExMatch(MyColumn,'insert|delete|update|exec|whatever') = 'True'
Regards,
Jacob
September 7, 2008 at 9:48 pm
Try this:
USE MASTER
select * from syscolumns
where name like '%exec%'
See? Your filter would consider some system calls illegal.
Don't you have column "IsDeleted" anywhere in your database?
Many developers like those "flag" columns.
What your filter for DELETE will do with an attempt to update that flag?
Or with an attempt to record "DeteteDate" into some Activity Log table?
In real life those text filters just don't work.
Not to mention huge overhead added by validating every SQL call from application.
If you want to protect your system against SQL injections you need to use proper security model (users are not allowed to access tables) and never use ad-hoc SQL calls.
_____________
Code for TallyGenerator
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply