Parameterized Queries From Application Code

A search box should supply a value, not write part of a SQL statement. Parameterized queries from application code keep those roles separate and make the behavior easier to test.

Keep Values Out of Command Text

Parameterized queries send SQL text with placeholders and send values separately through the driver. A quote in an email address remains data. It cannot close a string literal and append new SQL syntax. This is the main injection defense for ordinary values. It is also easier to read than manual escaping.

I look for string concatenation in application data-access code before I look for clever input filters. A filter can help user experience, but it should not be the security boundary. If the code builds SQL text by appending a user field, replace that path with a driver parameter. The fix belongs where the command is constructed.

Use Driver Parameters for Parameterized Queries

Each language and driver has its own API, but the pattern is the same: create a command with a placeholder, define a typed parameter, set its value, then execute. Do not wrap a placeholder in quote characters. The driver handles representation. Test with the exact driver version and connection settings used in production.

The PowerShell example uses the .NET SQL client available in the environment and leaves the connection string as a placeholder. It reads a sample parameterized value. Replace the table and connection details under your normal application conventions. Keep secrets in a secure store, not in a script file.

# PowerShell
$connectionString = 'Server=YOURSERVER;Database=YOURDATABASE;Integrated Security=True;Encrypt=True;TrustServerCertificate=False'
$connection = [System.Data.SqlClient.SqlConnection]::new($connectionString)
$command = $connection.CreateCommand()
$command.CommandText = 'SELECT CustomerID FROM dbo.Customers WHERE EmailAddress = @Email'
$parameter = $command.Parameters.Add('@Email', [System.Data.SqlDbType]::NVarChar, 320)
$parameter.Value = 'sample@example.com'
$connection.Open()
try { $null = $command.ExecuteScalar() }
finally { $connection.Dispose() }

Match Type and Length

A parameter should use the same logical type and a sensible length for the target column. Passing every value as a huge Unicode string can cause conversions and poor plans when the column type differs. Passing a value through a short parameter can truncate it or change comparisons. Use decimal precision and scale deliberately for money-like data, and use date types for dates.

I have found parameterization implemented correctly for security but poorly for performance because every parameter was guessed. Read the schema. Make types explicit in the data-access layer. Test long and boundary values. The goal is for the server to compare like with like, while the input remains safely separate from SQL syntax.

Understand Plan Reuse With Parameterized Queries

Stable command text with different parameter values can allow SQL Server to reuse a cached plan. That saves compilation work, but one plan is not always ideal for every parameter distribution. Parameter-sensitive plan behavior requires its own diagnosis. Do not abandon parameterization because a query needs tuning. Use indexes, statistics, query design, or supported plan features to address performance.

I separate security from tuning in the review. The code-data boundary stays in place. Then I investigate the actual plan and workload with measurements from the server. An old concatenated query can look fast in one test because every literal produced a new plan; that is not a reason to keep injection risk.

Handle Lists and Identifiers

A parameter represents one value, not a comma-separated list of SQL syntax. For a list of IDs, use a table-valued parameter, a temporary table populated safely, or a supported structured method. For a table or column name, use an approved allowlist and carefully quote the selected identifier. Driver value parameters do not replace object names.

I review sorting controls closely. A user choosing newest or oldest should map to fixed known query text, not supply ORDER BY content. A small number of clear query variants is easy to test. Do not pretend that adding a parameter marker to an identifier position will work. SQL Server needs the identifier in command text, so the application must control that choice.

Test Parameterized Queries With Hostile Values

Use a safe nonproduction database with synthetic data. Submit quotes, comment markers, Unicode characters, long strings, empty strings, and nulls. Verify that each remains a value and cannot change the query’s structure. Check errors as well as returned rows. A sanitized display can hide a database error that still deserves correction.

I include ordinary tricky data too: apostrophes in names, leading zeros, and unusual email addresses. A secure query should also be a correct query. If a test fails, inspect the parameter type and value before adding another string replacement. Manual escaping tends to create a new edge case for every fix.

Watch Logging and Error Handling

Application logs should record command identity, duration, and safe diagnostics without dumping secrets or personal parameter values. Parameterized queries can still expose sensitive values if the application writes them to logs. Handle database errors without showing raw SQL and connection details to end users. Keep enough internal context to reproduce a problem safely.

I ask what a support engineer would see after a failed request. It should identify the operation and error category, not reveal a password or customer record. Parameterization solves command construction. Data handling around the command remains part of secure application design.

Review Stored Procedures Too

Calling a stored procedure through a parameterized driver command is good, but the procedure itself can build unsafe dynamic SQL. Review both layers. A safe application parameter becomes unsafe if the procedure concatenates it into a new command. Use sp_executesql with typed parameters inside T-SQL when dynamic statements are necessary.

The SQL example shows the server-side pattern with a separate parameter. The table is a placeholder for your schema. Compare it with the application command and confirm lengths match. I test the full path from application input to final predicate.

DECLARE @sql nvarchar(max) =
    N'SELECT CustomerID FROM dbo.Customers WHERE EmailAddress = @Email';
DECLARE @email nvarchar(320) = N'sample@example.com';
EXEC sys.sp_executesql
    @sql,
    N'@Email nvarchar(320)',
    @Email = @email;

Make the Pattern Routine

Use parameterized access helpers in the application framework and add a code review rule against concatenating untrusted values into SQL text. Keep examples with correct types and lengths. Test new data paths, especially reports and search screens, where dynamic filters grow over time. A single safe helper used consistently beats scattered custom escaping functions.

What query in your application still joins a request field to a SQL string? Start there. Replace it, test it, and make the new pattern the easiest one for the next developer to use. The security benefit comes from consistent construction across every path.

Related reading on this blog: SQL Injection: How It Works and How to Stop It and Parameter Sniffing and Bad Plan.

A parameter is not an escaped string, it is a separate value sent through the driver.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.