Programmers Resource - Preventing SQL Injection

04-30-2005, 11:34 AM

purplepyro purplepyro is offline

Relational DB Theorist

Join Date: Apr 2005

Posts: 33

Preventing SQL Injection

For a detailed explanation on SQL Injection, click the link below:
http://www.securiteam.com/securityr...5DP0N1P76E.html

For this Tutorial, I will show you some of the basic steps to ensure that your code cannot be hit with an SQL Injection.

Whenever you request an ID from a querystring or form field that will eventually end up as part of your SQL statement, validate the variable type FIRST.

Example:

Code:

<%

'this piece of script assumes that your primary ID is some sort of integer

theID = Server.HTMLEncode(Request.Querystring("id"))
'note: htmlencode helps a great deal
' to remove any characters that may be part of the sql injection

'check to make SURE it is an integer
If theID = "" Or isNumeric(theID) = False Then
    theID = 0
End If

'after you have made SURE it is an integer - 
'you can now add the variable to your sql string without worries....

sql = "SELECT theField FROM theTable WHERE theID=" & theID
'etc etc etc....

%>

If you are using MS SQL Server, keep in mind that using stored procedures instead of creating sql commands within ASP will prevent injection as well.

Also, if you are "ultra-paranoid" about SQL Injection, you could use this function to replace any Sql Injection commands....

Code:

<%
'Format SQL Query function
Private Function formatSQLInput(ByVal strInputEntry)

	'Remove malicious characters from links and images
	strInputEntry = Replace(strInputEntry, "<", "&lt;")
	strInputEntry = Replace(strInputEntry, ">", "&gt;")
	strInputEntry = Replace(strInputEntry, "[", "[")
	strInputEntry = Replace(strInputEntry, "]", "]")
	strInputEntry = Replace(strInputEntry, """", "", 1, -1, 1)
	strInputEntry = Replace(strInputEntry, "=", "=", 1, -1, 1)
	strInputEntry = Replace(strInputEntry, "'", "''", 1, -1, 1)
	strInputEntry = Replace(strInputEntry, "select", "select", 1, -1, 1)
	strInputEntry = Replace(strInputEntry, "join", "join", 1, -1, 1)
	strInputEntry = Replace(strInputEntry, "union", "union", 1, -1, 1)
	strInputEntry = Replace(strInputEntry, "where", "where", 1, -1, 1)
	strInputEntry = Replace(strInputEntry, "insert", "insert", 1, -1, 1)
	strInputEntry = Replace(strInputEntry, "delete", "delete", 1, -1, 1)
	strInputEntry = Replace(strInputEntry, "update", "update", 1, -1, 1)
	strInputEntry = Replace(strInputEntry, "like", "like", 1, -1, 1)
	strInputEntry = Replace(strInputEntry, "drop", "drop", 1, -1, 1)
	strInputEntry = Replace(strInputEntry, "create", "create", 1, -1, 1)
	strInputEntry = Replace(strInputEntry, "modify", "modify", 1, -1, 1)
	strInputEntry = Replace(strInputEntry, "rename", "rename", 1, -1, 1)
	strInputEntry = Replace(strInputEntry, "alter", "alter", 1, -1, 1)
	strInputEntry = Replace(strInputEntry, "cast", "cast", 1, -1, 1)

	'Return
	formatSQLInput = strInputEntry
End Function
%>

Hope this bit of info keeps you safe!