How to Mask SQL Table Names, Column Names, and Values

Information that is easily leaked in SQL

The string in the WHERE clause and the value in the INSERT statement are noticeable, but the business content can also be inferred from identifiers such as customer_contract and employee_salary. Connection destination name, schema name, comment, and stored procedure name are also checked.

• Values such as customer number, name, address, email address, etc.
• Schema name and table name including internal system name
• Column names, constants, and comments that explain business rules
• Connection string, DB link, hostname, file path

Steps to replace without destroying the structure

1. Copy only the SQL statements needed for investigation and separate connection settings and execution logs.
2. Decide which categories to mask: tables, columns, values, and comments.
3. Use the same replacement for each occurrence of an identifier so JOINs and subqueries remain consistent.
4. Select the target DB and check whether DB specific expressions such as quotation marks, functions, and paging syntax remain.

Points to check before sharing

Treat masked SQL as a copy for sharing; do not run it against a production database. If execution testing is necessary, use an isolated environment with test data.

Specific example: Sharing customer search SQL for investigation

In the customer search SQL, the business content is inferred from just the table name and column name, the customer number and period remain in the WHERE clause, and multiple pieces of actual data remain in the IN clause. Check including comments and schema name.

Replacing all strings with the same value loses JOIN conditions and duplicate-value relationships. Assign the same masked value to the same original value, while keeping different values distinguishable.

1. Select the target DB and enter the entire SQL.
2. Determine the scope of tables, columns, identifiers, values, and comments.
3. Perform masking and check for broken quotes, placeholders, and DB-specific functions.
4. Search again for email addresses, phone numbers, dates, and IDs before copying the results.

Examining execution plans may require table structures and distributions of values. Please decide the balance between the scope to be hidden and reproducibility with the investigator.

Frequently asked questions

Is it enough to mask just the SQL values?

Confidential information may be inferred from schema names, table names, column names, and comments, including project names. Identifiers are also targeted depending on the purpose of sharing.

Can the SQL after masking be executed in the database?

Although the purpose is to preserve the syntax, please do not assume that it will be executed as is, as it will be replaced with a table name or value that does not exist.