CODEWITHSUNDEEP
sqlsql-serversql-injection
Amol Bavannavar
Amol Bavannavar

Reputation: 2062

How to solve Sql Injection issue?

When I try to create new login with Login Name as 'DROP TABLE Test --' then it will drop's the Test Table.

    IF OBJECT_ID ('Test') IS NULL
       CREATE TABLE [dbo].[test](
                                [Id] [int] NOT NULL
                                ) ON [PRIMARY]
    GO

    DECLARE @SQL nvarchar(max)
    DECLARE @UserCode nvarchar(50)

    SELECT @UserCode = N'''DROP TABLE Test --';

    SET @SQL = N'CREATE LOGIN '+ QUOTENAME(@UserCode) +' WITH PASSWORD = ''' + UPPER(@UserCode)  + '''';
    EXECUTE(@SQL);

Current Result : Creates New Login as well as Drop's whole Test Table.

Expected Result : Only Create Login.

Can you please help me to solve this issue ?

UPDATE :

I have fixed this issue using QUOTENAME(UPPER(@UserCode), '''')...

This thread helped me to solve this issue.

Upvotes: 0

Views: 166

Answers (2)

MAC
MAC

Reputation: 686

See this example...

 exec sp_prepexec @p1 output,N'@P1 varchar(22)',N'Select City, State from dbo.ZipCodes where zipcode = @P1','''; Drop Table Test; --'

Since it received the malicious code as a variable, the server would simply look for the value in the table and return a blank result sets. The malicious string is never executed, so the test table is never dropped.

Since I can't comment @ signs so I show it here. See this link for details. https://www.simple-talk.com/sql/learn-sql-server/sql-injection-defense-in-depth/

I hope I helped a little.

Upvotes: 1

tpcstld
tpcstld

Reputation: 39

Depending on which version/type of SQL, you could use tools such as prepared statements, string sanitation, or stored procedures.

Upvotes: 1

Related Questions