My ASP.NET Website is Attacked With SQL Injection

Question

Hacker reached my database User list and other tables.

First of all, I use parameterized command in all of the transactions by using

command.Parameters.Add("@Parameter1", SqlDbType.NVarChar).Value

All transactions are stored procedures.

I am inserting every single site navigation into database. Particular database table as follows;

ID int (PK)
UserID int (null)
URL nvarchar(500)
IPAddress nvarchar(25)
CreatedAt datetime

Project gets UserID information from the code is session opened or not.

CreatedAt is DateTime.UtcNow.

IPAddress code as follows:

public static string GetIPAddress(HttpContext context)
{
    string ipAddress = context.Request.ServerVariables["HTTP_X_FORWARDED_FOR"];

    if (!string.IsNullOrEmpty(ipAddress))
    {
        string[] addresses = ipAddress.Split(',');
        if (addresses.Length != 0)
            return addresses[0];
    }

    return context.Request.ServerVariables["HTTP_CLIENT_IP"] ?? context.Request.ServerVariables["REMOTE_ADDR"];
}

However URL is filled from Website Current URL with all query string. (Request.RawUrl)

Normally, when the user visits the site, Log is inserted into database as I stated above. Following records are inserted normally. Example data looks like this:

ID      UserID    URL                        IPAddress      CreatedAt
1        NULL     /User                      1.22.33.444    2019-12-12 16:22:33.441
2        NULL     /User/MyOrders             1.22.33.444    2019-12-12 16:24:33.441
3        NULL     /User?utm_source=email     1.22.33.444    2019-12-12 16:29:33.441

The hacker somehow inserted a record into database as follows:

ID      UserID    URL                        IPAddress                     CreatedAt
4        NULL     /User                      (select(0)from(select(sle     2019-12-12 17:22:33.441
5        NULL     /User/MyOrders             -1; waitfor delay '0:0:9'     2019-12-12 17:24:33.441
6        NULL     /User?utm_source=email     prvNA0R6'; waitfor delay      2019-12-12 17:29:33.441
7        NULL     /User?utm_source=email     -1' OR 2+198-198-1=0+0+0+     2019-12-12 17:29:33.441

As you can see IPAddress column is the SQL Query attack. IPAddress field is restricted to 25 character length. Following SQL query text is truncated by the SQL.

In my opinion, hacker gets database records by using SQL Injection by changing URL or IPAddress as SQL scripts.

Any idea how hacker reached my database and how to avoid attack from now on?

EDIT

Stored procedure is as follows:

create procedure SP_InsertLogNavigation
    @URL nvarchar(150),
    @UserID int,
    @IPAddress nvarchar(25),
    @CreatedAt datetime
as
    insert into LogNavigation (URL, UserID, IPAddress, CreatedAt)
    values (@URL, @UserID, @IPAddress, @CreatedAt)

Usage of the stored procedure is as follows:

public bool Save(LogNavigation logNavigation)
{
    int affectedRows = 0;

    InitializeSqlFields("SP_InsertLogNavigation");

    command.Parameters.Add("@URL", SqlDbType.NVarChar).Value = logNavigation.URL;
    command.Parameters.Add("@UserID", SqlDbType.Int).Value = Validation.IsNull(logNavigation.UserID);
    command.Parameters.Add("@IPAddress", SqlDbType.NVarChar).Value = logNavigation.IPAddress;
    command.Parameters.Add("@CreatedAt", SqlDbType.DateTime).Value = logNavigation.CreatedAt;

    try
    {
        Connect();

        affectedRows = command.ExecuteNonQuery();
    }
    catch (SqlException)
    {
    }
    finally
    {
        Disconnect();
    }

    return affectedRows != 0;
}

Answer 1

So I would assert that you actually have not succumbed to the SQL injection attack. If you are using only parameterised queries then the attacker has tried to gain access but failed.

However, the reason why your table has their attack attempts lodged is to do with these lines of code:

string ipAddress = context.Request.ServerVariables["HTTP_X_FORWARDED_FOR"];

return context.Request.ServerVariables["HTTP_CLIENT_IP"] ?? context.Request.ServerVariables["REMOTE_ADDR"];

You must understand that the client has almost total control over the headers submitted to your website. The attacker can modify the headers to be whichever values they desire.

These parameters are supplied by the client in their request:

HTTP_X_FORWARDED_FOR
REMOTE_ADDR
HTTP_CLIENT_IP

In your case, the attacker has provided spoofed headers that contain SQL Injection code, which you have faithfully placed into your database in the IP Address column.

Edit following OP query in comments

OP asked:

Excellent, but I have only one question. How she/he passed more than 25 characters to the my server side

The request headers have no specified size limit, although their are practical limits applied by various implementations (i.e. 8Kb in Apache). The client can send a request header of any length up to what is allowed by your website host software.

However, as your SP is configured with a parameter whose maximum length is 25 characters, the overflowing text is being truncated when persisted to the database.