Using SQL Server Stored Procedures To Prevent SQL Injection
page 2 of 6
by Randy Dyess
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 43799/ 54

Basic Logon Screen

In order to order to see how we can use a parameterized stored procedure during the login process, we need to set up our basic logon page. In this article I will only show the screen shot of the logon page, if you need the code for the page please follow this link to my web site and copy the code from there; http://www.Database-Security.Info/sqlinjectioncode_pt1.asp.

Standard Logon Page
http://www.database-security.info/Images/sqlinjection_pt1_image001.jpg

Most web develops will take the input from the two input boxes and concatenate those two inputs into a SQL string to be utilized by their code.

strSQL = "SELECT strusername, strpassword FROM tUser WHERE strusername = '" + username + "' AND strpassword = '" + password + "'"

set objRS = objDB.execute(strSQL)

if (objRS.EOF) then
'this is where you would do normal processing for invalid users
end if

'this is where you would do normal processing for valid users

If you use this typical method of logging in your users, you have left yourself open to SQL Injection. Yes, you could validate each of the user inputs to scrub the data for SQL

Injection, but validation has its own set of problems and sometimes requires a complex set of logic code to determine if the input is "bad" or "good".

You can greatly reduce the threat of SQL Injection in the case of the logon screen and in the case of missed threats during your input scrubbing by not creating the SQL statement in ASP and passing the user inputs as parameters to a stored procedure.

'Execute stored procedure
Set objConn = Server.CreateObject("ADODB.Command")
objConn.ActiveConnection = cn
objConn.CommandText = "spTestLogon"
objConn.CommandType = 4

'Parameter for stored procedure
SET objParam = objConn.CreateParameter("@strLogin",200, 1, 50)
objConn.Parameters.Append objParam
objConn.Parameters("@strLogin") = username

'Parameter for stored procedure
SET objParam = objConn.CreateParameter("@strPassWord",200, 1, 50)
objConn.Parameters.Append objParam
objConn.Parameters("@strPassWord") = password

Set objrso = objConn.Execute

If NOT objrso.EOF then
Response.Write "Access Granted"
Else
Response.Write "Access Denied"
End If


 

 


View Entire Article

User Comments

Title: terer'   
Name: fh
Date: 2012-09-07 10:08:04 AM
Comment:
vb
Title: 2012 NFL jerseys   
Name: NIKE NFL jerseys
Date: 2012-05-20 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: 2009-11-24 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: 2008-05-14 3:09:15 PM
Comment:
' or 1=1 --
Title: Mr.   
Name: CSC
Date: 2007-05-17 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: 2007-03-27 9:03:38 PM
Comment:
really good. made very easy and comprehensive.
Title: SQL Injection works without stored procedures too   
Name: Adi
Date: 2006-07-18 3:45:02 PM
Comment:
good basic aricle
Title: stored procedure   
Name: prakash
Date: 2006-05-21 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: 2004-09-15 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: 2004-06-15 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-2024 ASPAlliance.com  |  Page Processed at 2024-03-19 3:51:55 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search