| Version 20 (modified by , 3 weeks ago) ( diff ) |
|---|
Други теми
Безбедност
Спречување на SQL Injection
Користиме Entity Framework Core (ORM). EF Core автоматски ги параметризира сите LINQ прашања (queries). Ова спречува напаѓачите да вметнат злонамерни SQL команди преку полињата за внес.
- EF Core го третира username како параметар (@p0), а не како извршлив код.
- Ова спречува SQL Injection напади (на пр., ' OR 1=1 --).
public async Task<User> AuthenticateAsync(string username, string password)
{
var user = await _context.Users
.FirstOrDefaultAsync(u => u.Username == username && u.IsActive);
if (user == null)
return null;
bool isHashed = user.Password.StartsWith("$2") && user.Password.Length == 60;
if (isHashed)
{
if (BCrypt.Net.BCrypt.Verify(password, user.Password))
return user;
}
else
{
if (user.Password == password)
{
user.Password = BCrypt.Net.BCrypt.HashPassword(password);
await _context.SaveChangesAsync();
return user;
}
}
return null;
}
Хеширање на лозинки (Заштита на податоци)
Лозинките се зачувуваат како хеш вредности со користење на алгоритмот BCrypt, а не како обичен текст.
public async Task<bool> CreateUserAsync(User user, string password)
{
using var transaction = await _context.Database.BeginTransactionAsync();
try
{
user.Password = BCrypt.Net.BCrypt.HashPassword(password);
_context.Users.Add(user);
await _context.SaveChangesAsync();
await transaction.CommitAsync();
return true;
}
catch
{
await transaction.RollbackAsync();
return false;
}
}
Безбедност на Database Context (Row-Level идентификација)
Го пренесуваме идентитетот на моментално најавениот корисник од Application Layer до Database Layer (PostgreSQL) користејќи Session Variables. Ова и овозможува на базата на податоци да знае кој ја извршува операцијата.
public override async Task<int> SaveChangesAsync(CancellationToken cancellationToken = default)
{
var username = _httpContextAccessor.HttpContext?.User?.Identity?.Name ?? "system";
await Database.ExecuteSqlRawAsync("SELECT set_config('app.current_user', {0}, false)", new[] { username }, cancellationToken);
return await base.SaveChangesAsync(cancellationToken);
}
Авторизација (Role-Based Access Control)
Го ограничуваме пристапот до Controllers и Actions со користење на атрибутот [Authorize] . Само автентицирани корисници со валидни cookies можат да пристапат до овие ресурси.
- Овој атрибут осигурува дека само најавени корисници можат да пристапат до било која акција во овој контролер.
- Неавтентицираните барања се пренасочуваат кон страницата за најава (Login page).
using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Mvc;
namespace StockMaster.Controllers
{
[Authorize]
public class ReportController : Controller
{
private readonly IReportService _reportService;
public ReportController(IReportService reportService)
{
_reportService = reportService;
}
public IActionResult Index()
{
return View();
}
// ... други акции
}
}
Безбедност на база на податоци базирана на логика (Triggers)
Користиме Database Triggers за да спроведеме безбедносни правила што не можат да бидат заобиколени од апликацијата. Поточно, спречуваме корисник да ја избрише сопствената account за да обезбедиме стабилност на системот и следење на активности.
CREATE OR REPLACE FUNCTION stock_management.prevent_self_delete()
RETURNS TRIGGER AS $$
BEGIN
IF OLD.username = current_setting('app.current_user', true) THEN
RAISE EXCEPTION 'You cannot delete your own account.';
END IF;
RETURN OLD;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE TRIGGER trg_prevent_self_delete
BEFORE DELETE ON stock_management.users
FOR EACH ROW EXECUTE FUNCTION stock_management.prevent_self_delete();
Пеформанси - Индекси
За да се зголеми перформансата на базата на податоци, се применуваат стратегии за индексирање и се анализира споредбата на перформансите во состојби со индекс и без индекс. Тестовите се извршени со користење на командата EXPLAIN ANALYZE за реални мерења во реално време.
Сценарио 1: Тековен залиха по складиште
Цел: Прикажува вкупниот број производи и вредност на залихата по складишта.
SELECT
w.warehouse_id,
w.name AS warehouse_name,
SUM(ws.quantity_on_hand) AS total_units,
SUM(ws.quantity_on_hand * p.unit_price) AS total_stock_value
FROM warehouse_stock ws
JOIN warehouse w ON ws.warehouse_id = w.warehouse_id
JOIN product p ON ws.product_id = p.product_id
GROUP BY w.warehouse_id, w.name
ORDER BY total_stock_value DESC;
1.1. Без индекс
Кога се поврзува табелата warehouse_stock со табелата product преку колоната product_id (JOIN), ако нема индекс, базата на податоци ќе ги скенира сите редови.
EXPLAIN ANALYZE SELECT w.name, SUM(ws.quantity_on_hand), SUM(ws.quantity_on_hand * p.unit_price) FROM stock_management.warehouse_stock ws JOIN stock_management.warehouse w ON ws.warehouse_id = w.warehouse_id JOIN stock_management.product p ON ws.product_id = p.product_id GROUP BY w.warehouse_id, w.name;
"HashAggregate (cost=705.70..706.95 rows=100 width=262) (actual time=102.460..102.484 rows=3.00 loops=1)" " Group Key: w.warehouse_id" " Batches: 1 Memory Usage: 32kB" " Buffers: shared hit=164 dirtied=1" " -> Hash Join (cost=191.75..518.20 rows=15000 width=232) (actual time=4.965..78.081 rows=15000.00 loops=1)" " Hash Cond: (ws.product_id = p.product_id)" " Buffers: shared hit=164 dirtied=1" " -> Hash Join (cost=12.25..299.29 rows=15000 width=230) (actual time=0.282..37.808 rows=15000.00 loops=1)" " Hash Cond: (ws.warehouse_id = w.warehouse_id)" " Buffers: shared hit=97 dirtied=1" " -> Seq Scan on warehouse_stock ws (cost=0.00..246.00 rows=15000 width=12) (actual time=0.103..20.064 rows=15000.00 loops=1)" " Buffers: shared hit=96" " -> Hash (cost=11.00..11.00 rows=100 width=222) (actual time=0.068..0.069 rows=3.00 loops=1)" " Buckets: 1024 Batches: 1 Memory Usage: 9kB" " Buffers: shared hit=1 dirtied=1" " -> Seq Scan on warehouse w (cost=0.00..11.00 rows=100 width=222) (actual time=0.029..0.032 rows=3.00 loops=1)" " Buffers: shared hit=1 dirtied=1" " -> Hash (cost=117.00..117.00 rows=5000 width=10) (actual time=4.433..4.444 rows=5000.00 loops=1)" " Buckets: 8192 Batches: 1 Memory Usage: 279kB" " Buffers: shared hit=67" " -> Seq Scan on product p (cost=0.00..117.00 rows=5000 width=10) (actual time=0.044..1.930 rows=5000.00 loops=1)" " Buffers: shared hit=67" "Planning:" " Buffers: shared hit=86" "Planning Time: 26.724 ms" "Execution Time: 103.080 ms"
Времетраење: 103.080 ms
Анализа: Многу бавно.
1.2. Со индекс
За пребарувањето по product_id во табелата warehouse_stock да биде побрзо
Применет индекс:
CREATE INDEX idx_wh_stock_product_id ON stock_management.warehouse_stock(product_id);
"HashAggregate (cost=705.70..706.95 rows=100 width=262) (actual time=29.541..29.548 rows=3.00 loops=1)" " Group Key: w.warehouse_id" " Batches: 1 Memory Usage: 32kB" " Buffers: shared hit=164" " -> Hash Join (cost=191.75..518.20 rows=15000 width=232) (actual time=2.793..18.549 rows=15000.00 loops=1)" " Hash Cond: (ws.product_id = p.product_id)" " Buffers: shared hit=164" " -> Hash Join (cost=12.25..299.29 rows=15000 width=230) (actual time=0.060..9.059 rows=15000.00 loops=1)" " Hash Cond: (ws.warehouse_id = w.warehouse_id)" " Buffers: shared hit=97" " -> Seq Scan on warehouse_stock ws (cost=0.00..246.00 rows=15000 width=12) (actual time=0.025..1.711 rows=15000.00 loops=1)" " Buffers: shared hit=96" " -> Hash (cost=11.00..11.00 rows=100 width=222) (actual time=0.019..0.021 rows=3.00 loops=1)" " Buckets: 1024 Batches: 1 Memory Usage: 9kB" " Buffers: shared hit=1" " -> Seq Scan on warehouse w (cost=0.00..11.00 rows=100 width=222) (actual time=0.011..0.013 rows=3.00 loops=1)" " Buffers: shared hit=1" " -> Hash (cost=117.00..117.00 rows=5000 width=10) (actual time=2.710..2.710 rows=5000.00 loops=1)" " Buckets: 8192 Batches: 1 Memory Usage: 279kB" " Buffers: shared hit=67" " -> Seq Scan on product p (cost=0.00..117.00 rows=5000 width=10) (actual time=0.010..1.133 rows=5000.00 loops=1)" " Buffers: shared hit=67" "Planning:" " Buffers: shared hit=12" "Planning Time: 0.761 ms" "Execution Time: 29.767 ms"
Времетраење: 29.767 ms
Анализа: Многу по брзо.
Сценарио 2: Продажба по категорија
Цел: Цел: Прикажува кои категории носат најголем приход.
SELECT
c.category_id,
c.name AS category_name,
SUM(si.quantity * si.unit_price_at_sale) AS total_category_revenue
FROM sale_item si
JOIN product p ON si.product_id = p.product_id
JOIN category c ON p.category_id = c.category_id
GROUP BY c.category_id, c.name
ORDER BY total_category_revenue DESC;
1.1. Без индекс
Се спојува табелата sale_item (многу голема) со табелата product, а потоа се спојува и со табелата category.
EXPLAIN ANALYZE SELECT c.name, SUM(si.quantity * si.unit_price_at_sale) FROM stock_management.sale_item si JOIN stock_management.product p ON si.product_id = p.product_id JOIN stock_management.category c ON p.category_id = c.category_id GROUP BY c.category_id, c.name;
"Finalize GroupAggregate (cost=7589.33..7600.28 rows=50 width=47) (actual time=624.660..643.857 rows=50.00 loops=1)" " Group Key: c.category_id" " Buffers: shared hit=2054" " -> Gather Merge (cost=7589.33..7599.02 rows=85 width=47) (actual time=624.642..643.771 rows=100.00 loops=1)" " Workers Planned: 1" " Workers Launched: 1" " Buffers: shared hit=2054" " -> Sort (cost=6589.32..6589.45 rows=50 width=47) (actual time=507.965..507.972 rows=50.00 loops=2)" " Sort Key: c.category_id" " Sort Method: quicksort Memory: 30kB" " Buffers: shared hit=2054" " Worker 0: Sort Method: quicksort Memory: 30kB" " -> Partial HashAggregate (cost=6587.29..6587.91 rows=50 width=47) (actual time=507.781..507.904 rows=50.00 loops=2)" " Group Key: c.category_id" " Batches: 1 Memory Usage: 40kB" " Buffers: shared hit=2047" " Worker 0: Batches: 1 Memory Usage: 40kB" " -> Hash Join (cost=181.62..4822.93 rows=176436 width=25) (actual time=5.394..368.931 rows=149971.00 loops=2)" " Hash Cond: (p.category_id = c.category_id)" " Buffers: shared hit=2047" " -> Hash Join (cost=179.50..4318.40 rows=176436 width=14) (actual time=4.975..314.892 rows=149971.00 loops=2)" " Hash Cond: (si.product_id = p.product_id)" " Buffers: shared hit=2045" " -> Parallel Seq Scan on sale_item si (cost=0.00..3675.36 rows=176436 width=14) (actual time=0.704..156.201 rows=149971.00 loops=2)" " Buffers: shared hit=1911" " -> Hash (cost=117.00..117.00 rows=5000 width=8) (actual time=4.200..4.200 rows=5000.00 loops=2)" " Buckets: 8192 Batches: 1 Memory Usage: 260kB" " Buffers: shared hit=134" " -> Seq Scan on product p (cost=0.00..117.00 rows=5000 width=8) (actual time=0.610..3.091 rows=5000.00 loops=2)" " Buffers: shared hit=134" " -> Hash (cost=1.50..1.50 rows=50 width=15) (actual time=0.403..0.404 rows=50.00 loops=2)" " Buckets: 1024 Batches: 1 Memory Usage: 11kB" " Buffers: shared hit=2" " -> Seq Scan on category c (cost=0.00..1.50 rows=50 width=15) (actual time=0.371..0.378 rows=50.00 loops=2)" " Buffers: shared hit=2" "Planning:" " Buffers: shared hit=162 dirtied=5" "Planning Time: 148.428 ms" "Execution Time: 645.128 ms"
Времетраење: 103.080 ms
Анализа: Многу бавно.
1.2. Со индекс
За брзо совпаѓање на продажните ставки со производот.
Применет индекс:
CREATE INDEX idx_sale_item_product_id ON stock_management.sale_item(product_id);
За брзо совпаѓање на производите по категорија
Применет индекс:
CREATE INDEX idx_product_category_id ON stock_management.product(category_id);
"Finalize GroupAggregate (cost=7589.33..7600.28 rows=50 width=47) (actual time=969.751..977.654 rows=50.00 loops=1)" " Group Key: c.category_id" " Buffers: shared hit=2054" " -> Gather Merge (cost=7589.33..7599.02 rows=85 width=47) (actual time=969.729..977.531 rows=100.00 loops=1)" " Workers Planned: 1" " Workers Launched: 1" " Buffers: shared hit=2054" " -> Sort (cost=6589.32..6589.45 rows=50 width=47) (actual time=792.046..792.057 rows=50.00 loops=2)" " Sort Key: c.category_id" " Sort Method: quicksort Memory: 30kB" " Buffers: shared hit=2054" " Worker 0: Sort Method: quicksort Memory: 30kB" " -> Partial HashAggregate (cost=6587.29..6587.91 rows=50 width=47) (actual time=790.873..790.904 rows=50.00 loops=2)" " Group Key: c.category_id" " Batches: 1 Memory Usage: 40kB" " Buffers: shared hit=2047" " Worker 0: Batches: 1 Memory Usage: 40kB" " -> Hash Join (cost=181.62..4822.93 rows=176436 width=25) (actual time=3.506..493.595 rows=149971.00 loops=2)" " Hash Cond: (p.category_id = c.category_id)" " Buffers: shared hit=2047" " -> Hash Join (cost=179.50..4318.40 rows=176436 width=14) (actual time=3.070..303.720 rows=149971.00 loops=2)" " Hash Cond: (si.product_id = p.product_id)" " Buffers: shared hit=2045" " -> Parallel Seq Scan on sale_item si (cost=0.00..3675.36 rows=176436 width=14) (actual time=0.055..93.742 rows=149971.00 loops=2)" " Buffers: shared hit=1911" " -> Hash (cost=117.00..117.00 rows=5000 width=8) (actual time=2.952..2.954 rows=5000.00 loops=2)" " Buckets: 8192 Batches: 1 Memory Usage: 260kB" " Buffers: shared hit=134" " -> Seq Scan on product p (cost=0.00..117.00 rows=5000 width=8) (actual time=0.279..1.620 rows=5000.00 loops=2)" " Buffers: shared hit=134" " -> Hash (cost=1.50..1.50 rows=50 width=15) (actual time=0.418..0.419 rows=50.00 loops=2)" " Buckets: 1024 Batches: 1 Memory Usage: 11kB" " Buffers: shared hit=2" " -> Seq Scan on category c (cost=0.00..1.50 rows=50 width=15) (actual time=0.377..0.387 rows=50.00 loops=2)" " Buffers: shared hit=2" "Planning:" " Buffers: shared hit=16" "Planning Time: 1.649 ms" "Execution Time: 977.996 ms"
Времетраење: 29.767 ms
Анализа: Многу по брзо.
Attachments (4)
- with_index_1.png (26.7 KB ) - added by 3 weeks ago.
- with_index_2.png (48.8 KB ) - added by 3 weeks ago.
- without_index_1.png (34.6 KB ) - added by 3 weeks ago.
- without_index_2.png (32.8 KB ) - added by 3 weeks ago.
Download all attachments as: .zip
Note:
See TracWiki
for help on using the wiki.
