Parameterized Queries

This document details the parameterized query feature of the cDataBase class, an important security feature for preventing SQL injection attacks.

---

Table of Contents

• Parameterized Query Overview
• Param Method
• ExecParam Method
• QueryParam Method
• Data Types
• Security Advantages
• Usage Examples
• Best Practices

---

Parameterized Query Overview

What is Parameterized Query

Parameterized query is a technique that separates SQL statements and parameter values. Parameter values are passed through placeholders (usually ?).

Why Use Parameterized Queries

1. Prevent SQL Injection - Parameter values are escaped, cannot execute malicious SQL code
2. Performance Optimization - Database can cache execution plans
3. Type Safety - Automatic data type conversion
4. Clean Code - SQL statements and parameter values are separated, easy to maintain

SQL Injection Example

' Dangerous: Direct SQL concatenation (vulnerable to SQL injection)
Dim sName As String
sName = "'; DROP TABLE users; --"
db.Sql("SELECT * FROM users WHERE name = '" & sName & "'").Query
' Actual SQL executed: SELECT * FROM users WHERE name = ''; DROP TABLE users; --'
' Result: users table deleted!

' Safe: Use parameterized query
db.Sql("SELECT * FROM users WHERE name = ?") _
    .Param("name", sName, VBMAN.adVarWChar) _
    .QueryParam
' Parameter values are safely processed, cannot execute malicious code

---

Param Method

Syntax

Function Param(ByVal ParamName As String, _
               ByVal ParamValue As Variant, _
               Optional ByVal ParamType As DataTypeEnum = adVarChar) As cDataBase

Parameters

Parameter	Type	Description
ParamName	String	Parameter name (for identification, actually uses ? placeholder)
ParamValue	Variant	Parameter value (required)
ParamType	DataTypeEnum	Parameter data type (optional, default adVarChar)

Return Value

Returns cDataBase object, supports chained calls.

Example

' Add single parameter
db.Sql("SELECT * FROM users WHERE id = ?") _
    .Param("id", 1, VBMAN.adInteger)

' Add multiple parameters (chained call)
db.Sql("SELECT * FROM users WHERE name = ? AND age > ?") _
    .Param("name", "John", VBMAN.adVarWChar) _
    .Param("age", 18, VBMAN.adInteger)

---

ExecParam Method

Syntax

The ExecParam method executes parameterized INSERT, UPDATE, DELETE operations.

Function ExecParam(Optional RecordsAffected) As Boolean

Parameters

Parameter	Type	Description
RecordsAffected	Variant	Returns number of affected rows (optional)

Return Value

• True - Execution successful
• False - Execution failed (view error via LastErr)

Example

' Insert data
If db.Sql("INSERT INTO users (name, age, email) VALUES (?, ?, ?)") _
    .Param("name", "John", VBMAN.adVarWChar) _
    .Param("age", 25, VBMAN.adInteger) _
    .Param("email", "john@example.com", VBMAN.adVarWChar) _
    .ExecParam Then

    Debug.Print "Insert successful, ID: " & db.LastInsertId
End If

' Update data
If db.Sql("UPDATE users SET age = ?, email = ? WHERE id = ?") _
    .Param("age", 26, VBMAN.adInteger) _
    .Param("email", "newemail@example.com", VBMAN.adVarWChar) _
    .Param("id", 1, VBMAN.adInteger) _
    .ExecParam Then

    Debug.Print "Update successful"
End If

' Delete data
If db.Sql("DELETE FROM users WHERE id = ?") _
    .Param("id", 1, VBMAN.adInteger) _
    .ExecParam Then

    Debug.Print "Delete successful"
End If

---

QueryParam Method

Syntax

The QueryParam method executes parameterized SELECT queries.

Function QueryParam(Optional CurType As CursorTypeEnum = adOpenKeyset, _
                    Optional LockType As LockTypeEnum = adLockOptimistic) As Boolean

Parameters

Parameter	Type	Description
CurType	CursorTypeEnum	Cursor type (optional, default adOpenKeyset)
LockType	LockTypeEnum	Lock type (optional, default adLockOptimistic)

Return Value

• True - Query successful, results stored in db.Rs
• False - Query failed (view error via LastErr)

Important: The QueryParam method returns a Boolean value, not a Recordset. Query results need to be accessed through the db.Rs property.

Example

' Query single record (use db.Rs to access results)
If db.Sql("SELECT * FROM users WHERE id = ?") _
    .Param("id", 1, VBMAN.adInteger) _
    .QueryParam Then

    ' Use db.Rs to access Recordset
    If Not db.Rs.EOF Then
        Debug.Print db.Rs("name")
    End If
    db.Rs.Close
End If

