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
