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

Dhiraj Ranka

Dhiraj Ranka is currently working as Senior Security Analyst at Network Intelligence, Mumbai for more than four years. He is additionally involved in intensive research and Web-based applications testing, intended primarily for executing vulnerability assessment projects and delivering trainings.

Be the first to comment

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.