' Query multiple records (use db.Rs to access results)
If db.Sql("SELECT * FROM users WHERE age > ? AND status = ?") _
    .Param("age", 18, VBMAN.adInteger) _
    .Param("status", "active", VBMAN.adVarWChar) _
    .QueryParam Then

    ' Use db.Rs to iterate through results
    Do Until db.Rs.EOF
        Debug.Print db.Rs("name")
        db.Rs.MoveNext
    Loop
    db.Rs.Close
End If

' If using Fetch method, can access db.Rows and db.Row
If db.Sql("SELECT * FROM users WHERE id = ?") _
    .Param("id", 1, VBMAN.adInteger) _
    .QueryParam Then

    ' After using Fetch, can access db.Rows and db.Row
    If db.Fetch Then
        If db.Rows.Count > 0 Then
            Debug.Print db.Row("name")
        End If
    End If
End If

---

Data Types

Common Data Types

Type	Value	Description	Example
adVarChar	200	Variable-length string	"John"
adVarWChar	202	Unicode string	"John"
adInteger	3	32-bit integer	25
adBigInt	20	64-bit integer	1234567890
adDouble	5	Double precision float	3.14
adDate	7	Date time	Now
adBoolean	11	Boolean	True
adDecimal	14	Exact numeric	99.99

Data Type Selection

' String
db.Param("name", "John", VBMAN.adVarWChar)  ' Unicode string (recommended)
db.Param("name", "John", VBMAN.adVarChar)   ' ANSI string

' Integer
db.Param("age", 25, VBMAN.adInteger)        ' 32-bit integer
db.Param("id", 1234567890, VBMAN.adBigInt)  ' 64-bit integer

' Float
db.Param("price", 99.99, VBMAN.adDouble)    ' Double precision
db.Param("amount", 99.99, VBMAN.adDecimal) ' Exact numeric

' Date time
db.Param("created", Now, VBMAN.adDate)       ' Date time

' Boolean
db.Param("active", True, VBMAN.adBoolean)   ' Boolean

---

Security Advantages

SQL Injection Protection

' Dangerous: Direct concatenation
Dim sInput As String
sInput = "'; DROP TABLE users; --"
db.Sql("SELECT * FROM users WHERE name = '" & sInput & "'").Query
' Result: users table deleted

' Safe: Parameterized query
db.Sql("SELECT * FROM users WHERE name = ?") _
    .Param("name", sInput, VBMAN.adVarWChar) _
    .QueryParam
' Parameter values are safely processed, cannot execute malicious code

Special Character Handling

' Dangerous: Special characters may cause errors
Dim sName As String
sName = "O'Brien"
db.Sql("SELECT * FROM users WHERE name = '" & sName & "'").Query
' SQL: SELECT * FROM users WHERE name = 'O'Brien'
' Error: Unescaped single quote

' Safe: Parameterized query handles automatically
db.Sql("SELECT * FROM users WHERE name = ?") _
    .Param("name", sName, VBMAN.adVarWChar) _
    .QueryParam
' Special characters are automatically escaped

---

Usage Examples

Example 1: User Login

Function UserLogin(sUsername As String, sPassword As String) As Boolean
    ' Use parameterized query to prevent SQL injection
    If db.Sql("SELECT * FROM users WHERE username = ? AND password = ?") _
        .Param("username", sUsername, VBMAN.adVarWChar) _
        .Param("password", sPassword, VBMAN.adVarWChar) _
        .QueryParam Then

        If db.Rows.Count > 0 Then
            UserLogin = True
        Else
            UserLogin = False
        End If
    Else
        UserLogin = False
    End If
End Function

Example 2: Search Feature

Function SearchUsers(sKeyword As String) As Collection
    Set SearchUsers = New Collection

    ' Use LIKE with parameterized query
    Dim sSql As String
    sSql = "SELECT * FROM users WHERE name LIKE ? OR email LIKE ?"

    If db.Sql(sSql) _
        .Param("name", "%" & sKeyword & "%", VBMAN.adVarWChar) _
        .Param("email", "%" & sKeyword & "%", VBMAN.adVarWChar) _
        .QueryParam Then

        Set SearchUsers = db.Rows
    End If
End Function

Example 3: Batch Insert

Sub BatchInsertUsers(colUsers As Collection)
    db.TransBegin

    Dim i As Long
    For i = 1 To colUsers.Count
        Dim dictUser As Dictionary
        Set dictUser = colUsers(i)

        If Not db.Sql("INSERT INTO users (name, age, email) VALUES (?, ?, ?)") _
            .Param("name", dictUser("name"), VBMAN.adVarWChar) _
            .Param("age", dictUser("age"), VBMAN.adInteger) _
            .Param("email", dictUser("email"), VBMAN.adVarWChar) _
            .ExecParam Then

            db.TransRollback
            Exit Sub
        End If
    Next

    db.TransCommit
