November 14, 2008 at 3:39 pm
A developer used code like this, before SQL Injection was exposed:
[font="Courier New"]DECLARE @sql varchar(200)
SET @sql = 'SELECT somefield FROM sometable WHERE PersonName LIKE '%' + @data + '%'[/font]
Which does leave the application open to injection attacks. Unfortunately, replacing the code to use parameters becomes complicated because some criteria require an int parameter, others a varchar...
As a quick and dirty, naive attempt, the contents of @data are checked for offensive content. Something like...
[font="Courier New"]
DECLARE @s-2 varchar(50), @b-2 bit, @sText varchar(50)
SET @sText = 'zz ; zz'
SET @s-2 = UPPER(LTRIM(RTRIM((@sText))))
IF @s-2 LIKE '%0X%' SET @b-2 = 0
ELSE IF @s-2 LIKE '%;%' SET @b-2 = 0
ELSE IF @s-2 LIKE '%= 1%' SET @b-2 = 0
ELSE IF @s-2 LIKE '%=1%' SET @b-2 = 0
ELSE IF @s-2 LIKE '%ALTER%' SET @b-2 = 0
ELSE IF @s-2 LIKE '%BACKUP%' SET @b-2 = 0
ELSE IF @s-2 LIKE '%BCP%' SET @b-2 = 0
ELSE IF @s-2 LIKE '%BEGIN%' SET @b-2 = 0
ELSE IF @s-2 LIKE '%BETWEEN%' SET @b-2 = 0
ELSE IF @s-2 LIKE '%COMMIT%' SET @b-2 = 0
ELSE IF @s-2 LIKE '%CREATE%' SET @b-2 = 0
ELSE IF @s-2 LIKE '%DBCC%' SET @b-2 = 0
ELSE IF @s-2 LIKE '%DECLARE%' SET @b-2 = 0
ELSE IF @s-2 LIKE '%DELETE%' SET @b-2 = 0
ELSE IF @s-2 LIKE '%DISABLE%' SET @b-2 = 0
ELSE IF @s-2 LIKE '%DROP%' SET @b-2 = 0
ELSE IF @s-2 LIKE '%EXEC%' SET @b-2 = 0
ELSE IF @s-2 LIKE '%GOTO%' SET @b-2 = 0
ELSE IF @s-2 LIKE '%GRANT%' SET @b-2 = 0
ELSE IF @s-2 LIKE '%INSERT%' SET @b-2 = 0
ELSE IF @s-2 LIKE '%JOIN%' SET @b-2 = 0
ELSE IF @s-2 LIKE '%LIKE%' SET @b-2 = 0
ELSE IF @s-2 LIKE '%NEWID%' SET @b-2 = 0
ELSE IF @s-2 LIKE '%OPEN%' SET @b-2 = 0
ELSE IF @s-2 LIKE '%RAISEERROR%' SET @b-2 = 0
ELSE IF @s-2 LIKE '%RETURN%' SET @b-2 = 0
ELSE IF @s-2 LIKE '%REVOKE%' SET @b-2 = 0
ELSE IF @s-2 LIKE '%SELECT%' SET @b-2 = 0
ELSE IF @s-2 LIKE '%SNMP%' SET @b-2 = 0
ELSE IF @s-2 LIKE '%sp_%' SET @b-2 = 0
ELSE IF @s-2 LIKE '%TRUNCATE%' SET @b-2 = 0
ELSE IF @s-2 LIKE '%UPDATE%' SET @b-2 = 0
ELSE IF @s-2 LIKE '%WAITFOR%' SET @b-2 = 0
ELSE IF @s-2 LIKE '%WHILE%' SET @b-2 = 0
ELSE IF @s-2 LIKE '%WRITE%' SET @b-2 = 0
ELSE IF @s-2 LIKE '%UPDLOCK%' SET @b-2 = 0
ELSE IF @s-2 LIKE '%xp_%' SET @b-2 = 0
ELSE SET @b-2 = 1
Is this too weak to even consider ?
November 14, 2008 at 4:03 pm
I would try setting this up to use sp_executeSQL, and passing parameters to it. the passing of parameters makes it substantially harder to get away with something.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 15, 2008 at 10:27 pm
J (11/14/2008)
Is this too weak to even consider ?
It's worse than weak, it makes your app non-functional. You do realize that under this scheme, your users would not be ale to search for "Charles Dropper," "Jackie Joiner" or "Joe Mexecali".
[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]
November 16, 2008 at 8:25 pm
J (11/14/2008)
A developer used code like this, before SQL Injection was exposed:[font="Courier New"]DECLARE @sql varchar(200)
SET @sql = 'SELECT somefield FROM sometable WHERE PersonName LIKE '%' + @data + '%'[/font]
As a possible alternative...
SELECT somefield FROM sometable WHERE PersonName LIKE @data
No dynamic SQL necessary in this case.
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
November 16, 2008 at 8:43 pm
Gail has a great solution to limit vulnerabilities.
November 17, 2008 at 6:59 am
Gail,
I have always found your writings very interesting.
Regarding your solution, it is not immediately clear how your suggestion works in terms of being less susceptible to attack. Would you please elaborate a bit more ?
Regards
Barry,
Yup, I already knew that some search words were rejected. It seemed to me acceptable that searching for Joe Mexecali would have to be truncated as "Joe Mexe".
It seemed like a small price to pay in exchange for reducing vulnerability. While being occasionally a nuisance, I did not see it as a complete failure.
I was also very concerned about the newest attack based on binary or hex strings being fed as a search word.
Thanks for offering cases where legitimate search words would be rejected. I did not come up on my own as to why anyone would want to type id ...exec...
Since SQL Server insists on key words followed by at least a space character, "Mexecali" would appear to be fine, while rejecting "Mexec ali".
Oh yeah, one thing I forgot to mention is that I also limited the length of the search argument to just a few characters.
November 18, 2008 at 7:06 pm
J (11/17/2008)
Gail,I have always found your writings very interesting.
Regarding your solution, it is not immediately clear how your suggestion works in terms of being less susceptible to attack. Would you please elaborate a bit more ?
I'm not concatenating anything into a string that I execute. The query is parameterised and anything in @data will be treated as a parameter value and not as part of the command.
For there to be an injection vulnerability, there has to be a value concatenated into a string and that string executed.
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
November 19, 2008 at 7:10 am
@Gail
My mistake. I did not immediately realize that @data was a parameter.
Too stuck up in my personal convention of labelling parameters with prefix @ps_ (if a strting) to distinguish from local variables, labelled with prefix @ls_ (if a string).
Probably low caffeine level yesterday.
Thanks.
Trying to find some more Jolt cola ...
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply