SQL & Databases · Interview Question

You must build dynamic SQL for a flexible report. How do you keep it safe and fast?

Detailed answer, explanation and code for this interview question. Ideal for SQL & Databases interview preparation, coding rounds and viva questions.

Difficulty: MediumType: Subjective

Problem Statement

You must build dynamic SQL for a flexible report. How do you keep it safe and fast?

Explanation

Allow only whitelisted identifiers (columns, sort keys). Bind user values as parameters so the engine can reuse plans and injection is blocked. Expose a wrapper stored procedure with EXECUTE permissions, not direct table access. Log the final statement and role for audit. Add resource guards such as row limits and timeouts, and index the columns your dynamic filters hit most.

Code Solution

SolutionRead Only

-- SQL Server sample
DECLARE @sql nvarchar(max)=N'SELECT col_list FROM t WHERE status=@s';
EXEC sp_executesql @sql, N'@s nvarchar(10)', @s=@in_status;

Practice Sets

This question appears in the following practice sets:

Security, Backup & Recovery (DBA Essentials)

Next Question

More from SQL & Databases

Master SQL & Databases with our complete collection of questions, tutorials and guides.

Browse Subject View All Questions

Mobile App Now Available

Master Interviews
Anywhere, Anytime

Get the Preplance app for a seamless learning experience. Practice offline, get daily streaks, and stay ahead with real-time interview updates.

Get it on

Google Play

4.9/5 Rating on Store

You must build dynamic SQL for a flexible report. How do you keep it safe and fast?

Problem Statement

Explanation

Code Solution

Practice Sets

Related Questions

Which SQL statement retrieves all columns and rows from a table named employees?

Which clause filters rows before aggregation and affects which rows are grouped?

What is the default sort order when ORDER BY is used without ASC or DESC?

Which construct is commonly used to paginate results in SQL engines like Postgres and MySQL?

Which predicate correctly checks for missing values in standard SQL?

More from SQL & Databases

Master Interviews
Anywhere, Anytime

You must build dynamic SQL for a flexible report. How do you keep it safe and fast?

Problem Statement

Explanation

Code Solution

Practice Sets

Related Questions

Which SQL statement retrieves all columns and rows from a table named employees?

Which clause filters rows before aggregation and affects which rows are grouped?

What is the default sort order when ORDER BY is used without ASC or DESC?

Which construct is commonly used to paginate results in SQL engines like Postgres and MySQL?

Which predicate correctly checks for missing values in standard SQL?

More from SQL & Databases

You must build dynamic SQL for a flexible report. How do you keep it safe and fast?

Problem Statement

Explanation

Code Solution

Practice Sets

Related Questions

Which SQL statement retrieves all columns and rows from a table named employees?

Which clause filters rows before aggregation and affects which rows are grouped?

What is the default sort order when ORDER BY is used without ASC or DESC?

Which construct is commonly used to paginate results in SQL engines like Postgres and MySQL?

Which predicate correctly checks for missing values in standard SQL?

More from SQL & Databases

Master Interviews Anywhere, Anytime

You must build dynamic SQL for a flexible report. How do you keep it safe and fast?

Problem Statement

Explanation

Code Solution

Practice Sets

Related Questions

Which SQL statement retrieves all columns and rows from a table named employees?

Which clause filters rows before aggregation and affects which rows are grouped?

What is the default sort order when ORDER BY is used without ASC or DESC?

Which construct is commonly used to paginate results in SQL engines like Postgres and MySQL?

Which predicate correctly checks for missing values in standard SQL?

More from SQL & Databases

Master Interviews
Anywhere, Anytime