Automatic Database Encryption

Welcome to the final lesson of the "Cryptographic Failures" course! In our previous lessons, we explored the importance of cryptography in securing data and identified common vulnerabilities, such as weak algorithms and hardcoded secrets.

In this lesson, we'll focus on understanding the limitations of automatic database encryption and the importance of application-level encryption. Let's dive in! 🚀

Many developers assume that enabling database encryption features automatically makes their sensitive data secure. While database encryption protects data at rest (when stored on disk), it doesn't protect data in transit or during processing. When your application queries the database, the data is automatically decrypted and returned in plaintext. This means anyone who can access your application or database through legitimate means can view sensitive data in its unencrypted form.

This automatic decryption creates a significant security risk, especially for sensitive information like credit card numbers, personal identification data, or healthcare records. Let's examine how this vulnerability manifests in code and learn how to properly secure it using application-level encryption.

Suppose we have an endpoint responsible for adding credit card information. It uses SQL queries for adding the payment information. For simplicity, we'll skip JWT authentication in this example.

Here's how the endpoint might look without proper encryption:

router.post('/addCardInfo', async (req, res) => {
  const { userId, cardNumber } = req.body;
  
  try {
    // Card data is stored without encryption
    const query = 'INSERT INTO payments (user_id, card_number) VALUES (?, ?)';
    const stmt = db.prepare(query);
    stmt.run(userId, cardNumber);
    
    res.json({ success: true });
  } catch (error) {
    console.error("Database query error:", error);
    res.status(500).json({ error: "Internal server error" });
  }
});

This implementation is problematic because it stores the credit card number in its raw form. Even if the database encrypts data at rest, the number is vulnerable during transmission and processing. Additionally, anyone with access to the application can retrieve the unencrypted card numbers.

Let's see how this vulnerability manifests when retrieving data.

The vulnerability becomes even more apparent when retrieving stored card information:

router.get('/getCardInfo', async (_req, res) => {
  try {
    const query = 'SELECT * FROM payments';
    const stmt = db.prepare(query);
    const results = stmt.all();
    
    // Card numbers are returned in plaintext
    res.json(results);
  } catch (error) {
    console.error("Database query error:", error);
    res.status(500).json({ error: "Internal server error" });
  }
});

This endpoint retrieves credit card numbers directly from the database. If an attacker gains access to this endpoint or exploits another endpoint via SQL injection (We'll cover SQL injections in-detail next), they can access the exposed information.

Let's see how an attacker might exploit this vulnerability.

An attacker with access to the API can easily retrieve sensitive card information:

curl http://localhost:3000/api/payments/getCardInfo

# Example Response:
# {
#   "data": [
#     {
#       "user_id": 1,
#       "card_number": "4532-7153-3790-4561"
#     },
#     {
#       "user_id": 2,
#       "card_number": "4532-7153-3790-4562"
#     }
#   ]
# }

As you can see, the credit card numbers are exposed in plaintext in the API response. This vulnerability exists regardless of database-level encryption because the data is automatically decrypted when queried. Let's look at how to properly secure this sensitive data.

Here's how to properly hash and store sensitive data:

router.post('/addCardInfo', async (req, res) => {
  const { userId, cardNumber } = req.body;
  
  try {
    // Store only the hash of the card number for verification
    const cardHash = await hashCardNumber(cardNumber);
    
    // Store the last 4 digits for display purposes
    const lastFourDigits = cardNumber.slice(-4);
    
    const query = 'INSERT INTO payments (user_id, card_hash, last_four_digits) VALUES (?, ?, ?)';
    const stmt = db.prepare(query);
    stmt.run(userId, cardHash, lastFourDigits);
    
    res.json({ success: true });
  } catch (error) {
    console.error("Database query error:", error);
    res.status(500).json({ error: "Internal server error" });
  }
});

This secure implementation hashes the credit card number before storing it. We only store the hash (for verification purposes) and the last four digits (for display purposes). This way, even if someone accesses the database directly, they can't recover the original card number. Let's look at how the hashing function works.

The hashCardNumber function will use bcrypt, a strong hashing algorithm specifically designed for passwords and sensitive data:

async function hashCardNumber(cardNumber: string): Promise<string> {
  // Use bcrypt with a cost factor of 12
  const saltRounds = 12;
  return await bcrypt.hash(cardNumber, saltRounds);
}

We use bcrypt because it's specifically designed to be slow and computationally intensive, making it resistant to brute-force attacks. It also automatically handles salt generation and storage, making it a secure choice for hashing sensitive data.

Now, let's see how we can safely retrieve and display card information to users.

When displaying card information to users, we only show the last four digits of the card number:

router.get('/getCardInfo', async (_req, res) => {
  try {
    const query = 'SELECT user_id, last_four_digits FROM payments';
    const stmt = db.prepare(query);
    const results = stmt.all();
    
    // Only return masked card numbers
    const maskedResults = results.map(row => ({
      user_id: row.user_id,
      card_number: `****-****-****-${row.last_four_digits}`
    }));
    
    res.json(maskedResults);
  } catch (error) {
    console.error("Database query error:", error);
    res.status(500).json({ error: "Internal server error" });
  }
});

This implementation ensures that users only see masked card numbers with the last four digits visible. The last four digits are sufficient for users to identify their cards, as this is a standard practice in the payment card industry.

For example, if a user has multiple cards, they can easily recognize that "---4561" is their Visa card ending in 4561, while "---3789" is their Mastercard ending in 3789. This approach provides a balance between security and usability.

When you need to verify a card number (for example, during a payment transaction), you can compare it with the stored hash using bcrypt's compare function:

export async function verifyCardNumber(cardNumber: string, storedHash: string): Promise<boolean> {
  try {
    return await bcrypt.compare(cardNumber, storedHash);
  } catch (error) {
    console.error("Hash comparison error:", error);
    return false;
  }
}

This function takes the provided card number and the stored hash, then uses bcrypt's compare function to verify if they match. The comparison is done securely, protecting against timing attacks.

If there's an error during comparison, the function returns false to ensure no sensitive information is leaked through error messages.

In this lesson, we explored why relying solely on database encryption isn't sufficient for protecting sensitive data. We learned how to implement proper security measures for handling credit card data by:

• Storing only hashed values and last four digits
• Never transmitting full card numbers in responses
• Using bcrypt for secure hashing
• Displaying masked card numbers with last four digits for user recognition

As you move on to the practice exercises, you'll have the opportunity to implement these security methods yourself. In the next lesson, we'll continue to build on this knowledge, further enhancing your application security skills. Keep up the great work! 🌟