Secure Code Warrior_.Net Core 資安練習

問題描述

We received reports from a user that they were able to exploit a SQL Injection vulnerability in the transaction search feature of the internet bank solution. They stated that they were able to view transactions that belong to other users by exploiting this flaw, but pointed out that this vulnerability could allow an attacker to do all sorts of nasty stuff to the database, like dropping tables, viewing data from other tables, inserting data etc.

Please try to replicate what the user did, and show that you were able to exploit it by selecting transactions that belong to another account.

指示

1. View your account

Click the View button on the only account that appears in the list of bank accounts. This page lists transactions made on your account.

2. Observe the code

The View Account page offers a search functionality to search your transactions. If you take a look at the code snippet you can see how the database is queried for the transaction search. Take a special notice of how the searchTerm argument is appended to the raw sql query string.

舊程式碼(有資安問題)

using System.Collections.Generic;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using VikingBank.Data;

namespace VikingBank.Services
{
    public class TransactionService
    {
        private VikingBankContext _context;
        
        public TransactionService(VikingBankContext context)
        {
            _context = context;
        }
        
        public IEnumerable<Transaction> SearchTransactions(long accountId, string searchTerm)
        {


            var query = $"SELECT * FROM Transactions T WHERE T.ownerAccountId = {accountId}";
            if (!string.IsNullOrEmpty(searchTerm))
            {
                query += $" AND T.description LIKE '%{searchTerm}%'";
            }

            var transactionQuery = _context.Transactions.FromSqlRaw(query).Include(x => x.OwnerAccount).Include(x => x.ForeignAccount);

            return transactionQuery.ToList();
        }
    }
}

3. Enter a Search term

Try to enter something into the search term input field. Notice in the code snippet how the sql query string changes as you type. Submit a search to see the results, try different variations, e.g. try submitting the first three letters in one of your transactions' description to get a single result and try to submit a search term that doesn't match any description of your transactions.

4. Try to submit a single-quote

Now, submit a single quote ' into the search term input field. Notice the error that appears in the server log. The error message indicates that the SQL query that was executed was badly constructed. This tells us that the search term input was actually interpreted as being part of SQL query syntax instead of just being interpreted as being a pure value being queried. This means that the query is vulnerable to SQL Injection.

Exception when searching transactions, error: SQLite Error 1: 'unrecognized token: "' ) AS "v" LEFT JOIN "Accounts" AS "a" ON "v"."OwnerAccountId" = "a"."Id" LEFT JOIN "Accounts" AS "a0" ON "v"."ForeignAccountId" = "a0"."Id""'.

5. Submit the single-quote without getting the error

Now, submit again a single quote into the search term input field but add a whitespace and two dashes behind it, ' --. Notice that the error is now gone and you actually get some results back. This is because the two dashes are recognized as comment blocks in SQL, meaning that you comment out the rest of the query, i.e. the remaining single quote, hence your query is considered as being valid and will generate some results.

6. Select a transaction that doesn't belong to your account

Since we now know that the transaction search query is vulnerable to SQL Injection, we will exploit it by additionally selecting transactions that belong to another account, utilizing what we've just learned. Submit ' OR T.OwnerAccountId = 1 --, and notice a transaction that doesn't belong to your account appearing in the list. This is because you just manipulated the SQL query to select also transactions from account with Id 1.

===================================================================

字串串接SQL語句錯誤例子

using System;
using System.Data;
using System.Data.SqlClient;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Identity;
using Microsoft.Extensions.Options;
using ToDoList.Settings;

namespace ToDoList.CustomProvider.Repositories
{
    public class UsersRepository : IUsersRepository
    {
        private readonly ConnectionStrings _connectionStrings;

        public UsersRepository(IOptions<ConnectionStrings> connectionStrings)
        {
            _connectionStrings = connectionStrings.Value;
        }

