DEV Community

Cover image for Security and SQL Injection Prevention
arjun
arjun

Posted on

Security and SQL Injection Prevention

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 + "'";
Enter fullscreen mode Exit fullscreen mode

If an attacker inputs:

userEmail: ' OR 1=1 --
userPassword: anything
Enter fullscreen mode Exit fullscreen mode

The query becomes:

SELECT * FROM users WHERE email = '' OR 1=1 --' AND password = 'anything';
Enter fullscreen mode Exit fullscreen mode

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();
Enter fullscreen mode Exit fullscreen mode

In Python:

cursor.execute("SELECT * FROM users WHERE email = %s AND password = %s", (user_email, user_password))
Enter fullscreen mode Exit fullscreen mode

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';
Enter fullscreen mode Exit fullscreen mode

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}'`);
Enter fullscreen mode Exit fullscreen mode

After (Secure Query):

db.query("SELECT * FROM users WHERE email = ? AND password = ?", [email, password]);
Enter fullscreen mode Exit fullscreen mode

Step 2: Testing for SQL Injection Vulnerabilities

Use manual testing or tools like SQLMap to identify vulnerabilities in your API.

Steps:

  1. Input special characters like ', --, or ; into input fields to see if the database throws an error.
  2. Test for boolean-based injections:
    • Input: ' OR 1=1 --
  3. 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:

  1. 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';
Enter fullscreen mode Exit fullscreen mode
  1. 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 + "';
Enter fullscreen mode Exit fullscreen mode

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();
Enter fullscreen mode Exit fullscreen mode
  • 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:

  1. Input: ' OR 1=1 --
  2. Verify that the query doesn’t bypass authentication.
  3. Test with automated tools to ensure there are no injection points.

Additional Tips

  1. Use Input Validation:

    Validate all user inputs on both client and server sides. Restrict input formats using regular expressions.

  2. Monitor and Log Queries:

    Implement query logging to monitor for suspicious activity.

  3. 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!

Billboard image

Monitoring as code

With Checkly, you can use Playwright tests and Javascript to monitor end-to-end scenarios in your NextJS, Astro, Remix, or other application.

Get started now!

Top comments (0)

Billboard image

Try REST API Generation for Snowflake

DevOps for Private APIs. Automate the building, securing, and documenting of internal/private REST APIs with built-in enterprise security on bare-metal, VMs, or containers.

  • Auto-generated live APIs mapped from Snowflake database schema
  • Interactive Swagger API documentation
  • Scripting engine to customize your API
  • Built-in role-based access control

Learn more

👋 Kindness is contagious

Discover a treasure trove of wisdom within this insightful piece, highly respected in the nurturing DEV Community enviroment. Developers, whether novice or expert, are encouraged to participate and add to our shared knowledge basin.

A simple "thank you" can illuminate someone's day. Express your appreciation in the comments section!

On DEV, sharing ideas smoothens our journey and strengthens our community ties. Learn something useful? Offering a quick thanks to the author is deeply appreciated.

Okay