End Sub

Example 4: Dynamic Query

Function GetUsers(Optional sName As String = "", _
                  Optional lMinAge As Long = 0, _
                  Optional sStatus As String = "") As Collection
    Set GetUsers = New Collection

    Dim sSql As String
    sSql = "SELECT * FROM users WHERE 1=1"

    ' Dynamically build SQL and parameters
    If sName <> "" Then
        sSql = sSql & " AND name LIKE ?"
    End If
    If lMinAge > 0 Then
        sSql = sSql & " AND age >= ?"
    End If
    If sStatus <> "" Then
        sSql = sSql & " AND status = ?"
    End If

    ' Set SQL
    db.Sql (sSql)

    ' Add parameters
    If sName <> "" Then
        db.Param "name", "%" & sName & "%", VBMAN.adVarWChar
    End If
    If lMinAge > 0 Then
        db.Param "age", lMinAge, VBMAN.adInteger
    End If
    If sStatus <> "" Then
        db.Param "status", sStatus, VBMAN.adVarWChar
    End If

    ' Execute query
    If db.QueryParam Then
        Set GetUsers = db.Rows
    End If
End Function

---

Best Practices

1. Always Use Parameterized Queries for User Input

' Recommended: Use parameterized queries
db.Sql("SELECT * FROM users WHERE name = ?") _
    .Param("name", txtName.Text, VBMAN.adVarWChar) _
    .QueryParam

' Not recommended: Direct concatenation of user input
db.Sql("SELECT * FROM users WHERE name = '" & txtName.Text & "'").Query

2. Choose Correct Data Types

' Recommended: Explicitly specify data types
db.Param("age", 25, VBMAN.adInteger)
db.Param("name", "John", VBMAN.adVarWChar)
db.Param("price", 99.99, VBMAN.adDecimal)

' Not recommended: Use default types (may cause type mismatch)
db.Param("age", 25)  ' Default adVarChar, may cause errors

3. Use Unicode String Type

' Recommended: Use adVarWChar to support Chinese
db.Param("name", "John", VBMAN.adVarWChar)

' Not recommended: Use adVarChar (may cause Chinese garbled text)
db.Param("name", "John", VBMAN.adVarChar)

4. Parameter Order Must Match Placeholder Order

' Correct: Parameter order matches ? order
db.Sql("SELECT * FROM users WHERE name = ? AND age > ?") _
    .Param("name", "John", VBMAN.adVarWChar) _
    .Param("age", 18, VBMAN.adInteger)

' Wrong: Parameter order is wrong
db.Sql("SELECT * FROM users WHERE name = ? AND age > ?") _
    .Param("age", 18, VBMAN.adInteger) _
    .Param("name", "John", VBMAN.adVarWChar)

5. Clear Parameters

' Note: ExecParam and QueryParam automatically clear parameters
' If only calling Param without executing, need to manually clear (by executing or recreating object)

---

Common Questions

Q1: Is Parameterized Query Slower Than Direct Concatenation?

Answer: No, parameterized queries are usually faster because:

• Database can cache execution plans
• Reduces SQL parsing time
• Avoids string concatenation overhead

Q2: How to Use Parameters in LIKE Queries?

' Correct: Include wildcards in parameter value
db.Sql("SELECT * FROM users WHERE name LIKE ?") _
    .Param("name", "%" & sKeyword & "%", VBMAN.adVarWChar) _
    .QueryParam

' Wrong: Use wildcards in SQL
db.Sql("SELECT * FROM users WHERE name LIKE '%?%'") _
    .Param("name", sKeyword, VBMAN.adVarWChar) _
    .QueryParam

Q3: How to Handle NULL Values?

' Use Null value
db.Sql("SELECT * FROM users WHERE email = ?") _
    .Param("email", Null, VBMAN.adVarWChar) _
    .QueryParam

' Or use IS NULL
db.Sql("SELECT * FROM users WHERE email IS NULL").Query

Q4: Does Parameterized Query Support IN Clause?

' Note: ADO parameterized query has limited support for IN clause
' Recommendation: Use multiple OR conditions or dynamically build SQL

' Method 1: Use multiple OR
db.Sql("SELECT * FROM users WHERE id = ? OR id = ? OR id = ?") _
    .Param("id1", 1, VBMAN.adInteger) _
    .Param("id2", 2, VBMAN.adInteger) _
    .Param("id3", 3, VBMAN.adInteger) _
    .QueryParam

' Method 2: Dynamic building (requires input validation)
Dim sIds As String
sIds = "1,2,3"  ' Needs format validation
db.Sql("SELECT * FROM users WHERE id IN (" & sIds & ")").Query

---

Last Updated: 2026-01-21