Using SQL Server Stored Procedures To Prevent SQL Injection
page 1 of 6
Published: 02 Mar 2004
Unedited - Community Contributed
Abstract
An often over-looked method to help prevent SQL Injection attacks is the use of parameterized stored procedures. This first article in a two-part series briefly discusses the use of a parameterized stored procedure to help prevent SQL Injection attacks during your web site logon process.
by Randy Dyess
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 101846/ 134

Why use stored procedures

It seems sometimes that every time you turn around you see a new article concerning SQL Injection. Recently when I was brushing up on my ASP to create www.Database-Security.Info, I found out why SQL Injection seems to be a reoccurring problem within ASP pages. Most of the basic examples found on the web and in books during my studies would lead any beginning ASP programmer to create pages just ripe for SQL Injection. It was this problem of overly simplified examples that lead me to create this two-part series to give web developers a chance to look at another way of lessening the threat of SQL Injection; parameterized stored procedures. Keep in mind that both of these articles are using ASP with SQL Server 2000 as the back-end, but you should be able to apply the basic concepts to any back-end platform that has the ability to parameterize its stored procedures. Also keep in mind that many of the articles advocate validating any data received from users before utilizing that data in your processes, you should still perform validation on any user input data even if you are utilizing parameterized stored procedures. This series of articles will explain the basic of using parameterized stored procedures to show you that even if you missed something during your input cleanup, you can still protect yourself by using stored procedures.

Being a DBA at heart and training, it did not take much of a leap for me to move from the embedded SQL found in most ASP pages to stored procedures for my own pages. What I found during this transition was one major benefit: Parameterized stored procedures are a great method to lessen chances of SQL Injection. Yes, you should still validate user input and watch how you use parameterized stored procedures, but if you do these two steps you can almost eliminate the threat from SQL Injection.

In this series of articles I would like to show two examples of how parameterized stored procedures should lessen the threat of SQL Injection. The article will cover the ever-present Logon screen and how it is often used to attack a web site with SQL Injection. The second article will cover a more complex method of utilizing stored procedure parameters to create a dynamic stored procedure for a search screen.


View Entire Article

User Comments

Title: terer'   
Name: fh
Date: 9/7/2012 10:08:04 AM
Comment:
vb
Title: 2012 NFL jerseys   
Name: NIKE NFL jerseys
Date: 5/20/2012 11:42:08 PM
Comment:
[/pre]Cheap NFL,NBA,MLB,NHL
[url=http://www.jersey2shop.com/]Jerseys From China[/url]
[url=http://www.jersey2shop.com/]2012 nike nfl Jerseys[/url]
[url=http://www.jersey2shop.com/]cheap China Jerseys[/url]
[url=http://www.jersey2shop.com/]Sports Jerseys China[/url]
[url=http://www.jersey2shop.com/NFL-Jerseys-c68/]NFL Jerseys China[/url]
[url=http://www.jersey2shop.com/NBA-Jerseys-c77/]NBA Jerseys China[/url]
NHL Jerseys China
[url=http://www.jersey2shop.com/MLB-Jerseys-c94/]MLB Jerseys China[/url]NFL jerseys For Sale online.All Our Jerseys Are Sewn On and Directly From Chinese Jerseys Factory
[/pre]
[pre]We Are Professional China jerseys Wholesaler
[url=http://www.cheapjersey2store.com/]Wholesale cheap jerseys[/url]Cheap mlb jerseys
[url= http://www.cheapjersey2store.com/]2012 mlb all atar jerseys[/url]
[url= http://www.cheapjersey2store.com/ [/url]Cheap China Wholesael[/url]
[url= http://www.cheapjersey2store.com/]Wholesale jerseys From China[/url]
[url=http://www.cheapjersey2store.com/]2012 nike nfl Jerseys[/url]Free Shipping,Cheap Price,7 Days Deliver
[/pre]
[/pre]
We are professional jerseys manufacturer from china,wholesal
sports [url= http://www.cheapjersey2store.com/]Jerseys From China[/url]
[url=http://www.cheapjersey2store.com/NFL-Jerseys-c68]NFL jerseys China[/url]
[url=http://www.cheapjersey2store.com/NHL-Jerseys-c96/]NHL Jerseys China[/url]
[url=http://www.cheapjersey2store.com/NBA-Jerseys-c77/]NBA Jerseys China[/url]
[url=http://www.cheapjersey2store.com/MLB-Jerseys-c94/]MLB Jerseys China[/url]
[url= http://www.cheapjersey2store.com/]China Jerseys[/url],Free Shipping
[/pre]
[/pre]
We are professional jerseys manufacturer from china,wholesal
sports [url= http://www.jerseycaptain.com/]cheap jerseys sale online [/url]
[url= http://www.jerseycaptain.com/]2012 nike nfl Jerseys[/url]
[url=http://www.jerseycaptain.com/NFL-Jerseys-c68]cheap NFL jerseys China[/url]
[url=http://www.jerseycaptain.com/NHL-Jerseys-c96/]NHL Jerseys C
Title: Mr   
Name: David MD
Date: 11/24/2009 3:35:03 AM
Comment:
So where exactly do I write these stored procedures? in the SQL or asp.net?
Title: God   
Name: Joe
Date: 5/14/2008 3:09:15 PM
Comment:
' or 1=1 --
Title: Mr.   
Name: CSC
Date: 5/17/2007 3:21:16 PM
Comment:
Good article, should then cover some basic validation. For those still wanting to use passed in SQL lest we forget that stored procs in SQL are pre compiled and the passed in string does not have to be parsed by the Query Analyzer which then has to build an execution plan. For a stored proc the execution plan is already compiled and in cache. Also a great follow up may be using OUTPUT parameters whenever returning only one row of data and how this can be up to 10x or more faster than using a recordset object for a single row.
Title: Mr.   
Name: Nikhil
Date: 3/27/2007 9:03:38 PM
Comment:
really good. made very easy and comprehensive.
Title: SQL Injection works without stored procedures too   
Name: Adi
Date: 7/18/2006 3:45:02 PM
Comment:
good basic aricle
Title: stored procedure   
Name: prakash
Date: 5/21/2006 12:54:48 AM
Comment:
please its not sufficient.u must go through details from the begining.
Title: Setting Paramters for Stored Procedures   
Name: Dirty Sanchez
Date: 9/15/2004 2:50:17 PM
Comment:
In the line:
SET objParam = objConn.CreateParameter("@strLogin",200, 1, 50)
What are the 200, 1, 50 doing?

Thanks,

DS
Title: SQL Injection works without stored procedures too   
Name: Sid
Date: 6/15/2004 10:38:35 AM
Comment:
It may be worthwhile to note that you do not have to use a stored procedure in order to use parameters. Here's an example:

Set cmd = Server.CreateObject("ADODB.Command")
Set rs = Server.CreateObject("ADODB.Recordset")
sql = "SELECT pkUserId FROM Users WHERE email = @email AND password = @password"

cmd.CommandText = sql
Set param = cmd.CreateParameter("@email", adVarChar, adParamInput, 255, Request.Form("txtEmail"))
cmd.Parameters.Append param
Set param = cmd.CreateParameter("@password", adVarChar, adParamInput, 255, Request.Form("txtPassword"))
cmd.Parameters.Append param
cmd.ActiveConnection = con

Set rs = cmd.Execute






Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2014 ASPAlliance.com  |  Page Processed at 9/18/2014 1:41:59 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search