Classic ASP SQL Injection Protection

Question

What is a strong way to protect against sql injection for a classic asp app?

FYI I am using it with an access DB. (I didnt write the app)

Solution 1

Stored Procedures and/or prepared statements:

https://stackoverflow.com/questions/1973/what-is-the-best-way-to-avoid-sql-injection-attacks

Can I protect against SQL Injection by escaping single-quote and surrounding user input with single-quotes?

Catching SQL Injection and other Malicious Web Requests

With Access DB, you can still do it, but if you're already worried about SQL Injection, I think you need to get off Access anyway.

Here's a link to the technique in Access:

http://www.asp101.com/samples/storedqueries.asp

Note that what typically protects from injection is not the stored procedure itself, but that fact that it is parameterized and not dynamic. Remember that even SPs which build dynamic code can be vulnerable to injection if they use parameters in certain ways to build the dynamic code. Overall, I prefer SPs because they form an interface layer which the applications get to the database, so the apps aren't even allowed to execute arbitrary code in the first place.

In addition, the execution point of the stored procedure can be vulnerable if you don't use command and parameters, e.g. this is still vulnerable because it's dynamically built and can be an injection target:

Conn.Execute("EXEC usp_ImOnlySafeIfYouCallMeRight '" + param1 + "', '" + param2 + "'") ;

Remember that your database needs to defend its own perimeter, and if various logins have rights to INSERT/UPDATE/DELETE in tables, any code in those applications (or compromised applications) can be a potential problem. If the logins only have rights to execute stored procedures, this forms a funnel through which you can much more easily ensure correct behavior. (Similar to OO concepts where objects are responsible for their interfaces and don't expose all their inner workings.)

Solution 2

Here are a couple of sqlinject scripts I made a long time ago a simple version and a extended version:

function SQLInject(strWords) 
dim badChars, newChars, i
badChars = array("select", "drop", ";", "--", "insert", "delete", "xp_") 
newChars = strWords 
for i = 0 to uBound(badChars) 
newChars = replace(newChars, badChars(i), "") 
next 
newChars = newChars 
newChars= replace(newChars, "'", "''")
newChars= replace(newChars, " ", "")
newChars= replace(newChars, "'", "|")
newChars= replace(newChars, "|", "''")
newChars= replace(newChars, "\""", "|")
newChars= replace(newChars, "|", "''")
SQLInject=newChars
end function 


function SQLInject2(strWords)
dim badChars, newChars, tmpChars, regEx, i
badChars = array( _
"select(.*)(from|with|by){1}", "insert(.*)(into|values){1}", "update(.*)set", "delete(.*)(from|with){1}", _
"drop(.*)(from|aggre|role|assem|key|cert|cont|credential|data|endpoint|event|f ulltext|function|index|login|type|schema|procedure|que|remote|role|route|sign| stat|syno|table|trigger|user|view|xml){1}", _
"alter(.*)(application|assem|key|author|cert|credential|data|endpoint|fulltext |function|index|login|type|schema|procedure|que|remote|role|route|serv|table|u ser|view|xml){1}", _
"xp_", "sp_", "restore\s", "grant\s", "revoke\s", _
"dbcc", "dump", "use\s", "set\s", "truncate\s", "backup\s", _
"load\s", "save\s", "shutdown", "cast(.*)\(", "convert(.*)\(", "execute\s", _
"updatetext", "writetext", "reconfigure", _
"/\*", "\*/", ";", "\-\-", "\[", "\]", "char(.*)\(", "nchar(.*)\(") 
newChars = strWords
for i = 0 to uBound(badChars)
Set regEx = New RegExp
regEx.Pattern = badChars(i)
regEx.IgnoreCase = True
regEx.Global = True
newChars = regEx.Replace(newChars, "")
Set regEx = nothing
next
newChars = replace(newChars, "'", "''")
SqlInject2 = newChars
end function

Solution 3

Using parametrized querys, you need to create a command object, assign it parameters with a name and a value, if you do so you wouldn't need to worry about anything else (refering to sql injection of course ;))

http://prepared-statement.blogspot.com/2006/02/asp-prepared-statements.html

And don't trust stored procedures, they can became a attack vector too if you don't use prepared statements.

Solution 4

"A strong way to protect against sql injection for a classic asp app" is to ruthlessly validate all input. Period.

Stored procedures alone and/or a different database system do not necessarily equal good security.

MS recently put out a SQL Injection Inspection tool that looks for unvalidated input that is used in a query. THAT is what you should be looking for.

Here's the link: The Microsoft Source Code Analyzer for SQL Injection tool is available to find SQL injection vulnerabilities in ASP code

Solution 5

if stored procedures are not an option - and even if they are - validate all inputs thoroughly

View more solutions

Comments

[Flory]

Stored procedures are not the answer (even if he was not using Access) because you can still write injectible code using SP's (I have seen it). It is parameterized queries that protect you.

[Aisah Hamzah]

@Flory, isn't that exactly what he says?

[Flory]

@Abel, it sure does. I don't know what I was smoking two years ago.