Basant for sinceweb

Posted on Nov 14

SQL parameterization

#sql #sqlserver #database

1) Plain-language definition & core idea

A parameterized query is a SQL statement written with placeholders (parameters) instead of directly embedding user values into the SQL string. The application or driver sends the SQL text with placeholders to the database and sends the parameter values separately. The database treats parameter values as data only (not SQL), so malicious characters inside the values cannot change the SQL structure — that’s why parameterization prevents SQL injection.

Example (plain):

Unsafe: 'SELECT * FROM Users WHERE username = ''' + username + ''';' (string concatenation)
Safe (parameterized): SELECT * FROM Users WHERE username = @username; plus bind @username = 'alice'

2) How placeholders work (short)

Placeholder appears in SQL text (e.g., @name, ?, $1).
SQL text and parameter metadata are sent/compiled (server-side or driver-side).
Parameter values are sent separately and bound to placeholders.
The DB engine compiles/optimizes the statement using the placeholders; values are injected into plan as typed data, not as SQL code.

3) Simple SELECT filter: unsafe vs safe (SQL Server and MySQL examples)

Unsafe — string concatenation (vulnerable)

-- DO NOT RUN with untrusted input
DECLARE @username NVARCHAR(100) = 'alice''; DROP TABLE Users; --';
EXEC('SELECT * FROM Users WHERE username = ''' + @username + ''';');

If @username contains alice'; DROP TABLE Users; -- it becomes executable SQL.

Safe — parameterized (SQL Server)

-- SQL Server: parameterized
DECLARE @username NVARCHAR(100) = 'alice';
SELECT * FROM Users WHERE username = @username;
-- @username is bound as NVARCHAR, treated as data.

Safe — parameterized (MySQL client-style placeholder)

-- Example using a client/driver that supports ? placeholders:
SELECT * FROM Users WHERE username = ?;
-- The driver binds a string value for the single ? placeholder.

4) Prepared statements — what and why

Prepared statement = compile/parse SQL once (with placeholders) and then execute it multiple times with different bound values. Benefits:

Avoid repeated parsing/compilation overhead.
Enable plan reuse and caching (performance).
Adds an explicit separation between SQL and data (safety).
Some drivers/servers support server-side prepared statements for additional performance.

5) Placeholder styles across systems — quick reference

SQL Server / T-SQL: @name (named parameters). Example: @UserId
MySQL (client drivers): ? (positional) — many drivers use ?. MySQL server-side prepared statements also use ? and EXECUTE ... USING.
PostgreSQL: $1, $2 (positional) for server-side PREPARE or libpq parameterization. Some drivers allow named-style binding client-side.
Driver vs server: Some drivers emulate prepared statements client-side (substitute placeholders safely), others use server-side PREPARE. The placeholder syntax depends on driver and server.

6) SQL Server: prepared statements, stored procedures, and examples

6.1 Named parameter example (ad hoc query)

-- SQL Server ad-hoc, parameters are simple variables
DECLARE @MinAge INT = 25;
SELECT Id, Name FROM Person WHERE Age >= @MinAge;
-- @MinAge is bound locally in the batch

6.2 Stored procedure — single input parameter

CREATE PROCEDURE dbo.GetUsersByCountry
    @Country NVARCHAR(100)
AS
BEGIN
    SET NOCOUNT ON;
    SELECT UserId, Username, Country
    FROM Users
    WHERE Country = @Country;
END;
GO

-- Execute:
EXEC dbo.GetUsersByCountry @Country = 'Nepal';

Notes: stored procedure parameters are typed in the CREATE PROCEDURE header; call with EXEC proc @p = value or positional EXEC proc 'Nepal'.

6.3 Stored procedure — multiple parameters, joins and filters

CREATE PROCEDURE dbo.SearchOrders
    @CustomerId INT = NULL,
    @StartDate DATE = NULL,
    @EndDate DATE = NULL
AS
BEGIN
    SET NOCOUNT ON;

    SELECT o.OrderId, o.OrderDate, c.CustomerName, o.Total
    FROM Orders o
    JOIN Customers c ON c.CustomerId = o.CustomerId
    WHERE (@CustomerId IS NULL OR o.CustomerId = @CustomerId)
      AND (@StartDate IS NULL OR o.OrderDate >= @StartDate)
      AND (@EndDate   IS NULL OR o.OrderDate <= @EndDate);
END;
GO

-- Examples:
EXEC dbo.SearchOrders @CustomerId = 42;
EXEC dbo.SearchOrders @StartDate = '2025-01-01', @EndDate = '2025-12-31';

Why this supports reuse & safety: The same SQL logic runs for many parameter combinations; user input never changes SQL structure.

6.4 Dynamic SQL safely (SQL Server — `sp_executesql`)

When you must build SQL strings (e.g., variable table names), use sp_executesql with parameters:

DECLARE @sql NVARCHAR(MAX);
DECLARE @minPrice DECIMAL(10,2) = 100.00;

SET @sql = N'
    SELECT p.ProductId, p.Name, p.Price
    FROM dbo.Products p
    WHERE p.Price >= @minPrice
';

EXEC sp_executesql @sql, N'@minPrice DECIMAL(10,2)', @minPrice = @minPrice;

Key: the variable @minPrice is passed separately to sp_executesql so the value is bound safely.

If you need to inject identifiers (table/column names), validate/sanitize them against a whitelist before concatenation.

7) MySQL: prepared statements and examples

7.1 Client-driver example (positional `?`)

-- Client code uses:
SELECT id, username FROM users WHERE email = ? AND active = ?;
-- Driver binds first ? to email string, second ? to boolean/integer.

7.2 Server-side PREPARE / EXECUTE (MySQL)

-- Server-side prepare example
SET @sql = 'SELECT id, username FROM users WHERE age >= ? AND city = ?';
PREPARE stmt FROM @sql;
SET @age = 30;
SET @city = 'Kathmandu';
EXECUTE stmt USING @age, @city;
DEALLOCATE PREPARE stmt;

Notes: MySQL EXECUTE ... USING requires user-defined variables (e.g., @age). Many client drivers skip manual PREPARE and perform binds using ?.

7.3 Stored procedure (MySQL)

DELIMITER $$

CREATE PROCEDURE GetProductsByCategory(IN p_category VARCHAR(100))
BEGIN
    SELECT ProductId, Name, Price
    FROM Products
    WHERE Category = p_category;
END$$

DELIMITER ;

-- Execute:
CALL GetProductsByCategory('Beverages');

7.4 Safe dynamic SQL (MySQL)

If building SQL with identifiers, whitelist them. For values, use prepared statement placeholders with PREPARE + EXECUTE ... USING.

8) PostgreSQL: placeholders & prepared statements

8.1 Ad-hoc query using libpq ($1 style)

-- Example prepared on server or used by libpq: placeholders are $1, $2
SELECT id, name FROM employees WHERE department = $1 AND hired >= $2;
-- $1 and $2 are bound by the client/driver.

8.2 Server-side PREPARE / EXECUTE (Postgres)

PREPARE search_emps(text, date) AS
    SELECT id, name FROM employees WHERE department = $1 AND hired >= $2;

EXECUTE search_emps('Sales', '2024-01-01');
DEALLOCATE search_emps;

8.3 Dynamic SQL inside PL/pgSQL with parameters

CREATE OR REPLACE FUNCTION get_table_count(tbl TEXT) RETURNS BIGINT AS $$
DECLARE
  cnt BIGINT;
  sql TEXT;
BEGIN
  -- Validate table name against allowed list to avoid injection
  IF tbl NOT IN ('employees', 'departments') THEN
    RAISE EXCEPTION 'Invalid table name';
  END IF;

  sql := format('SELECT count(*) FROM %I', tbl); -- %I quotes identifier
  EXECUTE sql INTO cnt;
  RETURN cnt;
END;
$$ LANGUAGE plpgsql;

format(... %I) safely formats an identifier.

9) Application-layer example — C# ADO.NET (SqlCommand)

Good pattern (typed binding)

using System.Data;
using System.Data.SqlClient;

string connStr = "...";
using(SqlConnection conn = new SqlConnection(connStr))
{
    conn.Open();
    using(SqlCommand cmd = new SqlCommand(
        "SELECT UserId, Username FROM Users WHERE Email = @email AND IsActive = @active;",
        conn))
    {
        cmd.Parameters.Add("@email", SqlDbType.NVarChar, 256).Value = userEmail;
        cmd.Parameters.Add("@active", SqlDbType.Bit).Value = true;

        using(SqlDataReader r = cmd.ExecuteReader())
        {
            while(r.Read()) { /* ... */ }
        }
    }
}

Notes:

Prefer Parameters.Add() with explicit SqlDbType and size for correctness/plan stability.
AddWithValue() is common but can cause type inference issues (e.g., NVARCHAR vs VARCHAR) and suboptimal plans:

// Simpler but less recommended:
cmd.Parameters.AddWithValue("@email", userEmail);
cmd.Parameters.AddWithValue("@active", true);

Mapping:

@param in SQL Server → SqlCommand parameter name @param
? (MySQL) → MySqlCommand parameter markers depend on driver (e.g., ? or @p0), driver docs must be consulted.

10) Why parameter binding neutralizes injection (concise)

Injection occurs when user input is interpreted as SQL syntax. With parameter binding:

The SQL parser sees placeholders — not user text — when parsing/compiling.
Bound values are assigned as typed values; any characters they contain (quotes, semicolons) are data, not SQL tokens. Thus '; DROP TABLE Users; -- becomes a string value, not an executable clause.

Small attack example (unsafe) — MySQL / concatenation

Unsafe:

-- Suppose user input:  alice' OR '1'='1
SET @q = CONCAT('SELECT * FROM users WHERE username = ''', @userInput, ''';');
PREPARE st FROM @q;
EXECUTE st;

This yields ... WHERE username = 'alice' OR '1'='1' — attacker bypasses authentication.

Safe parameterized:

SELECT * FROM users WHERE username = ?;
-- Bind ? = "alice' OR '1'='1" -> treated as literal string; no bypass.

11) Dynamic SQL — safe patterns

SQL Server: use sp_executesql with parameter placeholders, pass parameter definitions and values separately.
Postgres: use EXECUTE with format() and %I for identifiers, but pass values as parameters to EXECUTE ... USING.
MySQL: use PREPARE + EXECUTE ... USING and user variables.

Rule of thumb: Never inject unvalidated user data into SQL text. If you must inject identifiers (table/column names), whitelist allowed values and/or use proper identifier quoting functions.

12) Performance notes

Prepared statements and stored procedures can reduce parse/compile overhead on repeated queries.
Plan caching: typed parameters and consistent parameter patterns help the optimizer reuse plans.
Beware of parameter sniffing (SQL Server): the plan created for a specific parameter value may be suboptimal for other values. Techniques: RECOMPILE hints, parameterization strategies, or OPTIMIZE FOR.
Overhead: creating many one-time server-side prepared statements may not help; reuse is key.
Use proper indexing and correct parameter types to achieve stable performance.

13) Practical use cases

Login authentication (SELECT ... WHERE username = @username AND passwordHash = @hash)
User-driven filtering (search forms with many optional parameters — combine with (@p IS NULL OR col = @p) pattern)
Inserts/Updates from forms (bind each form field to explicit parameter)
Batch operations: same prepared statement executed with different parameter sets.

14) Best practices (short)

Always parameterize user input.
Use typed parameters (avoid blind AddWithValue where type matters).
Do not build SQL by concatenating user strings (unless using a safe whitelist method for identifiers).
When using dynamic SQL, use parameterized execution (sp_executesql, EXECUTE ... USING, format(%I) with validation).
Validate or whitelist identifiers inserted into SQL.
Use stored procedures for business logic reuse and to centralize access control when appropriate.
Monitor and address parameter sniffing issues (SQL Server).

15) Exercises (with solutions)

Exercise 1 — Parameterized SELECT (SQL Server)

Task: Write a parameterized query that selects Id, Email from Customers where Country = @country and IsActive = @active.

Solution:

DECLARE @country NVARCHAR(100) = 'Nepal';
DECLARE @active BIT = 1;

SELECT Id, Email
FROM Customers
WHERE Country = @country
  AND IsActive = @active;

Exercise 2 — Parameterized INSERT (MySQL via driver syntax)

Task: Prepare an INSERT for Products(Name, Price) with two positional parameters.

Solution (driver SQL text):

INSERT INTO Products (Name, Price) VALUES (?, ?);
-- Driver binds first ? to string Name, second ? to decimal Price

Exercise 3 — Stored procedure with two parameters (SQL Server)

Task: Create a proc AddLog that inserts into Logs(Message, CreatedAt).

Solution:

CREATE PROCEDURE dbo.AddLog
    @Message NVARCHAR(4000),
    @CreatedAt DATETIME = NULL
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO Logs (Message, CreatedAt)
    VALUES (@Message, ISNULL(@CreatedAt, GETDATE()));
END;
GO

-- Execute:
EXEC dbo.AddLog @Message = 'User logged in', @CreatedAt = NULL;

Exercise 4 — Convert unsafe concatenated query to parameterized (Postgres)

Unsafe:

-- DO NOT DO:
EXECUTE 'SELECT * FROM orders WHERE order_no = ''' || p_order_no || '''';

Safe:

-- inside PL/pgSQL
EXECUTE 'SELECT * FROM orders WHERE order_no = $1' USING p_order_no;

16) Mini-FAQ — common errors & diagnosis

Q: Placeholder mismatch (more values than placeholders)?
A: Error will indicate number mismatch. Count placeholders and bound parameters; driver docs define placeholder counting (e.g., ? are positional).

Q: Quoting errors (unexpected token near ')')?
A: Often caused by concatenating strings. Use parameters instead. If using dynamic SQL, ensure text is built correctly and parameter placeholders match.

Q: Execution vs preparation scope (MySQL PREPARE)?
A: MySQL server-side PREPARE uses user variables for EXECUTE ... USING. PREPARE lives until DEALLOCATE PREPARE or session end.

Q: AddWithValue gives slow queries / wrong type?
A: AddWithValue infers type. Prefer Add(name, SqlDbType, size).Value = value.

Q: Why does plan change by parameter value?
A: Parameter sniffing: the optimizer uses parameter value to generate a plan. Solutions: OPTION (RECOMPILE), OPTIMIZE FOR UNKNOWN, or force plan stability with appropriate hints.

Q: Can I use parameters for table names?
A: No — parameters apply to values, not identifiers. Use white-listed identifiers or carefully built quoted identifiers.

17) Quick checklist (copy & use)

[ ] Parameterize every user input (no string concatenation).
[ ] Use typed parameters (specify db type and size).
[ ] Use prepared statements or stored procedures for repeated queries.
[ ] For dynamic SQL: pass values as parameters; whitelist/validate identifiers.
[ ] Review driver docs for placeholder style (?, $1, @name).
[ ] Test for SQL injection vectors during QA.
[ ] Monitor performance and parameter sniffing behavior.

SQL Server — runnable script (schema, data, parameterized examples, stored procedures, safe dynamic SQL)

Save this as param_examples_sqlserver.sql and run in SQL Server Management Studio (SSMS) or with sqlcmd. This script is safe to run on a development DB — it creates tables, inserts sample data, and demonstrates parameterized queries, stored procedures (single & multiple params), sp_executesql for safe dynamic SQL, and a commented unsafe concatenation example for illustration.

-- param_examples_sqlserver.sql
-- Run in SSMS (select a database first) or:
-- sqlcmd -S localhost -d YourDb -i param_examples_sqlserver.sql -U sa -P 'your_password'
-- WARNING: Run on a development database only.

SET NOCOUNT ON;

-- 1. Clean up existing objects (safe dev-only drop)
IF OBJECT_ID('dbo.Orders', 'U') IS NOT NULL DROP TABLE dbo.Orders;
IF OBJECT_ID('dbo.Users', 'U') IS NOT NULL DROP TABLE dbo.Users;
IF OBJECT_ID('dbo.GetUsersByCountry', 'P') IS NOT NULL DROP PROCEDURE dbo.GetUsersByCountry;
IF OBJECT_ID('dbo.SearchOrders', 'P') IS NOT NULL DROP PROCEDURE dbo.SearchOrders;
IF OBJECT_ID('dbo.AddLog', 'P') IS NOT NULL DROP PROCEDURE dbo.AddLog;

-- 2. Create sample schema
CREATE TABLE dbo.Users (
    UserId INT IDENTITY(1,1) PRIMARY KEY,
    Username NVARCHAR(100) NOT NULL UNIQUE,
    Email NVARCHAR(256) NOT NULL,
    IsActive BIT NOT NULL DEFAULT 1,
    Country NVARCHAR(100) NULL,
    CreatedAt DATETIME2 NOT NULL DEFAULT SYSDATETIME()
);

CREATE TABLE dbo.Orders (
    OrderId INT IDENTITY(1,1) PRIMARY KEY,
    UserId INT NOT NULL REFERENCES dbo.Users(UserId),
    OrderDate DATE NOT NULL,
    Total DECIMAL(10,2) NOT NULL
);

-- 3. Insert sample data
INSERT INTO dbo.Users (Username, Email, IsActive, Country)
VALUES
  ('alice', 'alice@example.com', 1, 'Nepal'),
  ('bob',   'bob@example.com', 1, 'USA'),
  ('mallory','mallory@badguy.example', 0, 'Nepal');

INSERT INTO dbo.Orders (UserId, OrderDate, Total)
VALUES
  (1, '2025-01-10', 120.50),
  (1, '2025-03-25',  75.00),
  (2, '2024-11-01', 2000.00);

-- 4. AD-HOC parameterized usage (T-SQL variables as parameters)
PRINT '--- Ad-hoc parameterized SELECT using T-SQL variables ---';
DECLARE @country NVARCHAR(100) = N'Nepal';
DECLARE @isActive BIT = 1;

SELECT UserId, Username, Email, Country, IsActive, CreatedAt
FROM dbo.Users
WHERE Country = @country
  AND IsActive = @isActive;

-- 5. Stored procedure: single input parameter
CREATE PROCEDURE dbo.GetUsersByCountry
    @Country NVARCHAR(100)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT UserId, Username, Email, Country, IsActive
    FROM dbo.Users
    WHERE Country = @Country;
END;
GO

-- Execute stored proc (named and positional)
PRINT '--- Execute dbo.GetUsersByCountry ---';
EXEC dbo.GetUsersByCountry @Country = N'Nepal';
EXEC dbo.GetUsersByCountry N'USA';

-- 6. Stored procedure: multiple parameters + JOINs + optional filters
CREATE PROCEDURE dbo.SearchOrders
    @CustomerId INT = NULL,        -- optional filter
    @StartDate DATE = NULL,        -- optional filter
    @EndDate   DATE = NULL
AS
BEGIN
    SET NOCOUNT ON;

    SELECT o.OrderId, o.OrderDate, o.Total, u.UserId, u.Username, u.Email
    FROM dbo.Orders o
    INNER JOIN dbo.Users u ON u.UserId = o.UserId
    WHERE (@CustomerId IS NULL OR o.UserId = @CustomerId)
      AND (@StartDate IS NULL OR o.OrderDate >= @StartDate)
      AND (@EndDate   IS NULL OR o.OrderDate <= @EndDate)
    ORDER BY o.OrderDate;
END;
GO

PRINT '--- Execute dbo.SearchOrders examples ---';
-- By customer
EXEC dbo.SearchOrders @CustomerId = 1;

-- By date range
EXEC dbo.SearchOrders @StartDate = '2025-01-01', @EndDate = '2025-12-31';

-- Combined
EXEC dbo.SearchOrders @CustomerId = 1, @StartDate = '2025-01-01';

-- 7. Parameterized dynamic SQL using sp_executesql (safe!)
-- Use this when SQL text must be built dynamically but values must still be bound safely.
PRINT '--- Demonstrate safe dynamic SQL with sp_executesql ---';
DECLARE @sql NVARCHAR(MAX);
DECLARE @minTotal DECIMAL(10,2) = 100.00;

SET @sql = N'
    SELECT o.OrderId, o.OrderDate, o.Total, u.Username
    FROM dbo.Orders o
    JOIN dbo.Users u ON u.UserId = o.UserId
    WHERE o.Total >= @minTotal
';

-- Note: parameter definition string and named parameter binding
EXEC sp_executesql
     @sql,
     N'@minTotal DECIMAL(10,2)',
     @minTotal = @minTotal;

-- 8. Unsafe concatenation example (commented) — DO NOT EXECUTE WITH UNTRUSTED INPUT
-- This shows how concatenation can lead to injection if user input is embedded directly.
-- Example user input that would break logic:  alice' OR '1'='1
-- Unsafe (do NOT run using untrusted input):
-- DECLARE @userInput NVARCHAR(200) = N'alice'' OR ''1''=''1';
-- DECLARE @badsql NVARCHAR(MAX) = N'SELECT UserId, Username FROM dbo.Users WHERE Username = ''' + @userInput + N''';';
-- PRINT @badsql;  -- Shows the injected SQL
-- EXEC(@badsql);  -- Vulnerable if @userInput is attacker-controlled

-- 9. Example converting unsafe to safe: use parameter instead of concatenation
PRINT '--- Safe equivalent (parameterized) to the above unsafe concatenation ---';
DECLARE @safeInput NVARCHAR(200) = N'alice'' OR ''1''=''1'; -- attacker string, treated as data
SELECT UserId, Username
FROM dbo.Users
WHERE Username = @safeInput;  -- safe: value cannot change SQL structure

-- 10. Demonstrate OUTPUT parameter and using a stored proc to insert (practical reuse)
CREATE PROCEDURE dbo.AddLog
    @Message NVARCHAR(4000),
    @CreatedAt DATETIME2 = NULL,
    @LogId INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO dbo.Users (Username, Email, IsActive, Country, CreatedAt)
    VALUES ('__loguser__', 'log@example.com', 0, 'Nowhere', ISNULL(@CreatedAt, SYSDATETIME()));

    SET @LogId = SCOPE_IDENTITY();
END;
GO

-- Note: Above AddLog uses Users table just for demo of OUTPUT — in real usage use a Logs table.
DECLARE @newId INT;
EXEC dbo.AddLog @Message = N'Test', @CreatedAt = NULL, @LogId = @newId OUTPUT;
PRINT 'Inserted surrogate log row with id: ' + CAST(@newId AS NVARCHAR(20));

-- 11. Demonstrate plan reuse note (examples you can run multiple times)
-- Running sp_executesql with the same statement text (and parameters) enables plan caching and reuse.
PRINT '--- Running repeated parameterized exec to demonstrate plan reuse potential ---';
DECLARE @i INT = 0;
WHILE @i < 3
BEGIN
    SET @minTotal = 50.00 + @i * 25;
    EXEC sp_executesql
         N'SELECT COUNT(*) FROM dbo.Orders WHERE Total >= @minTotal',
         N'@minTotal DECIMAL(10,2)',
         @minTotal = @minTotal;
    SET @i = @i + 1;
END;

-- 12. Cleanup note (optional)
-- DROP PROCEDURE dbo.GetUsersByCountry;
-- DROP PROCEDURE dbo.SearchOrders;
-- DROP PROCEDURE dbo.AddLog;
-- DROP TABLE dbo.Orders;
-- DROP TABLE dbo.Users;

PRINT '--- Script complete ---';

Quick notes & how this maps to the earlier explanation

@param is the SQL Server/T-SQL named placeholder style. Parameters are typed when declared in procedures or when passed to sp_executesql.
sp_executesql allows safe dynamic SQL by separating SQL text from bound parameter values (compile once, pass different values).
Stored procedures (CREATE PROCEDURE) accept typed parameters. Call them with EXEC proc @p = value (named) or EXEC proc value (positional).
Unsafe concatenation (commented) demonstrates how attacker-supplied strings can change SQL structure. Use parameter binding instead.
Repeated use of the same SQL text with different parameters helps plan reuse and reduces parsing/compilation overhead.

1) Plain-language definition & core idea

2) How placeholders work (short)

3) Simple SELECT filter: unsafe vs safe (SQL Server and MySQL examples)

Unsafe — string concatenation (vulnerable)

Safe — parameterized (SQL Server)

Safe — parameterized (MySQL client-style placeholder)

4) Prepared statements — what and why

5) Placeholder styles across systems — quick reference

6) SQL Server: prepared statements, stored procedures, and examples

6.1 Named parameter example (ad hoc query)

6.2 Stored procedure — single input parameter

6.3 Stored procedure — multiple parameters, joins and filters

6.4 Dynamic SQL safely (SQL Server — sp_executesql)

7) MySQL: prepared statements and examples

7.1 Client-driver example (positional ?)

7.2 Server-side PREPARE / EXECUTE (MySQL)

7.3 Stored procedure (MySQL)

7.4 Safe dynamic SQL (MySQL)

8) PostgreSQL: placeholders & prepared statements

8.1 Ad-hoc query using libpq ($1 style)

8.2 Server-side PREPARE / EXECUTE (Postgres)

8.3 Dynamic SQL inside PL/pgSQL with parameters

9) Application-layer example — C# ADO.NET (SqlCommand)

Good pattern (typed binding)

Mapping:

10) Why parameter binding neutralizes injection (concise)

Small attack example (unsafe) — MySQL / concatenation

11) Dynamic SQL — safe patterns

12) Performance notes

13) Practical use cases

14) Best practices (short)

15) Exercises (with solutions)

Exercise 1 — Parameterized SELECT (SQL Server)

Exercise 2 — Parameterized INSERT (MySQL via driver syntax)

Exercise 3 — Stored procedure with two parameters (SQL Server)

Exercise 4 — Convert unsafe concatenated query to parameterized (Postgres)

16) Mini-FAQ — common errors & diagnosis

17) Quick checklist (copy & use)

SQL Server — runnable script (schema, data, parameterized examples, stored procedures, safe dynamic SQL)

Quick notes & how this maps to the earlier explanation

6.4 Dynamic SQL safely (SQL Server — `sp_executesql`)

7.1 Client-driver example (positional `?`)