.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Articles >> DataBase >> Post New Resource Bookmark and Share   

 Subscribe to Articles

SQL Injection in Stored Procedure & Preventing from the same

Posted By:Dhiraj Ranka       Posted Date: October 08, 2010    Points: 50    Category: DataBase    URL: http://www.dotnetspark.com  

This article will show that how one can achieve SQL Injection in Stored Procedure & Preventing from the same
 

Following is the small example of creating a stored procedure.
====================================================================
CREATE PROC sp_login (@loginid nvarchar(25),@password nvarchar(25))
AS
DECLARE @SQLString VARCHAR(500)
DECLARE @loginid VARCHAR(64)
DECLARE @password VARCHAR(64)

/* Build the SQL string once.*/

SET @SQLString = 'SELECT * from cust_users WHERE login_id = '+ ''''+@loginid+'''' + 'AND password = '+ ''''+@password+''''

EXECUTE sp_executesql @SQLString
====================================================================
Your ASP.NET Code would look like this:
oCmd.CommandText = "sp_login";
oCmd.CommandType = CommandType.StoredProcedure;
oCmd.Parameters.Add( "@loginId", strUserName);
oCmd.Paramerters.Add( "@password", strPassword);
oCon.Open();
string result = (string)oCmd.ExecuteScalar();
oCon.Close();
====================================================================
If the user input is as follows:
loginId = ' OR 1=1 -- password = junk
SQL injection will not work and ASP.NET will throw an exception
"Unclosed quotation mark after the character string ' OR 1=1 -- and password=junk'. Incorrect syntax near ' OR 1=1 -- and password=junk'."
In this case you can use
loginID = '' OR 1=1--
password = junk
Two single quotations are used to complete where clause with null condition and OR is used to make the condition true always.

If you use sp_executesql this will definitely leads to the SQL Injection.
See more on this http://msdn.microsoft.com/en-us/library/ms188001.aspx

Solution :

Instead one should use the same stored procedure which he has created, for passing parameters. exec sp_login 'param1', 'param2'
param1 - would be loginID
param2 - would be password
And you are stored procedure would look like this i.e. with out sp_executesql

====================================================================
CREATE PROC sp_login

@loginid VARCHAR(64)
@password VARCHAR(64)

AS

BEGIN
SELECT * FROM cust_users WHERE loginid=@loginid AND password=@password
END
==================================================================== This will avoid the possible SQL Injection
 Subscribe to Articles

     

Further Readings:

Responses
Author: Ramesh P         Company URL: http://www.dotnetspark.com
Posted Date: October 19, 2010

Sql injection is a form of attack on a database-driven web site in which the attacker executes unauthorized SQL commands by taking advantage of insecure code on a system connected to the Internet, bypassing the firewall. SQL injectionn attacks are used to steal information from a database.

SQL injection attacks typically are easy to avoid by ensuring that a system has strong input validation.


Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend