Masking the First 6 Digits of a Phone Number in MySQL

Handling sensitive information, such as phone numbers, is crucial for maintaining user privacy in modern applications. One common requirement is to mask part of a phone number to protect user data while still displaying a portion of it for validation or confirmation. In this blog post, we’ll explore how to mask the first 6 digits of a phone number using MySQL, including the use of the REPLICATE function as well as CONCAT(), RIGHT(), and LPAD(). Let’s walk through the process with practical examples.

Why Mask Phone Numbers?

Masking phone numbers is important for various reasons, including:

• Data Privacy: Hiding parts of sensitive information helps to protect user data from unauthorized access.
• Compliance: Regulations like GDPR and CCPA mandate data protection practices, including anonymizing user data.
• User Security: Masking data can prevent misuse if someone gains unauthorized access to a database.

For instance, when displaying phone numbers on user profiles, customer service interfaces, or invoices, it’s better to show partially masked phone numbers like ******1234 rather than the full number.

Problem Statement

Given a phone number in a database, we want to mask the first 6 digits with asterisks (*). For example, if the phone number is 1234567890, it should be displayed as ******7890.

MySQL Solution Overview

To achieve this, we’ll use MySQL’s built-in string functions:

• CONCAT(): Concatenates multiple strings.
• RIGHT(): Extracts a specified number of characters from the right side of a string.
• REPLICATE(): Repeats a given string a specified number of times (useful for creating a string of asterisks).
• LPAD(): Pads the left side of a string with specified characters up to a certain length.

Step-by-Step Implementation

Let’s break down the solution:

1. Create a Sample Table and Insert Data: First, we’ll create a table named contacts to store phone numbers.
2. Use CONCAT(), REPLICATE(), and RIGHT() Functions: We’ll mask the first 6 digits and show the last 4.
3. Query the Data: Display masked phone numbers using a SELECT statement.

Step 1: Create the Sample Table

CREATE TABLE contacts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    phone_number VARCHAR(15)
);

-- Insert sample data
INSERT INTO contacts (name, phone_number) VALUES
('John Doe', '1234567890'),
('Jane Smith', '9876543210'),
('Alice Johnson', '5556667777'),
('Bob Brown', '4445556666');

In this example, we’ve created a table contacts with id, name, and phone_number columns. We also inserted a few dummy phone numbers for demonstration.

Step 2: Masking the First 6 Digits Using REPLICATE()

To mask the first 6 digits, we will use the REPLICATE() function to generate a string of asterisks and concatenate it with the last 4 digits of the phone number:

SELECT 
    name,
    CONCAT(REPLICATE('*', 6), RIGHT(phone_number, 4)) AS masked_phone_number
FROM 
    contacts;

Explanation:

• REPLICATE('*', 6): Creates a string with 6 asterisks (******).
• RIGHT(phone_number, 4): Extracts the last 4 digits of the phone_number.
• CONCAT(): Concatenates the masked portion with the last 4 digits, resulting in a masked version of the phone number.

Step 3: Result

Running the above query will yield the following result:

As you can see, the first 6 digits of each phone number have been replaced with asterisks using the REPLICATE() function.

Using LPAD for More Control

An alternative method is to use the LPAD() function. This function pads the left side of a string with specified characters until it reaches the desired length. Here’s how you can use LPAD() to achieve the same result:

SELECT 
    name,
    CONCAT(LPAD('', 6, '*'), RIGHT(phone_number, 4)) AS masked_phone_number
FROM 
    contacts;

Explanation:

• LPAD('', 6, '*'): Generates a string of 6 asterisks.
• CONCAT(): Concatenates the 6 asterisks with the last 4 digits extracted using RIGHT().

The result will be identical, but using LPAD() provides more flexibility if you need to adjust the length of the mask dynamically.

Result:

Comparing REPLICATE() and LPAD()

• REPLICATE(): Great for generating repeated characters quickly and concisely. It’s simple when you just need a fixed number of characters repeated.
• LPAD(): Offers more control when you want to pad strings to a certain length, and can be useful if the number of masked digits changes dynamically.

Both functions are effective for masking phone numbers, and the choice depends on your preference or specific requirements of your application.

Considerations and Best Practices

1. Data Types: Ensure that the phone_number column is stored as a string (VARCHAR). If stored as an integer, leading zeros might be lost, which can cause issues in regions where phone numbers may begin with zeros.
2. Masking Different Lengths: For international numbers or those with varying lengths, consider dynamically adjusting the number of masked digits based on length.
3. Performance: Be mindful when using string functions like RIGHT(), REPLICATE(), and CONCAT() in large datasets, as they can impact performance. Using proper indexing on columns involved can help optimize the queries.

Practice Questions

1. Write a query to mask the first 3 characters of an email address, keeping the domain intact (e.g., ***@domain.com).
2. How would you mask all but the last two digits of a 6-digit identification number in MySQL?
3. Write a query that masks phone numbers but only reveals the area code (e.g., 123****7890).
4. Modify the solution to display ****** only if the phone number is longer than 6 digits.
5. Create a stored procedure that accepts a phone number and returns it with the first 6 digits masked.

Conclusion

Masking the first 6 digits of a phone number in MySQL is a simple yet effective way to protect sensitive data while maintaining usability. Using functions like CONCAT(), RIGHT(), REPLICATE(), and LPAD(), you can ensure that phone numbers are displayed securely in your applications. By applying these methods, you can enhance user privacy and comply with data protection regulations.