        public async Task<IdentityResult> CreateAsync(ApplicationUser user)
        {
            using (var connection = new SqlConnection(_connectionStrings.ToDoConnectionString))
            {
                connection.Open();
                using (var command = connection.CreateCommand())
                {
                    command.CommandText = @"
                    INSERT INTO [CustomUser]
                        ([Id],
                        [Email],
                        [EmailConfirmed],
                        [PasswordHash],
                        [UserName])
                    VALUES
                        (@id,
                        @email,
                        @emailConfirmed,
                        @passwordHash,
                        @userName)";

                    command.Parameters.Add("@id", SqlDbType.UniqueIdentifier).Value = user.Id;
                    command.Parameters.Add("@email", SqlDbType.NVarChar).Value = user.Email;
                    command.Parameters.Add("@emailConfirmed", SqlDbType.Bit).Value = user.EmailConfirmed;
                    command.Parameters.Add("@passwordHash", SqlDbType.NVarChar).Value = user.PasswordHash;
                    command.Parameters.Add("@userName", SqlDbType.NVarChar).Value = user.UserName;

                    var rows = await command.ExecuteNonQueryAsync();

                    return rows > 0
                        ? IdentityResult.Success
                        : IdentityResult.Failed(
                            new IdentityError {Description = $"Could not insert user {user.Email}."});
                }
            }
        }

        public async Task<IdentityResult> DeleteAsync(ApplicationUser user)
        {
            using (var connection = new SqlConnection(_connectionStrings.ToDoConnectionString))
            {
                connection.Open();
                using (var command = connection.CreateCommand())
                {
                    command.CommandText = @"DELETE FROM [CustomUser] WHERE [Id] = @id";

                    command.Parameters.Add("@id", SqlDbType.UniqueIdentifier).Value = user.Id;

                    var rows = await command.ExecuteNonQueryAsync();

                    return rows > 0
                        ? IdentityResult.Success
                        : IdentityResult.Failed(
                            new IdentityError {Description = $"Could not delete user {user.Email}."});
                }
            }
        }

        public async Task<ApplicationUser> FindByIdAsync(Guid userId)
        {
            ApplicationUser result = null;
            using (var connection = new SqlConnection(_connectionStrings.ToDoConnectionString))
            {
                connection.Open();
                using (var command = connection.CreateCommand())
                {
                    command.CommandText = @"SELECT TOP 1 * FROM [CustomUser] WHERE [Id] = @userId";
                    command.Parameters.Add("@userId", SqlDbType.UniqueIdentifier).Value = userId;

                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            result = new ApplicationUser
                            {
                                Id = Guid.Parse(reader["Id"].ToString()),
                                Email = Convert.ToString(reader["Email"]),
                                EmailConfirmed = Convert.ToBoolean(reader["EmailConfirmed"]),
                                PasswordHash = Convert.ToString(reader["PasswordHash"]),
                                UserName = Convert.ToString(reader["UserName"])
                            };
                        }
                    }
                }
            }

            return result;
        }

        public async Task<ApplicationUser> FindByNameAsync(string userName)
        {
            ApplicationUser result = null;
            using (var connection = new SqlConnection(_connectionStrings.ToDoConnectionString))
            {
                connection.Open();
                using (var command = connection.CreateCommand())
                {
                    command.CommandText = $@"SELECT TOP 1 * FROM [CustomUser] WHERE [UserName] = {userName}";

                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            result = new ApplicationUser
                            {
                                Id = Guid.Parse(reader["Id"].ToString()),
                                Email = Convert.ToString(reader["Email"]),
                                EmailConfirmed = Convert.ToBoolean(reader["EmailConfirmed"]),
                                PasswordHash = Convert.ToString(reader["PasswordHash"]),
                                UserName = Convert.ToString(reader["UserName"])
                            };
                        }
                    }
                }
            }

            return result;
        }
    }
}

解決方案1

(O)方案1程式碼-參數化帶入查詢->唯一正解

public async Task<ApplicationUser> FindByNameAsync(string userName)
        {
            ApplicationUser result = null;
            using (var connection = new SqlConnection(_connectionStrings.ToDoConnectionString))
            {
                connection.Open();
                using (var command = connection.CreateCommand())
                {
                    command.CommandText = @"SELECT TOP 1 * FROM [CustomUser] WHERE [UserName] = @userName";
                    command.Parameters.Add("@userName", SqlDbType.NVarChar).Value = userName;

                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            result = new ApplicationUser
                            {
                                Id = Guid.Parse(reader["Id"].ToString()),
                                Email = Convert.ToString(reader["Email"]),
                                EmailConfirmed = Convert.ToBoolean(reader["EmailConfirmed"]),
                                PasswordHash = Convert.ToString(reader["PasswordHash"]),
                                UserName = Convert.ToString(reader["UserName"])
                            };
                        }
                    }
                }
            }

            return result;
        }

