Naming Conventions & SQL Injection Prevention
Consistent naming conventions make your database easier to understand and maintain. While there is no single universal standard, most teams adopt one convention and stick with it across all projects.
SQL injection is one of the most dangerous security vulnerabilities in web applications. It occurs when user input is directly inserted into SQL queries, allowing attackers to execute arbitrary SQL commands. Always use parameterized queries to prevent injection.
- Use snake_case for table and column names: user_accounts, first_name, created_at
- Use plural names for tables: users, orders, products (not user, order, product)
- Use singular names for columns: username, email, age (not usernames)
- Be descriptive: use created_at instead of ca, order_total instead of ot
- Avoid reserved words as names: do not name a column 'order', 'select', or 'table'
-- BAD: SQL injection vulnerability (NEVER do this)
-- query = "SELECT * FROM users WHERE username = '" + userInput + "'";
-- If userInput = "' OR '1'='1" --> returns ALL users!
-- If userInput = "'; DROP TABLE users; --" --> deletes the table!
-- GOOD: Use parameterized queries (example in application code)
-- Python:
-- cursor.execute("SELECT * FROM users WHERE username = %s", (username,))
-- JavaScript (Node.js with mysql2):
-- connection.execute('SELECT * FROM users WHERE username = ?', [username])
-- PHP (PDO):
-- $stmt = $pdo->prepare('SELECT * FROM users WHERE username = :username');
-- $stmt->execute(['username' => $username]);
-- Good naming conventions
CREATE TABLE product_categories (
id INT PRIMARY KEY AUTO_INCREMENT,
category_name VARCHAR(100) NOT NULL,
parent_category_id INT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);- SQL injection is listed as the #1 web application security risk by OWASP. NEVER build SQL queries by concatenating user input strings. Always use parameterized queries or prepared statements.
Transactions, ACID Properties & Backups
A transaction is a sequence of SQL operations that are treated as a single unit of work. Either all operations succeed (COMMIT), or all are rolled back (ROLLBACK) as if nothing happened. This is crucial for maintaining data consistency.
ACID properties define the guarantees that a reliable database transaction must provide. Understanding ACID is fundamental to building applications that handle data correctly.
- Atomicity: All operations in a transaction succeed or none do — there is no partial completion
- Consistency: A transaction brings the database from one valid state to another
- Isolation: Concurrent transactions do not interfere with each other
- Durability: Once a transaction is committed, the data is permanently saved, even if the system crashes
-- Transaction example: Transfer money between accounts
START TRANSACTION;
-- Deduct from sender
UPDATE accounts SET balance = balance - 100
WHERE id = 1;
-- Add to receiver
UPDATE accounts SET balance = balance + 100
WHERE id = 2;
-- If everything is correct, save the changes
COMMIT;
-- If something went wrong, undo everything
-- ROLLBACK;
-- Example with error handling (conceptual)
-- START TRANSACTION;
-- UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- IF (SELECT balance FROM accounts WHERE id = 1) < 0 THEN
-- ROLLBACK; -- Insufficient funds, undo
-- ELSE
-- UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- COMMIT;
-- END IF;
-- Backup a database (command line)
-- mysqldump -u root -p my_database > backup.sql
-- Restore: mysql -u root -p my_database < backup.sql- Always use transactions when multiple related operations must succeed or fail together. The classic example is a bank transfer — you never want money deducted from one account without being added to another.