Adventures In Other Programmers Code

sql injection was not considered... at all

[Easy_Coder]

I've just inherited an asp.net 1.1 app that has all passthru sql and no measures in places to stop any sql injection monkey business. I showed the folks that wrote the application how to get full access to their administration panel and they about croaked.

I prefer a data access layer and stored procedures but that's going to be a 2 week effort so in the meantime I need a stop gap measure that keeps the application secure.

I added a class to the project (regex not fully tested yet):

Imports System.Text.RegularExpressions
Public Class SQLUtilities
 REM *****************************************************************
 REM Remove any non alpha-numeric data from the command
 REM *****************************************************************
 Public Function CommandSecurity(ByVal sqlCommand As String) As String
 Return CStr(Regex.Replace(sqlCommand, "^.*[^A-Za-z0-9].*$", ""))
 End Function
End Class

Then I have to fire this thing in roughly 50+ methods that span over 4000 lines of code. Sorta like this:

Dim SQLUtil As New SQLUtilities
Dim mySQL = " select * from table where col1='" + SQLUtil.CommandSecurity(valOne) + "' and col2='" + SQLUtil.CommandSecurity(valTwo) + "'"

Is there an easier way to do this?

[wardbekker]

Yah, let the original coders clean up their mess :-)

[Easy_Coder]

Yeah right... they've been let go.

[FireFromWithin]

That seems ok as a stop gap solution.

I think the Regex needs work

Regex.Replace(sqlCommand, "[^A-Za-z0-9]", "")

this will replace all non-alphanumeric characters in the string with nothing. but spaces will be lost.

Maybe

Regex.Replace(sqlCommand, "'", "''")

is simpler

[tomasz]

going to be a 2 week effort

I've been using NetTiers for sometime now and you can probably do it in less that that..
[community.codesmithtools.com...]

[Easy_Coder]

Do you know if codesmith runs in the Medium Trust space? That looks promising for me if it does.

Thanks on the Regex tip...

[tomasz]

It is not a issue for me since I have my own server.
Here is a link to that thread

[community.codesmithtools.com...]

[Easy_Coder]

That doesn't look like it will work for me then. I'll probably have to put my own dal together. Thanks.

[aspdaddy]

Dont forget to replace ; and -- and add some logging.

I use a function thats checks for :
' -- ; GRANT DENY DROP ALTER DELETE INSERT UPDATE SELECT CREATE

[Easy_Coder]

My original regex should catch ', ; and -- because they're not alpha numerics.

--> GRANT DENY DROP ALTER DELETE INSERT UPDATE SELECT CREATE are solid suggestions.

Thanks...