Repository navigation
Expand file tree
/
Copy pathqueries.sql
More file actions
96 lines (80 loc) · 2.85 KB
/
Copy pathqueries.sql
File metadata and controls
96 lines (80 loc) · 2.85 KB
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
-- Database queries for user management system
-- WARNING: Contains intentional issues for testing
-- BUG: Missing indexes on JOIN columns
CREATE TABLE users (
id INTEGER PRIMARY KEY,
username VARCHAR(50),
email VARCHAR(100),
created_at TIMESTAMP,
department_id INTEGER
);
CREATE TABLE departments (
id INTEGER PRIMARY KEY,
name VARCHAR(100),
manager_id INTEGER
);
-- BUG: No indexes on foreign key columns
CREATE TABLE user_sessions (
id INTEGER PRIMARY KEY,
user_id INTEGER,
token VARCHAR(255),
created_at TIMESTAMP
);
-- BUG: SELECT * in production code (bad practice)
-- BUG: No pagination, will return all records
SELECT * FROM users;
-- BUG: Cartesian join from missing WHERE clause
SELECT u.username, d.name
FROM users u, departments d;
-- Missing: WHERE u.department_id = d.id
-- BUG: SQL injection vulnerability (concatenated query)
-- This would be built dynamically in application:
SELECT * FROM users WHERE username = '' OR '1'='1'; -- SQL injection example
-- BUG: Inefficient subquery instead of JOIN
SELECT username,
(SELECT name FROM departments WHERE id = users.department_id) as dept_name,
(SELECT COUNT(*) FROM user_sessions WHERE user_id = users.id) as session_count
FROM users;
-- Should use JOINs instead
-- BUG: Missing transaction for related operations
-- These should be in a transaction
UPDATE users SET email = 'new@email.com' WHERE id = 1;
UPDATE user_sessions SET token = 'new_token' WHERE user_id = 1;
-- Missing BEGIN TRANSACTION and COMMIT
-- BUG: Potential deadlock from transaction ordering
-- Transaction 1:
BEGIN TRANSACTION;
UPDATE users SET status = 'active' WHERE id = 1;
UPDATE departments SET updated = NOW() WHERE id = 1;
COMMIT;
-- Transaction 2 (in different order - deadlock potential):
BEGIN TRANSACTION;
UPDATE departments SET updated = NOW() WHERE id = 1;
UPDATE users SET status = 'active' WHERE id = 1;
COMMIT;
-- BUG: No proper indexing strategy
-- Query that would benefit from composite index
SELECT u.username, u.email, d.name
FROM users u
JOIN departments d ON u.department_id = d.id
WHERE u.created_at > '2024-01-01'
AND d.name LIKE '%Engineering%'
ORDER BY u.created_at DESC
LIMIT 100;
-- Needs index on (created_at, department_id)
-- BUG: Using LIKE with leading wildcard (can't use index)
SELECT * FROM users WHERE email LIKE '%@gmail.com';
-- BUG: Storing sensitive data in plain text
CREATE TABLE passwords (
user_id INTEGER,
password VARCHAR(255) -- Should be hashed!
);
-- BUG: No cascade deletes defined
DELETE FROM departments WHERE id = 1;
-- Will leave orphaned users with department_id = 1
-- BUG: Inefficient UPDATE without WHERE clause
UPDATE users SET last_login = NOW();
-- Updates ALL users instead of specific one
-- BUG: Not using prepared statements (shown as concatenated)
-- Example of what NOT to do:
SELECT * FROM users WHERE id = 'user_input_here';