Best Answer · By JanBask MS SQL Server Expert
Answered on Jan 13, 2020
SQL injection is a way by which SQL statements are manipulated by hackers to steal organization's critical data. Proper coding of the web application should be done to avoid SQL commands injection. Programmers should not use the user inputs directly, proper validations should be done before it is used in the system.
Below is the case which a hacker can do on login screen, in password this can be input:
Username =alex
Password: a’ or 1=1 --
And developer has written below statement:
Declare @user nvarchar(30) ,@password nvarchar(50)
Select 1 from users where user_id= @user and password = @password
Then the statement will become:
Select 1 from users where user_id= ‘alex’ and password = ‘a’ or 1=1 --‘
In case the weather the password match or not the statement 1=1 will be executed and hence user will able to login into the portal
Below is the sample of one procedure to avoid SQL injection:
CREATE PROCEDURE usp_user_login
(@P_user nvarchar(30) ,@p_password nvarchar(50) )
AS
BEGIN
DECLARE @V_sqlcmd NVARCHAR(MAX),@v_params NVARCHAR(MAX)
SET @V_sqlcmd = N'SELECT * FROM users WHERE user_id
= @P_user and password = @p_password;'
SET @v_params = N'@P_user nvarchar(30) ,@p_password nvarchar(50)';
EXECUTE sp_executesql @V_sqlcmd, @v_params,@P_user,@p_password
end
This will not get the data:
exec usp_user_login 'alex','''a'' or 1=1 --'
