Secure Code Warrior_.Net Core 資安練習_SQL Injection防範(一)

-
 

問題描述

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.


舊程式碼(有資安問題)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
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語句錯誤例子



  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
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程式碼-參數化帶入查詢->唯一正解

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
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程式碼-過濾取代用戶端輸入的危險字元

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
 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程式碼-針對可能是進行異動竄改的指令進行取代

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
        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程式碼-照已知的注入下法做取代防範


 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
        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);
        }














留言

張貼留言

這個網誌中的熱門文章

何謂淨重(Net Weight)、皮重(Tare Weight)與毛重(Gross Weight)

-
圖片
當談到貨物的重量時，往往會分成所謂的淨重(Net Weight)與毛重(Gross Weight)。 會有以下公式關係存在 淨重=毛重-皮重(包裝物的重量) 毛重=淨重+皮重 淨重( Net Weight 縮寫為N.W.) 指物品本身的重量，不包括容器或包裝材料的重量。 凈重是國際貿易中最常見的計重辦法。 例如，如果運輸原材料，原材料的重量就是所運輸貨物的淨重。包裝原材料的容器或包裝不計入淨重。同樣地，當我們考慮一罐豆子時，罐子內所含物品的重量就算作淨重。因此，淨重將包括豆子、罐頭里的任何液體，以及保存豆子所含的物品。 大多數 以重量為計價的貨物買賣 ，採用此計價方法。 若合同中並未明確規範以何種方式計量和計價時，按慣例以凈重計價。 毛重( Gross Weight 縮寫G.W.) 貨物連同它的包裝的重量，是你托運的所有物品的總重量。 皮重(Tare Weight) 指商品外包裝材料的重量(即運輸包裝的重量)，不包括內包裝材料和襯墊物的重量。 比方一個空的貨櫃、容器、或任何包裝材料的重量。 Net Net Weight純淨重 (指從Net Weight中扣除內包裝後的重量) 進出口商品為了因應運輸及消費需求, 一般都有內包裝與外包裝。 一般在買賣合同與信用狀中,通常只要求於裝箱單上註明Gross Weight和Net Weight即可, 但有些L/C 要求 Net Net Weight,大多是海關徵收從量稅時的依據 運輸重量對於航運業或者航空運輸等各自都通用這些術語 為何特別在意重量? 主要就在於會影響像是傳輸安全乘載限制。 就運輸層面(不同方式的運輸) 航空運輸：對機長而言，毛重包括皮重、產品淨重以及飛機、燃料、乘客和機組人員的重量。 公路/鐵路運輸:對於火車車長而言，毛重包括產品淨重、皮重和運輸車輛的重量。 水路航運運輸：對船長而言，毛重就是淨重和皮重之和。 就公司ERP成本角度 成本一般都會採用 "淨重" 主要是因為若用毛重，可能會有輔材不可控因素存在 比方一個 5cm 固定塑膠件，今天用一個 10cm 紙箱測重，明天用一個 100cm 紙箱測重，那可能就容易導致ERP資料比較會失真。 就物流層面來探討 通常還會有一名詞即所謂「實際重量」(Actual Weight) 根據稱重(過磅)所得到的重量，實際重量分為實際毛重(Gross Weig
閱讀完整內容

Architecture(架構) 和 Framework(框架) 有何不同?_軟體設計前的事前規劃的藍圖概念

-
圖片
一個應用程式(軟體) 不管是  互動拍照 也好、 投影也好、車牌偵測... 舞台演唱會後方電視牆的互動應用程序、 粒子特效、3D或2D遊戲  等等 開發者通常都需要在一開始做規劃 ㄟ  假設我今天要寫 跟影像有關 可能需要偵測、可能需要提取前景、可能需要偵測圓形 之類的 我有  emgucv 、AForge.Net、 opencv 等等可以用 但是 假設我今天要做的應用程序是需要有視窗介面的~~ emgucv 跟 C# windows form 媒合性 就很高 而且直接在 visual studio 做開發 opencv 我可能還需要依賴 Qt 之類等等  framework 假設我今天是要寫一個 演唱會電視牆互動粒子特效 可能就會挑   openframeworks  、 processing 、kinect 來寫互動 Achitecture  --->全稱 :  Software Architecture   軟體架構 Achitecture   由好多 framework 組成 軟體架構（Software Architecture）是一種軟體在開發前的設計藍圖， 用來告訴軟體的結構，功能，介面，用法，與其他系統的構連以及資料交換等等規範， 但它並沒有叫你要用什麼方式實作， 因此軟體架構通常會產生文件，圖樣，原型以及規格等， 就是沒有可用的程式碼，因為那不是軟體架構應該有的東西， 就像蓋房子時是給你藍圖，而不是一幢蓋好的房子。 軟體框架（Framework):是一個已經成形的方法，而且有程式碼實體 （例如鋼構工法也是要有鋼材才能做），並且會告訴你要如何使用它 （即 Framework Documentation，MSDN Library 即為一最佳例子）
閱讀完整內容

(2021年度)駕訓學科筆試準備題庫歸納分析_法規是非題

-
圖片
  若是出現 「必須」、「必需」、「...必...」、「應由」、「應先」、「應...」 則很高機率是圈 是非通常只要是如下字眼 「不必」+動詞 、「不需」「不須」、「可不」、「可以不」、「可立即」 有出現或符合的都選叉 (X)1.違規記點又不登記在駕照上，所以可以 不必理會 而任意違反交通規則。 (X)2.發生重大交通事故，為迅速恢復交通，清除現場，可 不必等待 肇事處理機關之指示，即可進行。 (X)3.道路駕駛練習，行車前 不必實施 車輛安全檢查，因為那是駕駛教練的工作。 (X)4.開車前， 不必檢查 機件安全，但在行駛中要隨時注意儀錶功能顯示，確保行車安全。 (X)5.煞車油若經常低於"MIN"線，只要添加即可， 不必到修理廠檢修 。 (X)6.冷卻系統只要副水箱在滿水位，主水箱 不必檢查 也不會有問題。 (X)7.引擎冷卻水內含有防凍液或防銹劑，可 不必更換 。 (X)8.儀錶板上的「燃油油量錶」之指針高度，已經降低至「E」點，橘色的「低油料警示燈」點亮時， 不必急著加油 ，到達目的地後再行補充燃油即可。 (X)9.有安全氣囊裝備的汽車， 不必繫 安全帶。 (X)10.煞車時有異音，是正常的情況， 不必檢修 。 (X)11.汽車定期檢驗 不必檢驗 車輛故障標誌，所以隨車不一定要帶車輛故障標誌。 (X)12.開車時 不必理會 義交之指揮，一切以號誌為主。 (X)13.汽車經過隧道，因隧道內燈光明亮視線良好，為省電可 不必開亮 頭燈。 (X)14.駕駛人只求技術優良，對遵守交通規則 不必太重視 。 (X)15.在開車前， 不必檢查 機件，以免耽誤行車時間。 (X)16.遇幼童專用車、校車、身心障礙者用特製車或教練車， 不必禮讓 。 (X)17.行車 不必攜帶 隨車工具。 (X)18.行車起駛前進， 不必顯示 方向燈。 (X)19.汽車通過鐵路平交道，前後兩車間， 不必保持 距離。 (X)20.夜間將車輛停放在無燈光設備及照明不清之道路時，亦 不必顯示 停車燈光或反光標識。 (X)21.在夜間或有霧靄、雨雪、風沙及晝晦時，把車輛停在路旁， 不必顯示 停車燈光或反光標識。 (X)22.汽車駕駛人，僅需具有優良駕駛技術， 不需 具備急救的常識和技能。 (X)23.汽車車身、引擎、底盤、電系等重要設備變更或調換時， 不需 向公路主管機關申請臨時檢驗。
閱讀完整內容