~~方案2程式碼-過濾取代用戶端輸入的危險字元~~

 public async Task<ApplicationUser> FindByNameAsync(string userName)
        {
            ApplicationUser result = null;
            using (var connection = new SqlConnection(_connectionStrings.ToDoConnectionString))
            {
                connection.Open();
                using (var command = connection.CreateCommand())
                {
                    command.CommandText = $@"SELECT TOP 1 * FROM [CustomUser] WHERE [UserName] = {ReplaceSpecialCharacters(userName)}";

                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            result = new ApplicationUser
                            {
                                Id = Guid.Parse(reader["Id"].ToString()),
                                Email = Convert.ToString(reader["Email"]),
                                EmailConfirmed = Convert.ToBoolean(reader["EmailConfirmed"]),
                                PasswordHash = Convert.ToString(reader["PasswordHash"]),
                                UserName = Convert.ToString(reader["UserName"])
                            };
                        }
                    }
                }
            }

            return result;
        }
        
        private string ReplaceSpecialCharacters(string str)
        {
            var regex = new Regex("[*'/\",_&#^@]");
            var result = regex.Replace(str, string.Empty);
            return result;
        }

~~方案3程式碼-針對可能是進行異動竄改的指令進行取代~~

        public async Task<ApplicationUser> FindByNameAsync(string userName)
        {
            ApplicationUser result = null;
            using (var connection = new SqlConnection(_connectionStrings.ToDoConnectionString))
            {
                connection.Open();
                using (var command = connection.CreateCommand())
                {
                    command.CommandText = $@"SELECT TOP 1 * FROM [CustomUser] WHERE [UserName] = {ReplaceSqlCommand(userName)}";

                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            result = new ApplicationUser
                            {
                                Id = Guid.Parse(reader["Id"].ToString()),
                                Email = Convert.ToString(reader["Email"]),
                                EmailConfirmed = Convert.ToBoolean(reader["EmailConfirmed"]),
                                PasswordHash = Convert.ToString(reader["PasswordHash"]),
                                UserName = Convert.ToString(reader["UserName"])
                            };
                        }
                    }
                }
            }

            return result;
        }
        
        private string ReplaceSqlCommand(string str)
        {
            var result = str;
            var sqlCommands = new List<string>
            {
                "UPDATE",
                "DROP",
                "DELETE",
                "ALTER",
                "CREATE",
                "INSERT"
            };
            
            foreach(var command in sqlCommands)
            {
                result = result.Replace(command, "", StringComparison.InvariantCultureIgnoreCase);
            }

            return result;
        }

~~方案4程式碼-照已知的注入下法做取代防範~~

        public async Task<ApplicationUser> FindByNameAsync(string userName)
        {
            ApplicationUser result = null;
            using (var connection = new SqlConnection(_connectionStrings.ToDoConnectionString))
            {
                connection.Open();
                using (var command = connection.CreateCommand())
                {
                    command.CommandText = $@"SELECT TOP 1 * FROM [CustomUser] WHERE [UserName] = {ReplaceExpression(userName)}";

                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            result = new ApplicationUser
                            {
                                Id = Guid.Parse(reader["Id"].ToString()),
                                Email = Convert.ToString(reader["Email"]),
                                EmailConfirmed = Convert.ToBoolean(reader["EmailConfirmed"]),
                                PasswordHash = Convert.ToString(reader["PasswordHash"]),
                                UserName = Convert.ToString(reader["UserName"])
                            };
                        }
                    }
                }
            }

            return result;
        }
        
        
        private string ReplaceExpression(string str)
        {
            return str.Replace("OR '1'='1'", "", StringComparison.InvariantCultureIgnoreCase);
        }