Day 9: Security and SQL Injection Prevention
Date: January 27, 2025
Database security is a critical aspect of application development. Today, we focus on understanding SQL injection—a major vulnerability in web applications—and learn how to prevent it using techniques like prepared statements, role management, and input sanitization. By the end of this session, you'll have a solid foundation for securing your database and API.
Concepts
1. What is SQL Injection and Why is it Dangerous?
SQL injection is an attack technique where malicious SQL code is injected into input fields, tricking the database into executing unintended commands. This can lead to:
- Unauthorized data access or modification.
- Full database compromise.
- Leakage of sensitive information like passwords and personal data.
Example of Vulnerable Query:
-- Vulnerable to SQL injection
SELECT * FROM users WHERE email = '" + userEmail + "' AND password = '" + userPassword + "'";
If an attacker inputs:
userEmail: ' OR 1=1 --
userPassword: anything
The query becomes:
SELECT * FROM users WHERE email = '' OR 1=1 --' AND password = 'anything';
Here, OR 1=1
always evaluates to true, bypassing authentication.
2. Using Prepared Statements and Parameterized Queries
Prepared statements separate SQL logic from input data, preventing malicious code injection.
Benefits:
- Inputs are treated as data, not executable SQL.
- Prevents attackers from injecting SQL commands.
Example of a Secure Query:
In Java:
String query = "SELECT * FROM users WHERE email = ? AND password = ?";
PreparedStatement stmt = connection.prepareStatement(query);
stmt.setString(1, userEmail);
stmt.setString(2, userPassword);
ResultSet rs = stmt.executeQuery();
In Python:
cursor.execute("SELECT * FROM users WHERE email = %s AND password = %s", (user_email, user_password))
3. Database Permissions and Role Management
Limiting database access based on roles reduces the impact of a potential compromise.
Key Practices:
- Grant least privilege to users and applications.
- Use different accounts for application-level and administrative tasks.
- Revise and audit database permissions regularly.
Example:
Create a role for an application with restricted permissions:
-- Create a role with read and write permissions only
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'secure_password';
GRANT SELECT, INSERT, UPDATE, DELETE ON database_name.* TO 'app_user'@'localhost';
Practice
Step 1: Securing Your API with Prepared Statements
Secure all database interactions in your API by replacing concatenated SQL queries with prepared statements or parameterized queries.
Before (Insecure Query):
db.query(`SELECT * FROM users WHERE email = '${email}' AND password = '${password}'`);
After (Secure Query):
db.query("SELECT * FROM users WHERE email = ? AND password = ?", [email, password]);
Step 2: Testing for SQL Injection Vulnerabilities
Use manual testing or tools like SQLMap to identify vulnerabilities in your API.
Steps:
- Input special characters like
'
,--
, or;
into input fields to see if the database throws an error. - Test for boolean-based injections:
- Input:
' OR 1=1 --
- Input:
- Test for error-based injections by inputting:
-
' AND 1=CAST((SELECT version()) AS INT) --
-
Fix Detected Vulnerabilities:
- Replace vulnerable queries with parameterized queries.
- Sanitize inputs to remove special characters.
Step 3: Role-Based Access Control
Implement database roles with restricted permissions for your application.
Example:
- Create a user with read-only access for analytics queries:
CREATE USER 'readonly_user'@'localhost' IDENTIFIED BY 'readonly_password';
GRANT SELECT ON database_name.* TO 'readonly_user'@'localhost';
- Use this account for non-critical operations like generating reports.
Real-World Example: Securing Login Forms
Your login form is a prime target for SQL injection attacks. Here's how to protect it:
Initial Vulnerable Code:
SELECT * FROM users WHERE email = '" + email + "' AND password = '" + password + "';
Secured Version with Prepared Statements:
- Backend (Java Example):
String query = "SELECT * FROM users WHERE email = ? AND password = ?";
PreparedStatement stmt = connection.prepareStatement(query);
stmt.setString(1, email);
stmt.setString(2, password);
ResultSet rs = stmt.executeQuery();
Sanitize Inputs: Use a library or framework like Hibernate or Django ORM that handles parameterization automatically.
Password Hashing: Store hashed passwords in the database to add another layer of security.
Testing the Form:
- Input:
' OR 1=1 --
- Verify that the query doesn’t bypass authentication.
- Test with automated tools to ensure there are no injection points.
Additional Tips
Use Input Validation:
Validate all user inputs on both client and server sides. Restrict input formats using regular expressions.Monitor and Log Queries:
Implement query logging to monitor for suspicious activity.Update Your Database and Application Framework:
Regular updates ensure you’re protected against known vulnerabilities.
Outcome for the Day
By the end of Day 9, you should:
- Understand the dangers of SQL injection and how to mitigate it.
- Use prepared statements for all database queries.
- Implement role-based access control and sanitize user inputs.
Next Step
In Day 10, we’ll bring everything together with Database Backup, Recovery, and Project Integration to ensure your database is robust and production-ready. Stay tuned!
Top comments (0)