SQL Injection Prevention - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Other Development Tools

SQL Injection Prevention

Author

Topic

mellamokb
Starting Member

39 Posts

Posted - 2006-05-23 : 17:56:25

How's this for an idea?

To protect against SQL Injection in ASP-VBScript (when using ADODB.Recordset objects instead of ADODB.Command), first use Server.URLEncode on the data being saved to the database. Then save the information to the database as a 'varchar' data type. When retrieving that value from the database for display on a web page, do a URLDecode on the data first so it will be converted back into regular text. Here is a sample URLDecode function since none is provided by ASP:


' URLDecode "undoes" the built-in Server.URLEncode function
Function URLDecode(str)
	If Len(str) > 0 Then

		dim re
		set re = new RegExp
	
		str = Replace(str, "+", " ")
		
		re.Pattern = "%([0-9a-fA-F]{2})"
		re.Global = True
		URLDecode = re.Replace(str, GetRef("URLDecodeHex"))
		
	Else
		URLDecode = ""
	End If
End Function

' Replacement function for the above
Function URLDecodeHex(match, hex_digits, pos, source)
	URLDecodeHex = Chr("&H" & hex_digits)
End Function

Since Server.URLEncode in ASP takes care of characters like the apostrophe (`), SQL Injection is basically impossible. I used this method presented above because I have a form where a user can type in the body of a web page, and web page bodies will certainly use the apostrophe character, among other things. This is just a catch-all method - granted, it is not the best idea if the information in the database will be used for another purpose besides web pages because it will have to be converted back into text once retrieved from the database by any application.

mellamokb

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-05-23 : 18:15:06

How about just using stored procedures and/or parameters?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-05-23 : 18:33:01

It would be better to decrypt the data on the server side before storing it in the database. Otherwise, searches for particular values, indexes, etc. would be fairly worthless.

CODO ERGO SUM

mellamokb
Starting Member

39 Posts

Posted - 2006-05-24 : 17:11:13

jsmith8858: you are right; I do use stored procedures for other web pages, but I decided to build this one without stored procedures because it is so simplistic in design and I would have virtually hundreds of stored procedures that do little more than spit back a few records of data.

Michael Valentine Jones: I never thought of that

. That would take care of both decoding the information retrieved from the database and limitations to usage only on web pages. And like you say, searches could be done as well [although you could encrypt the search text before querying, but that's an extra pain, too].

The purpose of this idea I presented was to find a way to process user data that could contain any characters while not using stored procedures.

Another neat idea: using server-side encryption and client-side decryption [JavaScript], I was able to open a page on my browser at my high school that is normally blocked

. My web server takes a web page and encrypts the characters and then writes that information onto a web page as a string in a JavaScript variable. Then, on the client machine, the JavaScript decodes the information and writes it onto the page. A few loopholes:

1) Pictures, videos, links, i.e. anything that links externally to another page via relative location doesn't show up. This procedure really only allows one to view the text of a blocked page.

2) Large pages put strain on the server and the client - server must encrypt a large piece of text, and the client must decode that entire text; I don't know if there is a limitation to the size of a JavaScript string, but that could pose another problem.

I tried this procedure on http://microsoft.com [although that's not blocked] and it displayed it pretty well [i.e., both server and client held up to great demands], but it took awhile to load.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-05-24 : 17:32:06

quote:
Originally posted by mellamokb

jsmith8858: you are right; I do use stored procedures for other web pages, but I decided to build this one without stored procedures because it is so simplistic in design and I would have virtually hundreds of stored procedures that do little more than spit back a few records of data...

Seems like a lot of work and extra processing to avoid the work of creating procedures.

There is probably a way to generate those procs with a script, so I doubt that you are really saving yourself much development work.

CODO ERGO SUM

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-05-25 : 03:14:56

quote:
Originally posted by jsmith8858

How about just using stored procedures and/or parameters?

This is your best advice

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources