EN/CH Mode

BRUCE_FE Interview Notes - SQL Injection Attack Details

In-depth analysis of SQL injection attack principles, types, and defense mechanisms. Learn how to prevent SQL injection attacks and protect web application database security.

EN/CH Mode

Lazy to read articles? Then watch videos!

What is SQL Injection Attack?

SQL injection is a security vulnerability where attackers insert malicious SQL code into input fields to make applications execute unauthorized SQL commands. This vulnerability occurs when applications directly concatenate user input into SQL queries without filtering.

Normal Login Flow:

// User input
Username: bruce
Password: 123456

// Generated SQL
SELECT * FROM users 
WHERE username = 'bruce' 
AND password = '123456'

SQL Injection Attack:

// User input
Username: admin' --
Password: anything

// Generated SQL
SELECT * FROM users 
WHERE username = 'admin' --' 
AND password = 'anything'

-- is SQL comment, making password check ignored

⚠️ SQL injection attacks may lead to:

1. Unauthorized access to sensitive data (user information, credit card numbers, etc.)
2. Bypass authentication, login as someone else
3. Modify or delete data in the database
4. Execute administrative operations (such as deleting entire tables)
5. Gain server control in some cases

Frontend-related SQL Injection Risks

Although SQL injection is mainly a backend issue, frontend developers also need to understand and help prevent it:

1. Parameters in API requests may be used to construct SQL queries
2. Frontend form data may pose risks when directly passed to backend
3. Client-side stored data (such as localStorage) may also be exploited if used for queries

Methods to Prevent SQL Injection

1. Use Parameterized Queries/Prepared Statements

// Unsafe way
const query = "SELECT * FROM users WHERE username = '" + username + "'";

// Safe way (parameterized query)
const query = "SELECT * FROM users WHERE username = ?";
db.execute(query, [username]);

2. Frontend Input Validation

Although frontend validation can be bypassed, it's still part of multi-layer defense

// React form validation example
const validateInput = (input) => {
  // Remove SQL special characters
  if (/[;'"\-]/.test(input)) {
    setError('Input contains disallowed characters');
    return false;
  }
  return true;
};

3. Use ORM (Object-Relational Mapping)

ORM is a tool that maps objects in code to database tables, allowing developers to operate databases by manipulating objects without directly writing SQL statements

// Without ORM (direct SQL)
const query = "SELECT * FROM users WHERE id = " + userId;

// With ORM (e.g., Sequelize)
const user = await User.findByPk(userId);  // Automatically prevents SQL injection

ORM tools automatically handle parameterized queries, effectively preventing SQL injection attacks while improving code readability and maintainability

Common SQL Injection Interview Questions

1. How to avoid SQL injection, what can frontend do?

A: Although SQL injection protection is mainly a backend responsibility, frontend can also provide additional protection layers:

1. Input Validation and Sanitization

Implement client-side input validation, filter or escape special characters (such as single quotes, semicolons, etc.)

const sanitizeInput = (input) => {
  return input.replace(/[\';\-\"\/\*]/g, '');
};

2. Use Parameterized API Requests

Use structured data (such as JSON) instead of string concatenation to pass parameters

// Recommended: Use structured data
fetch('/api/users', {
  method: 'POST',
  headers: { 'Content-Type': 'application/json' },
  body: JSON.stringify({ username, password })
});

3. Use Modern Frontend Frameworks

Frameworks like React and Vue automatically escape output, preventing XSS attacks and indirectly reducing SQL injection risks

4. Implement Appropriate Error Handling

Avoid displaying detailed database error messages to users, which may help attackers adjust injection strategies

Remember: Frontend protection is only the first line of defense and can never replace backend security measures. The fundamental defense against SQL injection must be implemented on the backend, such as using parameterized queries, ORM, and appropriate access controls.