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